Hi, I want to compare 2 sheets and based on Amount and put unique reference numbers from the other sheet.
For example, if the amount from sheet1 matches one of the amount from Sheet 2, put the unique reference from Sheet 2 to Sheet 1 (in Sheet2 Reference column) and put the unique reference from Sheet 1 to Sheet 2 (in Sheet1 Reference column).
I tried finding the value using the Find function:
Code
Sub matchSheets()
Dim ws1 As Worksheet, ws2 As Worksheet
Dim srchres As Variant
Set ws1 = Worksheets("Sheet1")
Set ws2 = Worksheets("Sheet2")
ws1.Range("D2").Select
Do Until IsEmpty(ActiveCell) And IsEmpty(ActiveCell.Offset(1, 0))
FindString = ActiveCell.Value
If ActiveCell.Value <> "" Then
With ws2.Range("F:F")
Set Rng = .Find(What:=FindString, _
After:=.Cells(.Cells.count), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not Rng Is Nothing Then
ActiveCell.Offset(0, 5).Value = Rng.Address
Application.Goto Rng, True
'Debug.Print Rng.Address
Else
Debug.Print "Nothing found"
End If
End With
End If
ActiveCell.Offset(1, 0).Select
Loop
End Sub
Display More
The above code does not give me desired results. I would also want to handle:
- If multiple matches are found, out comma separated values in reference.