Reputation: 477
I have an Excel spreadsheet in which I have 2 worksheets. In the first worksheet in Column A I have a Store Names. The second worksheet is a subset of worksheet 1 and also has Store Names, however, they are in Column B of worksheet 2. I need to compare every Store Name (column A) in worksheet 1 to every Store Name (column B) in worksheet 2 and extract where the 2 intersect (have the same Store Names). So far, I have done this in VB with the following:
Sub RunMe()
Dim lRow, x As Long
Sheets("Sheet1").Select
lRow = Range("A2").End(xlDown).Row
For Each cell In Range("B2:B" & lRow)
x = 2
Do
If cell.Value = Sheets("Sheet1").Cells(x, "A").Value Then
cell.EntireRow.Copy Sheets("Sheet3").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
End If
x = x + 1
Loop Until IsEmpty(Sheets("Sheet1").Cells(x, "A"))
Next
End Sub
How can I modify the code above to compare EVERY Store Name (Column A) of Worksheet 1 to EVERY Store Name (Column B) of Worksheet 2?
Upvotes: 0
Views: 406
Reputation: 29421
try this
Sub RunMe()
Dim cell1 As Range, cell2 As Range
Dim rng1 As Range, rng2 As Range
Set rng1 = GetRange("Sheet1",1)
Set rng2 = GetRange("Sheet2",2)
For Each cell2 In rng2
For Each cell1 In rng1
If cell1.Value = cell2.Value Then
cell2.EntireRow.Copy Worksheets("Sheet3").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
End If
Next
Next
End Sub
Function GetRange(shtName As String, colIndex as Long) As Range
With Worksheets(shtName)
Set GetRange = .Range(.Cells(2, colIndex), .Cells(.Rows.Count, colIndex).End(xlUp))
End With
End Function
Upvotes: 1