Run-time error 1004: Unable to set the FormulaArray property of the Range class

[jakeman]

Hey, guys - I have a line of VBA that's giving me trouble and I'm not sure how to solve for it.

I have several lines of code that create formulas for me inside of my table and they all work fine. When I tried to do the same using an array formula, I get an error: "Run-time error 1004: Unable to set the FormulaArray property of the Range class."

The formula that is erroring out is:

Range("Player_Matchup_tbl[TSFPP]").FormulaArray = "=IF(MAX(IF([POS]=[@POS],[SFP]))=[@SFP],""Top Player"","""")"

Here is an an example of a formula that works just fine:

Range("Player_Matchup_tbl[AFP]").FormulaR1C1 = "=INDEX(Roster_tbl[FPPG],MATCH([@Player],Roster_tbl[BBall Ref Alias],0))"

My guess is it's got something to do with how the syntax for the Array is to be setup?

 

[JoeMo]

The syntax of the array formula doesn't look right. Try generating the correct array formula directly in the worksheet holding the table, then translate it to your VBA by doubling the quote marks.

 

[jakeman]

hey JoeMo - I ran the VBA recorder and got this code to work:

Selection.FormulaArray = "=IF(MAX(IF([POS]=[@POS],[SFP]))=[@SFP],""Top Player"","""")"

I don't see where the difference is because everything looks the same after the

.FormulaArray = "=IF(MAX(IF([POS]=[@POS],[SFP]))=[@SFP],""Top Player"","""")"

Now, if I could only get the code to work where I tell it where in the table to put the formula.