excelvbaarray-formulasexcel-r1c1-notation

Why is Excel VBA only accepting a relative R1C1 reference in this array formula?

I am trying to add this line of code to my VBA. It adds an array formula to a range of cells:

str_Formula = "=INDEX(rng_List,MATCH(1,--(RC1>=rng_A)*--(RC1<=rng_B),0))"
rng_Formula.FormulaArray = str_Formula

For some reason, the formula only computes if I use a relative R1C1 reference (RC[-22]) instead. If I use the absolute form above, then it tries to use the value of cell RC1 (in A1 reference notation).

Is there a way I can force VBA to read RC1 as an R1C1 reference?

Solution

  • No, there's no way I can force VBA to read RC1 as an R1C1 reference.

    From Scott Craner above:

    "Because there is a column RC you cannot do an absolute column and relative row without the [] after the R. You can do an absolute row and relative column R1C and it will work, but it will first try A1 and if that works it will use it, otherwise it will try R1C1."