Finding EXACT MATCH

[Galego]

Morning,

I have the below code which works well with a bit BUT.

It searches in the wsData for Range("D11:N11") - which are numbers - and then copy/paste the entire to a different worksheet (Macro Statement.xlsm).
The problem is that I would like it to find a perfect match for the range as opposite as finding the number combination within an existing number. What I mean by that is that if it is searching for 1234 in the wsData it will find all rows containing this match but also those numbers which have this 1234 in it (for isntance 701234).

Is there anyway to correct the code and make it find the exact match?

Thanks in advance.

Sub ExtractData()

    Dim wsData As Worksheet
    Dim wsMacro As Worksheet
    Dim sToFind As String
    Dim sFirstAddress As String
    Dim nr As Long, lr As Long
    Dim rFind As Range
    Dim c As Range
    
    'Set worksheets as variables
    Set wsData = Workbooks("Data.xlsx").Sheets("Data")
    Set wsMacro = Workbooks("Macro Statement.xlsm").Sheets("Balance")
    
    'Last used row
    lr = wsData.Range("D" & Rows.Count).End(xlUp).Row
    
    'next available blank row on Macro sheet
    nr = wsMacro.Range("A" & Rows.Count).End(xlUp).Row + 1
    

    For Each c In wsMacro.Range("D11:N11")
        
        'set the string variable
        sToFind = c.Value 'Change as necessary
        
        'search column D
        Set rFind = wsData.Range("B1:B" & lr).Find(What:=sToFind)
        
        'search string not found so go to next cell
        
        If rFind Is Nothing Then
            MsgBox sToFind & "ERP# not found", vbInformation, "Not Found"
            GoTo nextSearch
        ElseIf rFind.Value <> sToFind Then
            MsgBox sToFind & "ERP# not found", vbInformation, "Not Found"
            GoTo nextSearch
        End If
        
        'store the first address
        sFirstAddress = rFind.Address
        
        Do
            'copy the row
            wsData.Range("D" & rFind.Row & ":N" & rFind.Row).Copy
            'paste the row
            wsMacro.Range("A" & nr).PasteSpecial xlPasteAll
            'set next row number
            nr = nr + 1
            'Find the next instance of the search value
            Set rFind = wsData.Range("B1:B" & lr).FindNext(After:=rFind)
        'loop until we get back to the first 1
        Loop Until rFind.Address = sFirstAddress
nextSearch:
        Set rFind = Nothing
    Next c
    
    'House keeping
    Set wsData = Nothing
    Set wsMacro = Nothing
    
    End Sub

 

[RoryA]

You need to specify the Lookat parameter:

Set rFind = wsData.Range("B1:B" & lr).Find(What:=sToFind, lookat:=xlwhole)

 

[Galego]

You need to specify the Lookat parameter:

Set rFind = wsData.Range("B1:B" & lr).Find(What:=sToFind, lookat:=xlwhole)

Yes that was it!

Many thanks.