Make an Index/Match formula dynamic in VBA

[anglais428]

Hi,

I have the following line of code which is taken from a recorded macro and works:

ActiveCell.FormulaR1C1 = _
"=INDEX(INDIRECT(""'""&RC10&""'!""&RC11),MATCH(RC[-4],INDIRECT(""'""&RC10&""'!$A$3:$A$100""),0),MATCH(RC[1],INDIRECT(""'""&RC10&""'!$D$2:$N$2""),0))"

What I would like to do is edit the $A$3:$A$100 range to make it dynamic. The same with the $D$2:$N$2 range.
I was trying to do this using Cells(3,1) etc. so that I could then swap out the numbers (e.g. 3 and 1) with stored variables, allowing the formula to be dynamic, but I keep running into errors.

Any help would be greatly appreciated.

 

[Eric W]

Keep in mind that when entering a formula like that, it's just a long string. You can manipulate it in lots of ways. For example:

    rng1 = "$A$3:$A$100"
    rng2 = "$D$2:$N$2"
    ActiveCell.FormulaR1C1 = _
               "=INDEX(INDIRECT(""'""&RC10&""'!""&RC11),MATCH(RC[-4],INDIRECT(""'""&RC10&""'!" & _
               rng1 & """),0),MATCH(RC[1],INDIRECT(""'""&RC10&""'!" & rng2 & """),0))"

You can define rng1 and rng2 in lots of ways:

rng1 = Cells(3,1)

rng1 = Range("A3:A100").Address(0,1,1,0)

rng1 = Replace("$A$3:$A$100", "3", "4")

and many others.

One thing to keep in mind is that you're mixing the R1C1 and A1 types of references in the same formula. Excel can usually figure it out, but not always.

 

[anglais428]

Thanks Eric