Keeping VLOOKUP table array fixed in VBA

[bikeman]

I am using VBA to run a VLOOKUP, but when it runs, the table array moves and therefore is not returning results. Below is the syntax and I am having trouble fixing the range to

..ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],RC[-7]:R[1000]C[-6],2,FALSE)"..

each time this moves down a row, the array misses entries above it in the table. Any suggestions?

Thanks

 

[niceguy_jay]

can you specify the range (columns and rows) that you are using in the range of the vlookup ?

 

[bikeman]

This is the VLOOKUP that I am trying to get working in the macro

=VLOOKUP(G2,$A$2:$B$1000,2,FALSE)

Hope that gives enough info.

 

[niceguy_jay]

try using this entire syntax instead of the one you specified....

ActiveCell.Formula = "=VLOOKUP(G2,$A$2:$B$1000,2,FALSE)"

 

[bikeman]

Thanks niceguy,

I've tried that had got an error, but you put me on the right track again.
I got it to work using this
"=VLOOKUP(RC[-1],R2C[-7]:R1000C[-6],2,FALSE)"

Cheers