vba 使用变量评估匹配函数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23884020/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Evaluate Match function with variables
提问by e700k
I would like to use this through VBA -
我想通过 VBA 使用它 -
=MATCH("PlanA",A:A,0)
with EVALUATE.
与EVALUATE.
Sub Test()
Dim SectionStartRow As Integer
Dim planname As String
planname = "PlanA"
SectionStartRow = [MATCH(planname,A:A,0)] 'Error 2029 /// Type mismatch '13
End Sub
I've already tried:
我已经试过了：
SectionStartRow = Evaluate("MATCH(planname,A:A,0)") 'Error 2029 /// Type mismatch '13
and
和
SectionStartRow = Evaluate("MATCH(" & planname & ",A:A,0)")
but nothing seems to work. Please note that plannamevariable is denied by a long set of functions.
但似乎没有任何效果。请注意，planname变量被一长串函数拒绝。
回答by Ioannis
The problem is that plannameneeds to be wrapped around string quotes.
The excel equivalent is =MATCH("PlanA",A:A,0), and the string that is passed to Evaluateshould be identical to that:
问题是planname需要环绕字符串引号。excel 等效项是=MATCH("PlanA",A:A,0)，并且传递给的字符串Evaluate应与该字符串相同：
SectionStartRow = Evaluate("=MATCH( " & Chr(34) & planname & Chr(34) & ",A:A,0)")
works OK (tested with Excel 2010)
工作正常（用 Excel 2010 测试）
Chr(34)stands for the "symbol in ASCII numbering.
Chr(34)代表"ASCII 编号中的符号。
Another way to go about it would be to define
另一种方法是定义
planname = Chr(34) & "PlanA" & Chr(34)
and then do
SectionStartRow = Evaluate("=MATCH( " & planname & ",A:A,0)")
然后做
SectionStartRow = Evaluate("=MATCH( " & planname & ",A:A,0)")
By the way, I would define SectionStartRowas Longand not Integer, because Integerwill throw an error if the matching row is after 32,767.
顺便说一下，我会定义SectionStartRowasLong和 not Integer，因为Integer如果匹配的行在 32,767 之后会抛出错误。
Note also that the shorthand form of Evaluate(that is, the square brackets []) won't work in this case. It works only when everything inside the brackets is a constant, not a variable.
另请注意，在这种情况下Evaluate（即方括号[]）的简写形式将不起作用。仅当括号内的所有内容都是常量而不是变量时才有效。
I hope this helps!
我希望这有帮助！
回答by Alex_P
I stumbled upon this useful question and struggled with connecting not only the plannamebut also resolve it with a different sheet.
我偶然发现了这个有用的问题，并努力不仅将planname它连接起来，而且还用另一张纸解决了它。
I was confused with the double quotation mark and & symbol from the answer by Ioannis.
planname = Chr(34) & "PlanA" & Chr(34)
我对 Ioannis 的回答中的双引号和 & 符号感到困惑。
planname = Chr(34) & "PlanA" & Chr(34)
In my example a version of the below line correctly resolved the first parameter. So only one"& variable &"worked for me.
在我的示例中，以下行的一个版本正确解析了第一个参数。所以只有一个"& variable &"对我有用。
SectionStartRow = Evaluate("=MATCH('Sheet1'!C " & variablename & ",A:A,0)")
SectionStartRow = Evaluate("=MATCH('Sheet1'!C " & variablename & ",A:A,0)")
