Error with Insert Formula VBA

[thefunstarts]

I'm having an issue with a piece of VBA used to insert an Index Match formula. Below is the code I'm using but it's returning an error - I can't figure out why! Can anyone help?!

Range("E8").Formula = "=IFERROR(INDEX('Data'!$C$2:$XFD$10000, MATCH(1,('Data'!$C$2:$C$10000=Dashboard!$B8)*('Data'!$I$2:$I$10000=Dashboard!$E$6),0),12),"")"

 

[bbott]

You need to double up any quotes that appear in the formula:

Range("E8").Formula = "=IFERROR(INDEX('Data'!$C$2:$XFD$10000, MATCH(1,('Data'!$C$2:$C$10000=Dashboard!$B8)*('Data'!$I$2:$I$10000=Dashboard!$E$6),0),12),[COLOR=#ff0000]"[/COLOR]""[COLOR=#ff0000]"[/COLOR])"

 

[thefunstarts]

I'm having an issue with a piece of VBA used to insert an Index Match formula. Below is the code I'm using but it's returning an error - I can't figure out why! Can anyone help?!

Range("E8").Formula = "=IFERROR(INDEX('Data'!$C$2:$XFD$10000, MATCH(1,('Data'!$C$2:$C$10000=Dashboard!$B8)*('Data'!$I$2:$I$10000=Dashboard!$E$6),0),12),"")"

I just realized I had to double quotes for the end of the formula. But I noticed once it inputs the formula you still have to go in a refresh it by clicking "Shift + Ctrl + Enter", is there anyway to automatically update the formulas once inserted in VBA?

 

[bbott]

If you're entering it as an array formula change:

Range("E8").Formula

to

Range("E8").FormulaArray