Application.WorksheetFunction.Match方法 [英] Application.WorksheetFunction.Match method

问题描述

我已经看到很多主题，无法获取工作表函数类的match属性问题。但是我无法让我的代码修复。

为什么这段代码不工作？

  rowNum = Application.WorksheetFunction.Match（aNumber，Sheet5.Range（B16：B615），0）
  

但是，这段代码可以运行几行：

  rowNum2 = Application.WorksheetFunction.Match originCode，Sheet7.Range（B10：B17），0）
  

我的两个行是在rowNum2中，我使用了一个String来查找并在rowNum中使用一个整数。
可能查询值需要是一个字符串？

@更新我的问题

 选择案例服务
案例低成本
 MsgBox Sheet5.Cells（16，B）'获取值0.5 
设置Rng = Sheet5.Range（B16：B615）
 
如果不是IsError（Application.Match（0.5，Rng，0））然后'但跳转到Else 
 rowNum = Application.Match （Weight，Rng，0）'Weight = 0.5 
 MsgBox rowNum 
 Else 
 MsgBoxerror
 End If 
 
 
 
案例标准
 
案例快递
 
案例Else 
 
结束选择
  / pre> 
 
 
 @UPDATE 2 
 
 
 
 !!!注意0.5是一个字符串而不是0.5 
所以0.5不是0.5（这是我的代码错误）
解决方案
您收到此错误是因为该范围内找不到该值。字符串或整数没关系。最好的做法是先做一个检查，看看是否存在这个值。 
 
 
 
我在下面使用了CountIf，但是有很多不同的方法可以检查一个范围内的值的存在。
  Public Sub test（）
 
 Dim rng As Range 
 Dim aNumber As Long 
 
 aNumber = 666 
 
设置rng = Sheet5.Range（B16：B615）
 
如果Application.WorksheetFunction.CountIf（rng，aNumber）> 0然后
 
 rowNum = Application.WorksheetFunction.Match（aNumber，rng，0）
 
 Else 
 MsgBox aNumber& 不存在于范围& rng.Address 
如果
 
 End Sub 
  
 替代方式 
 公共子测试（）
 Dim rng As Range 
 Dim aNumber As Variant 
 Dim rowNum As Long 
 
 aNumber =2gg
 
设置rng = Sheet5.Range（B1：B20）
 
如果不是IsError（Application.Match（aNumber，rng，0））然后
 rowNum = Application.Match（aNumber，rng，0）
 MsgBox rowNum 
 Else 
 MsgBox error
 End If 
 End Sub 
  
或
 公共子测试（）
 Dim rng As Range 
 Dim aNumber As Variant 
 Dim rowNum As Variant 
 
 aNumber =2gg
 
设置rng = Sheet5.Range（B1：B20）
 
 rowNum = Application.Match（aNumber，rng， 0）
 
如果不是IsError（rowNum）然后
 MsgBox rowNum 
 Else 
 MsgBoxerror
 End If 
 End Sub 
  
 
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.

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?

@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

!!! 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)
解决方案
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.
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


                        
这篇关于Application.WorksheetFunction.Match方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！