# VBA Index and Match with Formula Array, Ranges in R1C1 only?



## dylon25 (Jan 2, 2023)

Hi, any help here would be appreciated:

As you can see from the formula below, the parameters for the index/match formula are ranges taken from the "variable_1.csv" workbook.  
The formula works fine at this point
Windows("book1").Activate
 Range("C4").Select
 Selection.FormulaArray = _
        "=INDEX(variable_1.csv!R2C1:R3239C10,MATCH(1,(RC1=variable_1.csv!R2C2:R3239C2)*(RC2=variable_1.csv!R2C3:R3239C3)*(R3C=variable_1.csv!R2C5:R3239C5),0),MATCH(Input!R4C2,variable_1.csv!R1C1:R1C10,0))"

Instead of hardcoded ranges though, I want to save each range in a range variable or Named Range and then use those in the formula instead
for example
dim x as range
set x = variable_1.csv!$A$2:$J$3239
'

Selection.FormulaArray = _
        "=INDEX(x,MATCH(1,(RC1=variable_1.csv!R2C2:R3239C2)*(RC2=variable_1.csv!R2C3:R3239C3)*(R3C=variable_1.csv!R2C5:R3239C5),0),MATCH(Input!R4C2,variable_1.csv!R1C1:R1C10,0))"



I replaced the first argument with x, and I would want to do the same thing for all the other ranges in the formula.  However it doesn't work, it seems all ranges must be in R1C1 style.  How can I convert the ranges to R1C1 so that they can be used in this formula.

Thanks


----------



## hajiali (Wednesday at 11:50 PM)

can you post an example of what the formula should be in a view of the ranges?


----------

