Having trouble splitting up long FormulaArray

[TheJoelteon]

Hi everyone,

Ive been having some trouble with an array formula that's over 255 characters, and ive been trying to split it up into sub/dummy functions, but it doesnt seem to be working. Can someone please take a look at my code and see if you spot anything?

Sub LongArray_ProvincialCalcs()


    Dim Formula_Full As String
    Dim Formula_Get_ARHCA As String
    Dim Formula_Apply_ARHCA As String
    Dim Formula_Get_KEYWORD As String
    
    Formula_Full = "=IFERROR(H6*(IFERROR(IF(Get_ARHCA, Get_KEYWORD, Apply_ARHCA))))"
    
        Formula_Get_ARHCA = "IF(ISNUMBER(VALUE(LEFT(L6,1)))=TRUE,VLOOKUP(MID(L6,FIND("" "",L6)+1,10),RATES,2)"
        Formula_Apply_ARHCA = "H6*Keywords!$B$6"
        Formula_Get_KEYWORD = "VLOOKUP((INDEX(KEYWORDS, MATCH(1, COUNTIF(L6, ""*"" & KEYWORDS & ""*""), 0))), RATES, 2, FALSE)), "" ""))"
        
    With Worksheets("Draft_Version").Range("I5")
    
        .FormulaArray = Formula_Full
        .Replace Get_ARHCA, Formula_Get_ARHCA
        .Replace Get_KEYWORD, Formula_Get_KEYWORD
        .Replace Apply_ARHCA, Formula_Apply_ARHCA
    End With
    
End Sub

Right now the debug is having issues with the .FormulaArray = Formula_Full part, giving me error "unable to set the formulaarray property of the range class."

Thoughts?

 

[yky]

What is Keywords as in "H6*Keywords!$B$6"?

 

[StephenCrump]

Your immediate problem is that you don't have enough arguments for your two IFERROR functions. Perhaps you meant ISERROR?

Once that's working, it looks you're missing a closing bracket in Formula_Get_ARHCA?

You might also want to test the formula in Formula_Get_KEYWORD. It's not immediately clear to me whether this is a valid formula.

Finally, with your .Replaces, you'll need inverted commas around the strings, e.g.

.Replace "Get_ARHCA", Formula_Get_ARHCA

 

[TheJoelteon]

What is Keywords as in "H6*Keywords!$B$6"?

Keywords is just a named table that contains my rates for the various cost charges (OT labour, Operator, etc.)
The specific case you referenced was to use a generic rate (B6 with the absolute referencing) for the default IFERROR case.

 

[TheJoelteon]

Your immediate problem is that you don't have enough arguments for your two IFERROR functions. Perhaps you meant ISERROR?

Thanks for pointing this out! i will double check, though the function seemed to be working the way i needed to in the past. Having the two IFERROR's seems a bit redundant however.

Once that's working, it looks you're missing a closing bracket in Formula_Get_ARHCA?

You might also want to test the formula in Formula_Get_KEYWORD. It's not immediately clear to me whether this is a valid formula.

Finally, with your .Replaces, you'll need inverted commas around the strings, e.g.

.Replace "Get_ARHCA", Formula_Get_ARHCA

sounds good, ill give this a go and get back to you.

Thanks for the suggestions everyone!

 

[TheJoelteon]

You might also want to test the formula in Formula_Get_KEYWORD. It's not immediately clear to me whether this is a valid formula.

It appears that formula is working correctly; I tried to break down each part of it individually in the pic below; Under the COMMENTS column (L), i was operating on L6, "Equipment;"
The chunk of the formula in question is in cell )9, with the correct output of 0.5 (orange). This would be the payout rate on general equipment (50%).

 

[StephenCrump]

Your code is breaking down at this first step:

Formula_Full = "=IFERROR(H6*(IFERROR(IF(Get_ARHCA, Get_KEYWORD, Apply_ARHCA))))"

Try putting this formula: =IFERROR(H6*(IFERROR(IF(Get_ARHCA, Get_KEYWORD, Apply_ARHCA)))) in to an Excel cell and you'll see the problem. You haven't specified the 2nd argument for both IFERROR functions.

I don't know what the formula is meant to be, and can't figure it out from the picture.

But step 1 is to make sure it is a valid formula.