excel - 调整公式数组中的公式

Question

我有以下公式，我想在公式数组中输入，但由于大小限制而无法这样做。

=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

有人可以帮我吗？

Answer 1

FormulaArray有 255 个字符的限制。

您将不得不拆分 Formula和 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

编辑:

在您更新您的 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