inserting vlookup via VBA help

[abhaysanan]

I'm trying to do a vlookup with vba where my lookup value changes per row.

For j = row To i
    If ActiveSheet.Cells(j, 5) = "USD" And Sheets("TempExtract").Cells(1, 8) = "USD" Then
        ActiveSheet.Cells(j, col).Formula = "=VLOOKUP(Cells(j,6),TempExtract!A:M,7,FALSE)"
        ActiveSheet.Cells(j, col).Copy
        ActiveSheet.Cells(j, col).PasteSpecial xlPasteValues
    End If
Next j

The value I want to lookup is in column 6 of every row. I need to look up that value from another sheet and Insert it into another column in the same row. I cant seem to make my look up value correspond to te current row though. This is pretty simple I guess but I just cant figure out the syntax. Thanks!

 

[Von Pookie]

1) Use a variable name other than "row," as it that is a VBA property. You could use "myrow," for example. Just (try to) make sure it's not something used in VBA.

2) You want to reference the cell in the formula, so do not include it within the quotes holding the formula. In the sample code you posted above, "Cells(j,6)" is considered part of the formula and not a cell reference. You have to separate it from the formula text:

"=VLOOKUP(" & Cells(j,6) & ",TempExtract!A:M,7,FALSE)"

 

[abhaysanan]

Thanks!!! That works perfectly :D