I'm trying to put the formula below into a column. The formula works once I remove RC[-1] but I get an object defined error with it included. Is there anyway I can get formula array to work with both r1c1 and a1 referencing?
Formula assignment which returns an object defined error:
.Range(columnReference).FormulaArray "=MATCH(RC[" & tokyoDigitRC & "] &" & Chr(34) & keyfigure & Chr(34) & "&" & Chr(34) & salesOrg & Chr(34) & "," & tokyoDigitRNG & "&" & keyfigureRNG & "&" & salesOrgRNG & ",0)"
RNG variables are all in the same format e.g "CPL_REFERENCE_DATA!$C$15:$C$120"
What the right side of the assignment returns.
=MATCH(RC[-1] &"NET SALES"&"RU10",CPL_REFERENCE_DATA!$C$15:$C$1207&CPL_REFERENCE_DATA!$D$15:$D$1207&CPL_REFERENCE_DATA!$B$15:$B$1207,0)
Once I remove the RC[-1] and replace is the a cell it returns the correct value.
PS: I know I can iterate through the column and replace RC[-1] with offset but there's a large amount of data and down want the process to be too long.
You cannot mix R1C1 and A1 notation. You must do one or the other.
So change your references:
CPL_REFERENCE_DATA!$C$15:$C$120
to R1C1 style:
CPL_REFERENCE_DATA!R15C3:R120C3
Without the [] the reference is relative to the page and absolute.