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

[dylon25]

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]

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