Announcement

Collapse
No announcement yet.

Partial search of text in a different excel

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Partial search of text in a different excel

    Hi Team,

    I am stuck in macro. I am trying to search 03/06/2017176947 in a password protected file where the attendance is kept. I have the below mentioned code to check if any attendance has been marked for 03/06/2017 for 176947, if it is marked already it will be saved as "03/06/2017u176947P".

    Incase if the macro finds then it will display "Attendance already marked for " or else mark the attendance.
    Code:
    Sub Mysearch()
        Dim wb As Workbook
        Dim ws As Worksheet
        Dim Ret
        
        Dim SearchRange As Range
        Dim FindRow As Range
    
    
        tdate = Range("C4").Value
        Tempcode = Range("C3").Value
        Tname = Range("C2").Value
        Tstat = Range("C6").Value
        
        Dim lRow As Long
        
        'x = tdate & Tempcode & Tstat
        x = tdate & Tempcode
        
         Set wb = Workbooks.Open("E:\Attendance\Master.xls")
    
    
        Set ws = wb.Worksheets("Sheet1")
        Sheets(1).Unprotect Password:="Nokia123"
        ws.Protect Password:="Nokia123", UserInterfaceOnly:=True
    
    
        Set SearchRange = Range("F1", Range("F600").End(xlUp))
        Set FindRow = SearchRange.Find(x, LookIn:=xlValues, lookat:=xlWhole)
    
    
        If FindRow Is Nothing Then
            lRow = Cells(Rows.Count, 1).End(xlUp).Row + 1
            ws.Range("A" & lRow).Value = tdate
            ws.Range("B" & lRow).Value = Tempcode
            ws.Range("C" & lRow).Value = Tname
            ws.Range("D" & lRow).Value = Tstat
            ws.Range("E" & lRow).Value = Now()
            ws.Range("F" & lRow).Value = x
            wb.Close savechanges:=True
            ActiveSheet.Protect UserInterfaceOnly:=False
            MsgBox "Attendance Added"
        Else
            tempadd = FindRow.Address
            
            Range(tempadd).Select
            Selection.Offset(0, -2).Select
            s1 = ActiveCell.Value
            
            Selection.Offset(0, -1).Select
            n1 = ActiveCell.Value
            
            wb.Close savechanges:=False
            'ActiveSheet.Protect UserInterfaceOnly:=False
            MsgBox "Attendance already marked for " & n1 & " as " & s1 & " for " & tdate
        End If
    End Sub
    Please try to help cause i am able to search 03/06/2017u176947P or 03/06/2017u176947A or 03/06/2017u176947L not 03/06/2017u176947

    Thanks,
    Siddhiq
    Last edited by PCI; June 18th, 2017, 06:06.

  • #2
    Re: Partial search of text in a different excel

    Hi Team,

    Can someone please try to help me in this macro.

    Thanks

    Comment


    • #3
      Re: Partial search of text in a different excel

      Hi siddhiq.sayed
      You can loop an array something like
      Code:
       Sub Mysearch()
          Dim wb As Workbook
          Dim ws As Worksheet
          Dim Ret
          Dim myArray
          Dim SearchRange As Range
          Dim FindRow As Range
           
           
          tdate = Range("C4").Value
          Tempcode = Range("C3").Value
          Tname = Range("C2").Value
          Tstat = Range("C6").Value
          Dim arrayItem
          Dim lRow As Long
           
           'x = tdate & Tempcode & Tstat
          x = tdate & Tempcode
          myArray = VBA.Array(x & "P", x & "L", x & "A")
          For Each arrayItem In myArray
          
          Set wb = Workbooks.Open("E:\Attendance\Master.xls")
           
           
          Set ws = wb.Worksheets("Sheet1")
          Sheets(1).Unprotect Password:="Nokia123"
          ws.Protect Password:="Nokia123", UserInterfaceOnly:=True
           
           
          Set SearchRange = Range("F1", Range("F600").End(xlUp))
          
          
          Set FindRow = SearchRange.Find(arrayItem, LookIn:=xlValues, lookat:=xlWhole)
           
           
          If FindRow Is Nothing Then
              lRow = Cells(Rows.Count, 1).End(xlUp).Row + 1
              ws.Range("A" & lRow).Value = tdate
              ws.Range("B" & lRow).Value = Tempcode
              ws.Range("C" & lRow).Value = Tname
              ws.Range("D" & lRow).Value = Tstat
              ws.Range("E" & lRow).Value = Now()
              ws.Range("F" & lRow).Value = x
              wb.Close savechanges:=True
              ActiveSheet.Protect UserInterfaceOnly:=False
              MsgBox "Attendance Added"
          Else
              tempadd = FindRow.Address
               
              Range(tempadd).Select
              Selection.Offset(0, -2).Select
              s1 = ActiveCell.Value
               
              Selection.Offset(0, -1).Select
              n1 = ActiveCell.Value
               
              wb.Close savechanges:=False
               'ActiveSheet.Protect UserInterfaceOnly:=False
              MsgBox "Attendance already marked for " & n1 & " as " & s1 & " for " & tdate
          End If
          Next arrayItem
      End Sub
      If the solution helped please donate to RSPCA

      Sites worth visiting: Rabbitohs | Excel-it royUK | Excel Matters Rory | Kris' Spreadsheet Solutions | Domenic xl-central | SO The Macro Man | The Smallman

      Comment


      • #4
        Re: Partial search of text in a different excel

        or just use a wildcard
        Code:
        Sub Mysearch()
            Dim wb As Workbook
            Dim ws As Worksheet
            Dim Ret
            Dim SearchRange As Range
            Dim FindRow As Range
            Dim lRow As Long
        
            tdate = Range("C4").Value
            Tempcode = Range("C3").Value
            Tname = Range("C2").Value
            Tstat = Range("C6").Value
            'x = tdate & Tempcode & Tstat
            x = tdate & Tempcode
            Set wb = Workbooks.Open("E:\Attendance\Master.xls")
            Set ws = wb.Worksheets("Sheet1")
            Sheets(1).Unprotect Password:="Nokia123"
            ws.Protect Password:="Nokia123", UserInterfaceOnly:=True
            Set SearchRange = Range("F1", Range("F600").End(xlUp))
            Set FindRow = SearchRange.Find(x & "?", LookIn:=xlValues, lookat:=xlWhole)
            If FindRow Is Nothing Then
                lRow = Cells(Rows.Count, 1).End(xlUp).Row + 1
                ws.Range("A" & lRow).Value = tdate
                ws.Range("B" & lRow).Value = Tempcode
                ws.Range("C" & lRow).Value = Tname
                ws.Range("D" & lRow).Value = Tstat
                ws.Range("E" & lRow).Value = Now()
                ws.Range("F" & lRow).Value = x
                wb.Close savechanges:=True
                ActiveSheet.Protect UserInterfaceOnly:=False
                MsgBox "Attendance Added"
            Else
                tempadd = FindRow.Address
                Range(tempadd).Select
                Selection.Offset(0, -2).Select
                s1 = ActiveCell.Value
                Selection.Offset(0, -1).Select
                n1 = ActiveCell.Value
                wb.Close savechanges:=False
                'ActiveSheet.Protect UserInterfaceOnly:=False
                MsgBox "Attendance already marked for " & n1 & " as " & s1 & " for " & tdate
            End If
        
        End Sub
        If the solution helped please donate to RSPCA

        Sites worth visiting: Rabbitohs | Excel-it royUK | Excel Matters Rory | Kris' Spreadsheet Solutions | Domenic xl-central | SO The Macro Man | The Smallman

        Comment

        Working...
        X