Finding EXACT MATCH

Galego

New Member
Joined
May 8, 2017
Messages
43
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.


Code:
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
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
You need to specify the Lookat parameter:

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

Forum statistics

Threads
1,223,275
Messages
6,171,126
Members
452,381
Latest member
Nova88

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top