Why Formula R1C1 put bracket automatically?

Hi guys,

This is probably a stupid question but i'm currently struggling with this right now. Below is my line of code to put formula into Cell D4

Me.Sheets("ETF Holdings").Cells(4, 4).FormulaR1C1 = "=IFERROR(INDEX('FV Database'!B:B,MATCH('ETF Holdings'!RC[-2],'FV Database'!A:A,0)),INDEX('FV Database'!B:B,MATCH(SUBSTITUTE('ETF Holdings'!RC[-2],""."",""-""),'FV Database'!A:A,0)))"

why it turn to be this when it being put in the cell?

=IFERROR(INDEX('FV Database'!B:(B),MATCH('ETF Holdings'!B4,'FV Database'!A:(A),0)),INDEX('FV Database'!B:(B),MATCH(SUBSTITUTE('ETF Holdings'!B4,".","-"),'FV Database'!A:(A),0)))

The problem is on the FV Database'!B:(B) part, why it's turn out to have a bracket??

I seldom use R1C1 style so kindly help to enlighten me, thanks in advance !!

Hi Norie,

Thanks for the reply. Sorry if i didnt make it clear, the reason i need to use R1C1 style is because i will loop the formula later on, so in my code whereever i put the formula, the reference will always be RC[-2], which is 2 column on the left of the formula.

But for the Array column reference

( FV Database'!B:(B) )

i don't want it to change when i move it to another cell, Hope this makes it clear.

I tried that also C1 turn out to be E:E, and C2 turn out to be F:F when the formula is in the cell

This is actually a long code,to avoid confusion i didn't put it in..

This part of my code

Me.Sheets("ETF Holdings").Cells(4, 4).FormulaR1C1

is actually..

Me.Sheets("ETF Holdings").Cells(Lr, Sc).FormulaR1C1

So it will put the formula in a different cells on the loop

OMG....now it works..wow..i really don't know what happen, i tried that before but it doesn't work and now it works...

Whew..i think that shows how stressed and tired i am now, thanks Norie for your time ^^