调整公式的公式 [英] Adjusting the formula in formulaarray
本文介绍了调整公式的公式的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有以下公式,我想输入公式,但由于大小限制,我不能这样做。
= VLOOKUP(MIN(IF(ABS('S:\AT Reports\Abhor - Ganganagar\ [1 1550 AB.xls] 1 1550 A-B'!$ C $ 17:$ C $ 42-B5 * 1000) = MIN(ABS('S:\AT Reports\Abhor - Ganganagar\ [1 1550 AB.xls] 1 1550 A-B'!$ C $ 17:$ C $ 42-B5 * 1000)),IF ('S:\AT Reports\Abhor - Ganganagar\ [1 1550 AB.xls] 1 1550 A-B'!$ C $ 17:$ C $ 42-B5 * 1000)< 500,'S:\ AT报告\Abhor - Ganganagar\ [1 1550 AB.xls] 1 1550 A-B'!$ C $ 17:$ C $ 42,))),'S:\AT Reports\Abhor - Ganganagar\ [ 1 1550 AB.xls] 1 1550 A-B'!$ C $ 17:$ E $ 42,3,FALSE)
上面你看到的是VBA程序的结果。 VBA代码中的实际公式如下:
formulaValue == VLOOKUP(MIN(IF(ABS(' ; initpath&(n / 3 - 2)&& freqSheet&AB.xls]&(n / 3 - 2)&& freqSheet&A-B! ($ C $ 17:$ C $ 42-& ColToSelect&5 * 1000)= MIN(ABS('& initpath&(n / 2-3)&& freqSheet& xls]&(n / 3 - 2)&& freqSheet&A-B'!$ C $ 17:$ C $ 42-& ColToSelect&5 * 1000)),IF ABS('& initpath&(n / 3 - 2)&& freqSheet&AB.xls]&(n / 3 - 2)&& freqSheet& A-B'!$ C $ 17:$ C $ 42-& ColToSelect&5 * 1000)< 500"& initpath&(n / 3 - 2)&& freqSheet& ;AB.xls&(n / 3 - 2)&& freqSheet&A-B'!$ C $ 17:$ C $ 42,)))''& initpath& (n / 3 - 2)&& freqSheet&AB.xls]&(n / 3 - 2)&& freqSheet&A-B'!$ C $ 17:$ E $ 42,3,FALSE)
ActiveCell.Formula = formulaValue
有人可以帮我吗?
解决方案
FormulaArray
有255个字符的限制。
您将不得不拆分公式
和替换
部分,使其重新整理。
请参阅以下代码:
Dim $ As $
$ b 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)<
fLoc ='S:\AT Reports\\ \\ Abhor - Ganganagar\ [1 1550 AB.xls] 1 1550 A-B'!$ C $ 17
With ActiveCell
.FormulaArray = Formula
.Replace$ C $ 17,fLoc
结束
编辑: / p>
在您的公式
更新后,这是如何使用变量:
Formula == VLOOKUP(MIN(IF(ABS($ C $ 17:$ C $ 42-& ColToSelect&5 * 1000)= MIN ABS($ C $ 17:$ C $ 42-&ColToSelect5 * 1000)),IF(ABS($ C $ 17:$ C $ 42-& ColToSelect5 * 1000)< 500, $ C $ 17:$ C $ 42,))),$ C $ 17:$ E $ 42,3,FALSE)
fLoc ='& initpath&(n / 3 - 2) && freqSheet&AB.xls]&(n / 3 - 2)&& freqSheet&A-B'!$ C $ 17
使用ActiveCell
.FormulaArray =公式
。替换$ C $ 17,fLoc
结束
I have the following formula which i want to enter in formulaarray but am not able to do so due to size limitations.
=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)
What you see above is the result of the VBA program. The actual formula in the VBA code is as follows:
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
Can someone please help me with this?
解决方案
The FormulaArray
has a 255 characters limit.
You will have to split up the Formula
and Replace
parts to make it whole again.
See code below:
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:
After your update about your Formula
this is how to do it with Variables:
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
这篇关于调整公式的公式的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文