Help - change VLOOKUP reference using a loop 1

[01491]

I'm having a problem changing the reference in a vlookup formula for each cycle of a For..Next loop.

Current code is:

ActiveCell.FormulaR1C1 = "=IF(ISNA(VLOOKUP(RC1,QryRecDetailsCalc!R2C1:R2C3,3,FALSE)),"""",VLOOKUP(RC1,QryRecDetailsCalc!R2C1:R2C3,3,FALSE))"

I want the 'QryRecDetailsCalc!R2C1:R2C3' reference to move down a line each time through the loop. ie R3C1:R3C3.

Any help much appreciated!!!

 

[dsi]

Build the formula string in the loop:

Dim sRange As String

For i = 2 to 10
    sRange = "R" & i & "C1:R" & i & "C3"

    ActiveCell.FormulaR1C1 = "=IF(ISNA(VLOOKUP (RC1,QryRecDetailsCalc!" & sRange & ",3,FALSE)),"""",VLOOKUP(RC1,QryRecDetailsCalc!" & sRange & ",3,FALSE))"

Next i
[\code]
You could also use VB to copy and paste the formula from the first cell. This will let Excel do the incrementation.

Hope this helps...

 

[01491]

Fantastic !!! Thanks very much.