Search code examples
vbaexcelvlookupevaluate

Combining EVALUATE with VLOOKUP in VBA

I'm trying to perform a series of VLOOKUPs in VBA and figured that there must be a way to do it in batch with the EVALUATE function, rather than loop through the values on the sheet individually (which I already know how to do). But I'm struggling with making the lookup value "dynamic". This is what I've got so far:

Dim ws1 as worksheets, ws2 as worksheet, rngJ2 as Range, Lastrow2 as long

Set ws1 = ActiveWorkbook.Sheets("Sheet1")
Set ws2 = ActiveWorkbook.Sheets("Sheet2")

 LastRow2 = ws2.Cells.Find(What:="*", _
                After:=Range("A1"), _
                LookAt:=xlPart, _
                LookIn:=xlFormulas, _
                SearchOrder:=xlByRows, _
                SearchDirection:=xlPrevious, _
                MatchCase:=False).Row

set rngJ2 = ws2.range("J2:J" & Lastrow2)

Now here is what I want to do next, knowing that the rngJ2.address(row) part is not the correct syntax:

rngJ2.Value= Evaluate("VLOOKUP(Sheet2!I" & rngJ2.address(row) & ",Sheet1!A:H,8,FALSE)")

What I'm trying to do with the rngJ2.address(row) part is defining the appropriate row for each cell in the range so that Cell J5 uses I5 as the lookup value, J12 uses I12, etc.

Any ideas?

Solution

  • There really isn't any need for Evaluate there at all. You can use the Worksheetfunction version instead:

    rngJ2.Value = WorksheetFunction.VLookup(rngJ2.Offset(, -1), Sheets("Sheet1").Range("A:H"), 8, False)