Why am I getting run time error 1004 for one part of this code only?

[Bert_Withpeterson]

Hi all,

The below code works with regards to populating cells B3 and B8, but when it gets to populating C8 it throws up 'run time error 1004' - I can't see what the difference is with this compared to the other two sections of code, that don't trigger an error. Can anyone help please?


'Index match the most recent date
Range("B3").Select
Selection.FormulaArray = _
"=INDEX('PublicHealthOutcomesFramework-D'!C[12],MATCH(1,(R[-2]C='PublicHealthOutcomesFramework-D'!C)*(R[-1]C='PublicHealthOutcomesFramework-D'!C[1])*(R[21]C[-1]='PublicHealthOutcomesFramework-D'!C[11]),0))"


'Add in the index match formulae


Range("B8").Select
Selection.FormulaArray = _
"=INDEX('PublicHealthOutcomesFramework-D'!C[13],MATCH(1,(R[-7]C='PublicHealthOutcomesFramework-D'!C)*(R[-6]C='PublicHealthOutcomesFramework-D'!C[1])*(R[-5]C='PublicHealthOutcomesFramework-D'!C[12])*(RC[-1]='PublicHealthOutcomesFramework-D'!C[5]),0))"


Range("C8").Select
Selection.FormulaArray = _
"=INDEX('PublicHealthOutcomesFramework-D'!C[13],MATCH(1,(R[-7]C[-1]='PublicHealthOutcomesFramework-D'!C[-1])*(R[-6]C[-1]='PublicHealthOutcomesFramework-D'!C)*(R[-5]C[-1]='PublicHealthOutcomesFramework-D'!C[11])*(RC[-2]='PublicHealthOutcomesFramework-D'!C[4]),0))"

 

[RoryA]

The third formula is over 255 characters, which is why it fails - it's a limitation of the formulaarray property. Can you shorten the sheet name?

 

[Bert_Withpeterson]

Ah great, resolved now thank you so much.