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

提示:将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间:2020-09-08 17:17:40  来源:igfitidea点击:

Application.WorksheetFunction.Match method

excelvba

提问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