vba Application.WorksheetFunction.Match 方法
声明:本页面是StackOverFlow热门问题的中英对照翻译,遵循CC BY-SA 4.0协议,如果您需要使用它,必须同样遵循CC BY-SA许可,注明原文地址和作者信息,同时你必须将它归于原作者(不是我):StackOverFlow
原文地址: http://stackoverflow.com/questions/20214540/
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
Application.WorksheetFunction.Match method
提问by user3035828
I have seen a lot of Topics to the "unable to get the match property of the Worksheetfunction class" problem. But I can't get my code fixed.
我已经看到很多关于“无法获取 Worksheetfunction 类的匹配属性”问题的主题。但我无法修复我的代码。
Why isn't this code work?
为什么这段代码不起作用?
rowNum = Application.WorksheetFunction.Match(aNumber, Sheet5.Range("B16:B615"), 0)
But a few rows higher this code works:
但是此代码在几行之后有效:
rowNum2 = Application.WorksheetFunction.Match(originCode, Sheet7.Range("B10:B17"), 0)
The only difference between my two lines is that in rowNum2 I used a String for look up and in rowNum a integer. Is it possible that the look up Value needs to be a String?
我的两行之间的唯一区别是,在 rowNum2 中,我使用了一个字符串进行查找,而在 rowNum 中使用了一个整数。查找值是否可能需要是字符串?
@Update on my Problem
@更新我的问题
Select Case service
Case "Low Cost"
MsgBox Sheet5.Cells(16, "B") 'Gets value 0.5
Set Rng = Sheet5.Range("B16:B615")
If Not IsError(Application.Match("0.5", Rng, 0)) Then 'But jumps to Else
rowNum = Application.Match(Weight, Rng, 0) 'Weight = 0.5
MsgBox rowNum
Else
MsgBox "error"
End If
Case "Standard"
Case "Express"
Case Else
End Select
@UPDATE 2
@更新 2
!!! Take care that "0.5" is a String and not 0.5 So 0.5 is not "0.5" (that was my error in the code)
!!!注意“0.5”是一个字符串而不是 0.5 所以 0.5 不是“0.5”(这是我在代码中的错误)
回答by Sam
You are getting this error because the value cannot be found in the range. String or integer doesn't matter. Best thing to do in my experience is to do a check first to see if the value exists.
您收到此错误是因为在该范围内找不到该值。字符串或整数无关紧要。根据我的经验,最好的办法是先检查一下该值是否存在。
I used CountIf below, but there is lots of different ways to check existence of a value in a range.
我在下面使用了 CountIf,但是有很多不同的方法可以检查某个范围内的值是否存在。
Public Sub test()
Dim rng As Range
Dim aNumber As Long
aNumber = 666
Set rng = Sheet5.Range("B16:B615")
If Application.WorksheetFunction.CountIf(rng, aNumber) > 0 Then
rowNum = Application.WorksheetFunction.Match(aNumber, rng, 0)
Else
MsgBox aNumber & " does not exist in range " & rng.Address
End If
End Sub
ALTERNATIVE WAY
替代方式
Public Sub test()
Dim rng As Range
Dim aNumber As Variant
Dim rowNum As Long
aNumber = "2gg"
Set rng = Sheet5.Range("B1:B20")
If Not IsError(Application.Match(aNumber, rng, 0)) Then
rowNum = Application.Match(aNumber, rng, 0)
MsgBox rowNum
Else
MsgBox "error"
End If
End Sub
OR
或者
Public Sub test()
Dim rng As Range
Dim aNumber As Variant
Dim rowNum As Variant
aNumber = "2gg"
Set rng = Sheet5.Range("B1:B20")
rowNum = Application.Match(aNumber, rng, 0)
If Not IsError(rowNum) Then
MsgBox rowNum
Else
MsgBox "error"
End If
End Sub