I am trying to compare vertical range "A" in sheet1 to vertical range "K" in sheet2 if they match to copy both rows and place in sheet3.
I have tried the following but it did not work. Any suggestions would be greatly appreciated.
Sub Searching()
Dim sh1 As Worksheet, sh2 As Worksheet, sh3 As Worksheet
' create short references to sheets
' inside the Sheets() use either the tab number or name
Set sh1 = Sheets("Sheet1"): Set sh2 = Sheets(2): Set sh3 = Sheets(3)
' Create iterators
Dim i As Long, j As Long
' Create last rows values for the columns you will be comparing
Dim lr1 As Long, lr2 As Long
' create a reference variable to the next available row in sheet3
Dim nxtRow As Long
' Create ranges to easily reference data
Dim rng1 As Range, rng2 As Range
' Assign values to variables
lr1 = sh1.Range("A" & Rows.Count).End(xlUp).Row
lr2 = sh2.Range("K" & Rows.Count).End(xlUp).Row
' Clear sheet3
sh3.Cells.ClearContents
' Loop through column A on sheet1
For i = 1 To lr1
Set rng1 = sh1.Range("A" & i)
' Loop through column A on sheet1
For j = 1 To lr2
Set rng2 = sh2.Range("K" & j)
' compare the words in column a on sheet1 with the words in column on sheet2
If StrComp(CStr(rng1.Value), CStr(rng2.Value), vbTextCompare) = 0 Then
' now, the words match you want to compare the values
If rng1.Offset(0, 1).Value = rng2.Offset(0, 1).Value Then
' the exact match is found
' manipulate data
'
' find next empty row
nxtRow = sh3.Range("A" & Rows.Count).End(xlUp).Row + 1
' copy the word in column A on sheet2 to the next available row in sheet3
sh3.Range("A" & nxtRow).Value = rng2.Value
' copy the value ( offset(0,1) Column B ) to the next available row in sheet3
sh3.Range("B" & nxtRow).Value = rng2.Offset(0, 1).Value
' display the matching index/row from sheet1 and sheet2
sh3.Range("C" & nxtRow).NumberFormat = "@"
sh3.Range("C" & nxtRow).Value = rng1.Row & "/" & rng2.Row
End If
End If
Set rng2 = Nothing
Next j
Set rng1 = Nothing
Next i
sh3.Rows("1:1").Delete
End Sub
Bookmarks