Formula works in sheet but syntax error pops in VBA

[kermja13]

Hi there,

This formula works in the sheet but it won't compile in VBA due to a syntax error. Any tips? Thank you!

As it appears in the sheet: =INDIRECT(SUBSTITUTE(ADDRESS(1, COLUMN(), 4),1,"")&"$"&$A8)*(Y8/100)

As it appears in VBA: Sheets(shName).Range("C" & b + 1).Formula = "=INDIRECT(SUBSTITUTE(ADDRESS(1, COLUMN(), 4),1,"")&"$"&$A8)*(Y8/100)"

 

[kermja13]

I think it has something to do with one of the $ signs but if I double quote the $ (""$"") a 1004 error pops when I run it

 

[Fluff]

Hi & welcome to MrExcel.
You need to double-up on the quotes inside the formula like

 Sheets(shName).Range("C" & b + 1).Formula = "=INDIRECT(SUBSTITUTE(ADDRESS(1, COLUMN(), 4),1,"""")&""$""&$A8)*(Y8/100)"