formula in vba help

[spq24]

ActiveCell.FormulaR1C1 = "=IF(RC[-1]="""", """", RC[-16,1]/VLOOKUP('Sales Data'!RC[-22],'Regional Pipeline map'!R1C1:R4C29,3,FALSE))"


=IF(V2="","",G2/VLOOKUP('Sales Data'!A3,'Regional Pipeline map'!$A$1:$D$29,3,FALSE))


I need to create a formula like the 2nd formula. Above that is what I have written in VBA currently. Everything works currently except the vlookup in the formula is showing 'Sales Data'!A2 instead of 'Sales Data'!A3 (when it's in cell A2). Does anyone know how I can get it to go to cell A3?

 

[RSXchin]

Do you HAVE to use the R1C1 method? or is A1, B1 fine?

also, what cell is this going into?

 

[AlphaFrog]

Try something like....
'Sales Data'!R[1]C[-22]

Alternatively, you can manually put your formula in a cell like you normally would, then go to Tools\Options|General and turn on the R1C1 reference style to convert the formula to R1C1 notation to use it in VBA.

 

[spq24]

Sorry I forgot to mention that I need this to go down an entire data list. So when it's in cell A2 it will reference cell 'Sales Data'!A3 when it's in cell A3 it will reference cell 'Sales Data'!A4 etc. and this dataset will grow throughout the year so it needs to be able to grow with it