VBA to Vlookup inside a Loop that compares data between 2 sheets and pastes de repeated values in a 3rd sheet

Question

My objective is to compare data in a range in Ws1 with data in a range in ws2, and copy those values that repeat in ws3.

Ideally I would like to copy the value found and the rest of the information to the right of that cell (from ws2), but for now I am happy just copying the value found.

I decided to use a loop for this but I was getting an infinite looping, and now that I re-summarize; I am getting:

range of object _ global failed" error and it points to: "With Range(ws3.cells(i, 1))

 Sub quicktests()

Dim ws1, ws2, ws3 As Worksheet
Dim ColNum, ColNum2 As Long
Dim rng, Range2 As Range
Dim lRow1, lRow2, lCol2 As Integer

ColNum = 9
ColNum2 = 14
lRow1 = 347

Set ws2 = Sheets("Filled today")
With ws2
    lCol2 = .cells(1, .Columns.Count).End(xlToLeft).Column
    'MsgBox lCol2
    lRow2 = .cells(.Rows.Count, 1).End(xlUp).Row
    'MsgBox lRow2
    Set Range2 = .Range(.cells(1, ColNum2), .cells(lRow2, lCol2))
End With

Set ws3 = Sheets("Duplicates filled and hiring")
Set ws1 = Sheets("Reconciliated Recruiment Plan")

For i = 1 To lRow1
    With ws1
        Set rng = .cells(i, ColNum)
    End With

    With Range(ws3.cells(i, 1))
        .Formula = "=VLookup(rng, Range2, ColNum, False)"
        .Value = .Value
    End With

Next i

End Sub

I think you're going to need to be a bit more specific about your an issue to get an answer on stackoverflow. If you haven't already please read How to Ask — Mick
Thanks Mick - I just read it - surely will improve with more use of the site - cheers! — PVL

Scott Craner Scott Craner · Accepted Answer · 2017-08-09T00:11:44

Looking at just the part with the VLOOKUP:

You can't used range with one cells() it needs a begining and an end, remove the Range wrapper.

The Vlookup; You need to remove the vba part from the string and concatenate it.

With ws3.cells(i, 1)
    .Formula = "=VLookup(" & rng.Address(0,0) & "," &  Range2.Address(0,0) & "," &  ColNum & ", False)"
    .Value = .Value
End With

VBA to Vlookup inside a Loop that compares data between 2 sheets and pastes de repeated values in a 3rd sheet

1 Answers