Find A Customer on sheet from a list of customers

Question

OK I was struggling to find an answer to this one. I have a list of customers in an excel sheet (downloaded from my accounting software) and I have another excel sheet with today's invoices (each invoice is on a different tab, also downloaded from my accounting software(as PDF then converted to excel)) What I do is use VBA to scrape the data from each invoice and paste it into another excel file (to explain reasoning would take ages). What I am currently doing is finding "invoice to" on the sheet then off setting it by 1,0 as I know that is where the customers name is,

Set customer = Cells.Find(what:="INVOICE TO").Offset(1, 0)

but recently they(accounting software) changed "Invoice to" to "Bill to" I have worked around that with an error handler then underneath just used the same code with "Bill to" and that works.

What I would like to do is use the list of customers to find and select the customer name from that tab. can anyone help me with this? here is the whole code (well the main part that is needed) p.s. I am new to programming and newer still to VBA so this may not look good and have lots of terrible practices

Sub scappingData()
On Error Resume Next
   

Dim fileName As Variant
Dim folderName As String
Dim wsDest As Worksheet
Dim x As Long
Dim customer As Object
Dim firstItem As Object
Dim lastItem As Object
Dim columnItem As Long
Dim firstQTY As Object
Dim lastQTY As Object
Dim ColumnQTY As Long
Dim invoiceDate As Object
Dim LoginName As String
Dim BillTo As Object

'open the destination folder
LoginName = Environ("Username")
Workbooks.Open "C:\Users\" & LoginName & "\Google Drive\Returns\" & "ReturnsData.xlsm"

 'set variables

fileName = Dir("C:\Users\" & LoginName & "\Google Drive\sales\")
folderName = "C:\Users\" & LoginName & "\Google Drive\sales\"
Set wsDest = Workbooks("ReturnsData.xlsm").Worksheets("OrderedData")

'Loop through all files in a folder
While fileName <> ""

x = 0
Workbooks.Open folderName & fileName

'consolidate worksheets for those who have a 2 page delivery note
For Each Worksheet In Worksheets
        x = x + 1
        Workbooks(fileName).Worksheets(x).Activate
        If Range("A1") = "ACTIVITY" Then
            Range(Cells.Find(what:="ACTIVITY").Offset(1, 0), 
Cells.Find(what:="ACTIVITY").End(xlDown)).Copy _
                Worksheets(x - 1).Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
            Range(Cells.Find(what:="QTY").Offset(1, 0), Cells.Find(what:="QTY").End(xlDown)).Copy _
                Worksheets(x - 1).Range("E" & Rows.Count).End(xlUp).Offset(1, 0)
        End If
Next

x = 0
LastItemPrevious = ""
 
For Each Worksheet In Worksheets

    x = x + 1
        
    Workbooks(fileName).Worksheets(x).Activate
      
        If Range("A1") = "D&A Foods Ltd" Then
        
            'copy and paste products
            Set firstItem = Cells.Find(what:="ACTIVITY").Offset(1, 0)
            Set lastItem = Cells.Find(what:="ACTIVITY").End(xlDown)
            columnItem = wsDest.Cells(Rows.Count, "C").End(xlUp).Row
            Range(firstItem, lastItem).Copy wsDest.Range("C" & columnItem).Offset(1)
            
            'copy,paste, fill name

            Set customer = Cells.Find(what:="INVOICE TO").Offset(1, 0)
            Set customer = Cells.Find(what:="BILL TO").Offset(1, 0)
            
            customer.Copy wsDest.Range("B" & columnItem).Offset(1)
            
            'Copy and paste Amounts
            'If LastItemPrevious <> "(200G)" Then
            
                Set firstQTY = Cells.Find(what:="QTY").Offset(1, 0)
                Set lastQTY = Range("E" & Rows.Count).End(xlUp)
                ColumnQTY = wsDest.Cells(Rows.Count, 4).End(xlUp).Row
                Range(firstQTY, lastQTY).Copy wsDest.Range("D" & ColumnQTY).Offset(1)
         
            'copy,paste, fill date
            Set invoiceDate = Cells.Find(what:="DATE").Offset(0, 1)
            invoiceDate.Copy wsDest.Range("A" & columnItem).Offset(1)
            
            'LastItemPrevious = lastItem
            
        End If
        
Next

'Closes file and Sets the fileName to the next file
Workbooks(fileName).Close SaveChanges:=False
fileName = Dir
Wend

End Sub

Thanks Allan

When you try debugging, you must comment the line `On Error Resume Next`. Oherwise, you will not see where and what error does appear. Then, `fileName` needs a **file name** with a specific extension... In terms of `*.xlsx`. No need of two consecutive iterations between the workbook sheets. You can switch between the two cases of `Range("A1").value` and do what it is necessary for each specific case, in the same (unique) iteration. — FaneDuru, Jul 01 '20 at 16:05
Worth reviewing this post for guidance on how to avoid your code relying on an implicit ActiveSheet reference: changes here can make your code much more robust. https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba — Tim Williams, Jul 01 '20 at 16:20
Thank you for your help I will look into doing that Tim Williams. And thank you FaneDuru But the Thing I am struggling with is: i would like to do is use a customer list (from another excel sheet) to see if it can find a customer name on the current tab I am on. So I have a list of customers (each in a separate cell) and I would like VBA to look on the current tab to see if it contains one of those customers names. hope that makes sense. In other words Does the current Tab have a customers name in it (from a list of about 100 customers), if so select it. Thanks again — Allan Holleran, Jul 08 '20 at 14:27

score 1 · Answer 1 · answered Jul 01 '20 at 16:28

There's no need for On Error Resume Next here - you can always check if Find() makes a match before trying to do something with the return value.

For example (splitting out the search for one of two or more values into a separate function):

Sub Tester()

    Dim f As Range, ws As Worksheet
    Set ws = ActiveSheet
    
    Set f = FindFirst(ws.Cells, Array("INVOICE TO", "BILL TO"))

    If Not f Is Nothing Then          '<< was there a match ?
        Debug.Print f.Address
    Else
        Debug.Print "Search terms not found"
    End If

End Sub

'Find a value in "arr" in the Range "rng"
'  Return Nothing if no matches
Function FindFirst(rng As Range, arr)
    Dim f As Range, txt
    For Each txt In arr
        Set f = rng.Find(txt, lookat:=xlWhole)
        If Not f Is Nothing Then Exit For
    Next txt
    Set FindFirst = f 'might still be Nothing
End Function

Thank you for your response, I know the on error is not ideal, and will remove that when I find a solution to the problem I am trying to solve. what i would like to do is use a customer list (from another excel sheet) to see if it can find a customer name on the current tab I am on. So I have a list of customers (each in a separate cell) and I would like VBA to look on the current tab to see if it contains one of those customers names. hope that makes sense. In other words Does the current Tab have a customers name in it (from a list of about 100 customers), if so select it. Thanks again — Allan Holleran, Jul 08 '20 at 14:21

Find A Customer on sheet from a list of customers

1 Answers1