Application.Match 给出类型不匹配 [英] Application.Match gives type mismatch
问题描述
我正在尝试使用 Application.Match
但是它返回了 type mismatch error:13
错误.为什么?
I am trying to use Application.Match
however it is returning a type mismatch error:13
error. Why?
Dim mySrs as Series
Dim ws as Worksheet
set ws Activesheet
For Each mySrs in ActiveChart.SeriesCollection
tempvar = mySrs.Name
y = Application.Match(tempvar, ws.Range("P37:P71"), 0)
MsgBox y
推荐答案
很可能没有找到匹配项.在这种情况下,Application.Match
返回一个 Excel 错误代码,即一个变量/错误,其值为 Error 2042
(这对应于获取 #N/A
在 Excel 中).
In all likelihood, no match is found. In such a case, Application.Match
returns an Excel error code i.e. a Variant/Error whose value is Error 2042
(this corresponds to getting #N/A
in Excel).
这样的错误值不能被隐式强制转换为字符串(这是 MsgBox
所期望的),因此您会得到类型不匹配.
Such an Error value cannot be implicitly coerced to a String (which is what MsgBox
expects) and thus you get the type mismatch.
请注意,可以使用 WorksheetFunction.Match
调用相同的 Match
函数.唯一的区别是如何处理错误:
Note that the same Match
function can be called using WorksheetFunction.Match
. The only difference is how errors are to be handled:
使用
WorksheetFunction
,错误被视为 VBA 错误,可使用On Error
语法捕获.
With
WorksheetFunction
, errors are treated as VBA errors, trappable using theOn Error
syntax.
使用 Application
,它们返回一个包含在 Variant 中的 Excel 错误代码.您可以使用 IsError
来查看返回的变量是否为 Error 类型变体.
With Application
, they return an Excel error code wrapped in a Variant. You can use IsError
to see if the returned variable is an Error type variant.
这篇关于Application.Match 给出类型不匹配的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!