dionysus
dionysus

Reputation: 477

Compare Values in Excel Sheet1 to Values in Sheet2

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

Answers (1)

user3598756
user3598756

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

Related Questions