Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports



Results 1 to 4 of 4

Thread: Partial search of text in a different excel

  1. #1
    Join Date
    18th June 2017
    Posts
    2

    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.
    VB:
    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 at 06:06.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    18th June 2017
    Posts
    2

    Re: Partial search of text in a different excel

    Hi Team,

    Can someone please try to help me in this macro.

    Thanks

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    9th April 2007
    Location
    Alstonville, Straya
    Posts
    5,724

    Re: Partial search of text in a different excel

    Hi siddhiq.sayed
    You can loop an array something like
    VB:
    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 
    
    

  4. #4
    Join Date
    9th April 2007
    Location
    Alstonville, Straya
    Posts
    5,724

    Re: Partial search of text in a different excel

    or just use a wildcard
    VB:
    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 
    
    

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Search & open an excel file with partial name known to me
    By Suprakash in forum Excel General
    Replies: 1
    Last Post: June 6th, 2015, 17:41
  2. partial file name, partial folder name search
    By Jaye7 in forum Excel General
    Replies: 4
    Last Post: July 22nd, 2014, 16:04
  3. Replies: 3
    Last Post: September 29th, 2013, 03:20
  4. Search full cell with partial text
    By jazzgilbert in forum Excel General
    Replies: 8
    Last Post: August 18th, 2012, 01:21
  5. import partial text file(s) to excel with VBA
    By Blackdawg in forum Excel General
    Replies: 3
    Last Post: July 7th, 2012, 14:14

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno