1

I have a large worksheet with a bunch of array formulas. I want to update these formulas using VBA to include an if statement. Basically the I want the new formula to be If(year>max_year, If_True_Formula, If_False_Formula).

This is a fairly simple task to do by joining three strings and setting the ActiveCell.ArrayFormula = The result of the three strings. However, things start to get complicated if the resultant string exceeds 255 characters. In several instances, the formula I needed did exceed 255 characters, so I added the placeholders "X_X" for the If_True_Formula and "Y_Y" for the If_False_Formula and used this method to create the complete formula:

With ActiveCell
    .FormulaArray = If_Statement
    .Replace "X_X", If_True
    .Replace "Y_Y", If_False
End With

This worked perfectly, but several of my array formulas are so long that the If_True portion and If_False portion were each more than 255 characters. To overcome this, I figured I could just divide each of these strings into three pieces (I won't a string more than 750 characters), and then using modified method to create the full string at the end:

True_1 = ""
If len(If_True) > 255 Then
    True_1 = Left(If_True, len(If_True)/3) & "X2_X2"
    True_2 = Mid(If_True, len(If_True)/3, len(If_True)/3) & "X3_X3"
    True_3 = Mid(If_True, len(True_1) + len(True_2) - 10, len(If_True))
End If

This method splits up my big string into three sub-strings, I could then use three replace statements to add to the existing string in three pieces.

With ActiveCell
    .FormulaArray = If_Statement
    If True_1 = "" Then
        .Replace "X_X", If_True
    Else
        .Replace "X_X", True_1
        .Replace "X2_X2", True_2
        .Replace "X3_X3", True_3
    End If
 End With

For some strange reason, the code runs without error but does not make any replacements for True_1, True_2, True_3. If the initial string is not broken up, the code makes the appropriate replacement. Very strange...

Woody Harelson
  • 35
  • 5

1 Answers1

0

I posted an answer here: Array formula with more than 255 characters

The reason is, that the formulas have to make sense to be replaced at any time. So you can't replace with Strings. I made a function that works for english function in A1-notation. If you use the if-command in the formula several times and never used the line 1337 you can copy it and use it without any efford.

Community
  • 1
  • 1
B_Nut
  • 1