CODEWITHSUNDEEP
excelvba
Rhyfelwr
Rhyfelwr

Reputation: 329

Find matching rows across two sheets

I have a sheet1 (sh1) where I have a country name in say (A2) and a direction in (B2).

I would like to find a row on sheet2 (sh2) where column A contains the same city name and column B contains the same district and copy that entire row next to the row on sh1 that was matching. Then I would like to loop through all rows on sh1 and find the matching rows on sh2 and copy it in the same manner.

It might seem I am duplicating data, but the matched row on sh2 contains other information I'd like to copy to sh1.

To illustrate:

On Sheet1:

Column A               Column B
(header)               (header)
San Diego              South
New York               North
Chicago                East

On Sheet2:

Column A              Column B
(header)              (header)
Chicago               East              
San Diego             South
New York              North

The loop would first check for San Diego, then New York, then Chicago and so on until the end of the column.

Here is my code:

Sub Matchcountry()

    Dim sh1 As Worksheet
    Dim sh2 As Worksheet
    Dim r As Range

    Set sh1 = Sheets("Sheet1")
    Set sh2 = Sheets("Sheet2")
    r = lastrow = sh1.Range("A" & Rows.Count) + 2.End(xlUp).Row    

    For x = 1 To r    
        If sh1.Range("A" & x) = sh2.Range("A" & x) And sh1.Range("B" & x) = sh1.Range("A" & x) & sh2.Range("B" & x) Then 
            sh1.Range("A" & x).EntireRow.Copy Destination:=sh2.Range("C" & x)    
        x = x + 1    
    Next x

End Sub

Upvotes: 0

Views: 190

Answers (1)

riskypenguin
riskypenguin

Reputation: 2199

You are already pretty close, try this corrected code (corrections are in comments):

Sub Matchcountry()

Dim sh1 As Worksheet
Dim sh2 As Worksheet
Dim r As Long, r2 As Long 'we just need the row number, not the Range object

Set sh1 = Sheets("Sheet1")
Set sh2 = Sheets("Sheet2")
r = sh1.Range("A" & Rows.Count).End(xlUp).Row 'All the necessary parts were there, just the syntax was wrong
r2 = sh2.Range("A" & Rows.Count).End(xlUp).Row

Dim x As Long, y As Long 'It's good practice to declare all your variables
For x = 1 To r
    For y = 1 To r2
        If sh1.Cells(x, 1).Value2 = sh2.Cells(y, 1).Value2 And sh1.Cells(x, 2).Value2 = sh2.Cells(y, 2).Value2 Then 'Again, most necessary parts were already there
            sh1.Range(sh1.Cells(x, 1), sh1.Cells(x, Columns.Count).End(xlToLeft)).Copy Destination:=sh2.Range("C" & y) 'We don't need the entire row, in fact we won't be able to copy it to the desired renage since it's too big
            Exit For 'will stop the second loop once it's found a match
        End If
    Next y
    'x = x + 1 Common mistake. Next x already iterates x, by doing it this way we skip every second step
Next x

End Sub

The biggest change is the second For loop. We need the second loop since you you want to loop through sh2 for every row of sh1, nut just once.

Upvotes: 1

Related Questions