调整公式数组中的公式

我有以下公式，我想将其输入到 Formulaarray 中，但由于大小限制而无法这样做。

=VLOOKUP(MIN(IF(ABS('S:\AT Reports\Abhor - Ganganagar\[1 1550 A-B.xls]1 1550 A-B'!$C$17:$C$42-B5*1000)=MIN(ABS('S:\AT Reports\Abhor - Ganganagar\[1 1550 A-B.xls]1 1550 A-B'!$C$17:$C$42-B5*1000)),IF(ABS('S:\AT Reports\Abhor - Ganganagar\[1 1550 A-B.xls]1 1550 A-B'!$C$17:$C$42-B5*1000)< 500,'S:\AT Reports\Abhor - Ganganagar\[1 1550 A-B.xls]1 1550 A-B'!$C$17:$C$42,))),'S:\AT Reports\Abhor - Ganganagar\[1 1550 A-B.xls]1 1550 A-B'!$C$17:$E$42,3,FALSE)

上面你看到的是VBA程序的结果。 VBA代码中的实际公式如下：

formulaValue = "=VLOOKUP(MIN(IF(ABS('" & initpath & (n / 3 - 2) & " " & freqSheet & " A-B.xls]" & (n / 3 - 2) & " " & freqSheet & " A-B'!$C$17:$C$42-" & ColToSelect & "5*1000)=MIN(ABS('" & initpath & (n / 3 - 2) & " " & freqSheet & " A-B.xls]" & (n / 3 - 2) & " " & freqSheet & " A-B'!$C$17:$C$42-" & ColToSelect & "5*1000)),IF(ABS('" & initpath & (n / 3 - 2) & " " & freqSheet & " A-B.xls]" & (n / 3 - 2) & " " & freqSheet & " A-B'!$C$17:$C$42-" & ColToSelect & "5*1000)< 500,'" & initpath & (n / 3 - 2) & " " & freqSheet & " A-B.xls]" & (n / 3 - 2) & " " & freqSheet & " A-B'!$C$17:$C$42,))),'" & initpath & (n / 3 - 2) & " " & freqSheet & " A-B.xls]" & (n / 3 - 2) & " " & freqSheet & " A-B'!$C$17:$E$42,3,FALSE)"

            ActiveCell.Formula = formulaValue

有人可以帮我解决这个问题吗？

The FormulaArray有 255 个字符的限制。

你将不得不分开Formula and Replace零件使之再次完整。
参见下面的代码：

Dim Formula As String
Dim fLoc As String

Formula = "=VLOOKUP(MIN(IF(ABS($C$17:$C$42-B5*1000)=MIN(ABS($C$17:$C$42-B5*1000)),IF(ABS($C$17:$C$42-B5*1000)< 500,$C$17:$C$42,))),$C$17:$E$42,3,FALSE)"


fLoc = "'S:\AT Reports\Abhor - Ganganagar\[1 1550 A-B.xls]1 1550 A-B'!$C$17"

With ActiveCell
    .FormulaArray = Formula
    .Replace "$C$17", fLoc   
End With

Edit:

更新您的信息后Formula这是使用变量执行此操作的方法：

Formula = "=VLOOKUP(MIN(IF(ABS($C$17:$C$42-" & ColToSelect & "5*1000)=MIN(ABS($C$17:$C$42-" & ColToSelect & "5*1000)),IF(ABS($C$17:$C$42-" & ColToSelect & "5*1000)< 500,$C$17:$C$42,))),$C$17:$E$42,3,FALSE)"

fLoc = "'" & initpath & (n / 3 - 2) & " " & freqSheet & " A-B.xls]" & (n / 3 - 2) & " " & freqSheet & " A-B'!$C$17"

With ActiveCell
    .FormulaArray = Formula
    .Replace "$C$17", fLoc  
End With