CODEWITHSUNDEEP
vbaexcel
Z.ovesen
Z.ovesen

Reputation: 21

Loop - Match values in two columns in different worksheets, copy entire row to new worksheet if match

I'm new in VBA coding, and would really appreciate some help solving this problem.

I need to do as follows:

I've tried this so far:

Sub test()

    Application.ScreenUpdating = False

    Dim rng1 As Range, rng2 As Range, rngName As Range, i As Integer, j As Integer

    For i = 1 To Sheets("Worksheet1").Range("G" & Rows.Count).End(xlUp).Row
        Set rng1 = Sheets("Worksheet1").Range("G" & i)

    For j = 1 To Sheets("Worksheet2").Range("D" & Rows.Count).End(xlUp).Row
        Set rng2 = Sheets("Worksheet2").Range("D" & j)

        Set rngName = Sheets("Worksheet1").Range("H" & j)

        If rng1.Value = rng2.Value Then
        rngName.Copy Destination:=Worksheets("Worksheet3").Range("B" & i)
        End If

        Set rng2 = Nothing
    Next j
    Set rng1 = Nothing
 Next i

End Sub

But it doesn't work.

Upvotes: 2

Views: 2621

Answers (2)

trincot
trincot

Reputation: 351218

There is a problem with this statement:

Set rngName = Sheets("Worksheet1").Range("H" & j)

The variable j refers to a row in Worksheet2, but you use it on Worksheet1. Depending on what you intended here, you should either change the worksheet name or use the variable i instead of j.

Assuming it is the first, the code could also be written as:

Dim rng1 As Range, rng2 As Range

' Iterate over the used cells in the G column of Worksheet1
For Each rng1 In Sheets(1).UsedRange.Columns(8 - Sheets(1).UsedRange.Column).Cells
    ' Iterate over the used cells in the D column of Worksheet2
    For Each rng2 In Sheets(2).UsedRange.Columns(5 - Sheets(2).UsedRange.Column).Cells
        If rng1.Value = rng2.Value Then 
            ' Copy value from the C column in Worksheet2 to the B column in Worksheet3
            Sheets(3).Cells(rng2.Row, 2).Value = rng2.Offset(0, -1).Value 
        End If
    Next
Next

Alternative to VBA code

Instead of using code, you could do this with formulas.

For instance in Worksheet3 you could put this formula in B1:

=INDEX(Worksheet2!$C:$C, MATCH(Worksheet1!$G1,Worksheet2!$D:$D, 0))

Here is an explanation of the two main parts of that formula:

MATCH(Worksheet1!$G1, Worksheet2!$D:$D, 0)

This part will take the value from Worksheet1!$G1, find it in Worksheet2!$D:$D (i.e. the complete D column) and return the row number where it was found. The last argument (0) makes sure that only exact matches count.

INDEX(Worksheet2!$C:$C, ...)

The row number returned by MATCH will be used to get a value from the C column of Worksheet2, at that same row.

You can change that $C:$C by $H:$H to get the value from the H column, etc.

Drag/copy the formula downwards to repeat it for other rows.

Upvotes: 2

RobK
RobK

Reputation: 207

I would use the Cells property and a Do loop to loop through G on WS1. Try something like this:

Dim i as Integer, j as Integer
Dim c as Range
i = 2 'Will be used to loop through WS1, Column G
j = 1 'Will be used to find next empty row in WS3

Do Until Sheets(1).Cells(i, 7).Value = ""

    Set c = Sheets(2).Range("D2")
        Do Until c.value = Sheets(1).Cells(i, 7).Value Or c.value = ""
            Set c = c.Offset(1, 0)
        Loop

    If c.value = Sheets(1).Cells(i, 7).Value Then
        'Find first empty row in WS3
        j = 1
        Do Until Sheets(3).Cells(j, 1).Value = ""
            j = j + 1
        Loop

        'Copy row
        Sheets(3).Rows(j).value = Sheets(1).Rows(I).value

    End if

    i = i + 1

Loop

Set c = Nothing

Upvotes: 0

Related Questions