Long FormulaArray

[XLVBA]

I have a FormulaArray that I am trying to use but it is over the 255 character limit.

The FormulaArray as it should appear in a cell is:

=IF(MAX(IF(A3=Results!A$2:INDEX(Results!$1:$65526, COUNTA(Results!$A:$A), 1),Results!F$2:INDEX(Results!$1:$65526, COUNTA(Results!$F:$F), 6)))=0,"",MAX(IF(A3=Results!A$2:INDEX(Results!$1:$65526, COUNTA(Results!$A:$A), 1),Results!F$2:INDEX(Results!$1:$65526, COUNTA(Results!$F:$F), 6))))

However they are two spots where I need to use a variable (bolded/underlined in the example above).

This was the formula as I was trying to use it:

Please Login or Register  to view this content.

How can I get around this?

[danielexcelvba]

One way to trim characters would be to simply shorten the length of your sheet/variable names (e.g. varNextRow > NxtR or Results > Res)
Another way would be to split the text of the FormulaArray into two, and pass each to a separate variable (e.g. Var1 = "IF(MAX..." and Var2 = ",MAX(IF..."), and then make

Please Login or Register  to view this content.

[XLVBA]

When I try your code above (with the variables populated) I get an error:

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

[danielexcelvba]

I see. You may have to do something like this then
http://dailydoseofexcel.com/archives...rmulas-in-vba/

Where you write a pseudo-function at the end of the first half, and replace it with the second half

[mikerickson]

The OP dynamic ranges could be made Named ranges, which would shorten the formula considerably.

On a side note: INDEX(Results!$1:$65526, COUNTA(Results!$F:$F), 6) could also be expressed as
INDEX(Results!$F$1:$F$65526, COUNTA(Results!$F:$F), 1)

Which would speed execution by making the formula less volatile. As written, the entire sheet is a precedent for the expression, essentially making the INDEX a volatile function. The re-fourmulation makes only column F (plus column A for the whole formula) the precedent.

[XLVBA]

Thanks. I didn't write the formula, it was something that came in a spreadsheet we got from a vendor. I'm having trouble splitting up the formula so that it works with the replace method - I think with the character limit it would have to be broken into three sections? Is there another way where I could put the who formula in a different cell and then use that cell to make the array in the actual cell?

[danielexcelvba]

Splitting it up like this actually seems to work.

Please Login or Register  to view this content.

I believe the correct formula appears in cell "A1"

[XLVBA]

But that doesn't use .FormulaArray