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.
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