inserting vlookup via VBA help

abhaysanan

Board Regular
Joined
Jun 1, 2005
Messages
95
I'm trying to do a vlookup with vba where my lookup value changes per row.

Code:
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!
 

Some videos you may like

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

Von Pookie

MrExcel MVP
Joined
Feb 17, 2002
Messages
13,686
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:
Code:
"=VLOOKUP(" & Cells(j,6) & ",TempExtract!A:M,7,FALSE)"
 

Watch MrExcel Video

Forum statistics

Threads
1,114,403
Messages
5,547,750
Members
410,811
Latest member
adustin42
Top