ARRAYFORMULA超过255个字符.Replace不工作 [英] ArrayFormula More than 255 Characters .Replace Not Working

问题描述

我有一堆数组公式的大型工作表。我想用VBA来包括if语句来更新这些公式。基本上，我希望新的公式是如果（年> MAX_YEAR，If_True_Formula，If_False_Formula）。

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).

这是一个相当简单的任务，通过连接三个字符串，并设置ActiveCell.ArrayFormula =的三根弦的结果做。但是，如果结果字符串超过255个字符的事情开始变得复杂起来。在一些情况下，我需要做的公式超过255个字符，所以我加了If_False_Formula为If_True_Formula占位符X_X和Y_Y，并用这个方法来创建完整的公式：

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

这完美地工作，但我的几个数组公式都长，所以IF_TRUE部分和IF_FALSE部分分别超过255个字符。为了克服这个问题，我想我可能只是将每个这些字符串成三块（我不会一个字符串超过750个字符），然后用改良的方法来创建在最后完整的字符串：

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

有关一些奇怪的原因，code运行没有错误，但不会使True_1，True_2，True_3任何替换。如果初始字符串不破了，在code进行适当的替代。很奇怪...

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...

推荐答案

我在这里张贴一个答案：的数组公式具有超过255个字符

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

的原因是，该公式具有有意义随时更换。所以你不能用字符串替换。我提出，在A1-符号英语功能工作的功能。如果您在公式中使用if命令几次，从来没有使用过该行1337，您可以将它复制并使用它没有任何efford。

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.

这篇关于ARRAYFORMULA超过255个字符.Replace不工作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！