CODEWITHSUNDEEP
excelexcel-formulamatchexcel-r1c1-notationvba
mcseth antwi
mcseth antwi

Reputation: 131

VBA: R1C1 Notation not being recognised with Range.FormulaArray

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.

Upvotes: 0

Views: 1230

Answers (1)

Scott Craner
Scott Craner

Reputation: 152505

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.

Upvotes: 1

Related Questions