VBA Find not working!

ricardomilhomem · Aug 20th 2015

Hello all! First of all, I want to thank you for you great responsiveness. Although it is my first or second post, I come to this forum every now and then looking for answers.

Now, let's go to my problem. I have a workbook containing a list of all invoices from all branches, let's call it "Everything", and basically I need to search if the invoices are found in another file containing each branch's invoices,. It's actually on file for each branch, and each file is divided with sheets by month, and I need to check in every sheet and then insert a value in a cell. Let's call this one "0001" and so on for each branch.

The "everything" file contains basically one column with the branch number, one with the invoice number, one with the issuer code and one saying if it was found on the branches files. The branches files contains the same except the branch number, and the last column says if the invoice is on the "Everything" file or not. There are cases where an invoice is on the branch file and is not on the "everything file" and also cases where it is on the everything file and is not on the branches file.

What I tried to do was insert a loop in VBA so it would go automatically invoice after invoice in the everything file and open the specific branch file, then search for the invoice number in each sheet. I would also need to check if the issuer is the same, but first I tried this code and when it searched for the value it returned the wrong cell! Here is the code:

Code

Dim sh As Worksheet
Dim iLoop As Integer
For iLoop = 7 To 1719


' this is where the invoices are in an excel sheet


iloopoffset = iLoop - 6


' as you see above, the list of invoices starts at line 7, so I used this to offset


If Range("K6").Offset(iloopoffset).Value = "No" Then


' Column K is the one saying if the invoice was found or not in the branches file


Set searchedvalue = Range("B6").Offset(iloopoffset, 0)


' I used this so i could use the value in the .find formula


MsgBox (searchedvalue.Value)
Workbooks.Open ("C:\Users\xxxxxx\Documents\xxxxxx\XML " + Range("D6").Offset(iloopoffset).Value)
For Each sh In Worksheets
If ActiveSheet.Name = "062015" Or "052015" Or "042015" Or "032015" Or "022015" Or "012015" Or "122014" Or "112014" Then


' I needed to do this because on the sheets with the names above, the searched value will be in another column. sheets before 112014 are different.
    
        Set NFE = Worksheets(sh.Name).Range("B:B").Find(Range("B6").Offset(iloopoffset, 0).Value, lookat:=xlPart)
        Else
        Set NFE = Worksheets(sh.Name).Range("A:A").Find(Range("B6").Offset(iloopoffset, 0).Value, lookat:=xlPart)
        End If


    
    If Not NFE Is Nothing Then
    MsgBox ("Found on sheet " + ActiveSheet.Name + " " + NFE.Address)
    Range(NFE.Address).Offset(, 12).Value = "YES"


    ' yes for found


    ActiveWorkbook.Save
    ActiveWindow.Close
    End If
    Next sh
    ActiveWorkbook.Save
    ActiveWindow.Close
End If
Next iLoop
End sub

Display More

What is going on? I am a true noob in VBA, but i didn't find anything wrong with this code... can you help me?

Kieran · Aug 21st 2015

Re: VBA Find not working!

The following code is UNTESTED, but may help.

Code

Dim sh As Worksheet
Dim iLoop As Integer
For iLoop = 7 To 1719
     
     ' this is where the invoices are in an excel sheet
     
    iloopoffset = iLoop - 6
     
     ' as you see above, the list of invoices starts at line 7, so I used this to offset
     
    If Range("K6").Offset(iloopoffset).Value = "No" Then
         
         ' Column K is the one saying if the invoice was found or not in the branches file
         
        Set searchedvalue = Range("B6").Offset(iloopoffset, 0)
         
         ' I used this so i could use the value in the .find formula
         
        MsgBox (searchedvalue.Value)
        Workbooks.Open ("C:\Users\xxxxxx\Documents\xxxxxx\XML " + Range("D6").Offset(iloopoffset).Value)
        For Each sh In Worksheets
        'changed activesheet.name to sh.name
            If sh.Name = "062015" Or "052015" Or "042015" Or "032015" Or "022015" Or "012015" Or "122014" Or "112014" Then
                 
                 ' I needed to do this because on the sheets with the names above, the searched value will be in another column. sheets before 112014 are different.
                 
                'Set NFE = Worksheets(sh.Name).Range("B:B").Find(Range("B6").Offset(iloopoffset, 0).Value, lookat:=xlPart)
                ' was using the worksheet find method - not the range find method.
                With sh.Name.Range("B:B")
                    Set NFE = .Find(Range("B6").Offset(iloopoffset, 0).Value, LookIn:=xlValues, lookat:=xlPart)
                        If Not NFE Is Nothing Then
                            firstAddress = c.Address
                            Do
                                Set NFE = .FindNext(c)
                            Loop While Not NFE Is Nothing And c.Address <> firstAddress
                        End If
                End With
                
            Else
                'Set NFE = Worksheets(sh.Name).Range("A:A").Find(Range("B6").Offset(iloopoffset, 0).Value, lookat:=xlPart)
                 ' was using the worksheet find method - not the range find method.
                With sh.Name.Range("A:A")
                    Set NFE = .Find(Range("B6").Offset(iloopoffset, 0).Value, LookIn:=xlValues, lookat:=xlPart)
                        If Not NFE Is Nothing Then
                            firstAddress = c.Address
                            Do
                                Set NFE = .FindNext(c)
                            Loop While Not NFE Is Nothing And c.Address <> firstAddress
                        End If
                End With
            End If
             
                     
            If Not NFE Is Nothing Then
                'changed activesheet.name to sh.name
                MsgBox ("Found on sheet " + sh.Name + " " + NFE.Address)
                Range(NFE.Address).Offset(, 12).Value = "YES"
                 
                 ' yes for found
                 
                ActiveWorkbook.Save  ' this is done outside the "For Each sh In Worksheets" loop - do you really want it here?
                ActiveWindow.Close   ' this is done outside the "For Each sh In Worksheets" loop - do you really want it here?
            End If
        Next sh
        ActiveWorkbook.Save
        ActiveWindow.Close
    End If
Next iLoop
End Sub

Display More

VBA Find not working!

Participate now!

Share