在宏中使用Match或VLookup [英] Using Match or VLookup from within a macro

问题描述

$ b $，我会得到相同的结果b

我想使用第1列中的键在参考表中查找值，并在剩余列中提取信息（因此使用"匹配"而不是"查找"以避免 多次搜索相同的密钥）。

我无法正常工作   使用

Application.WorksheetFunction.Match

或

Application.Match

第一个语句返回错误代码1004（这意味着，我收集，相当于#N / A），即使我知道搜索到的值  那里。我假设秒语句因同样的原因失败。

我决定试着通过运行这个简单的，几乎是教科书的代码来弄清楚我做错了什么，它仍然不起作用，我无法理解为什么。

更改类型声明不会改变结果。

在这两种情况下，应用程序.Match生成错误触发第一个MSGBOX但是，当循环遍历矩阵时，找到正确的行并显示第二个MSGBOX。

如果我使用  " Application.WorksheetFunction。"。

这似乎相当直接，但我必须做错事，我无法弄清楚是什么。

谢谢

I want to lookup a value in a reference table using a key in column 1 and extract the information in the remaining columns (hence the use of "Match" instead of "Lookup" to avoid multiple searches of the same Key).

I could not get it working  using either

Application.WorksheetFunction.Match

or

Application.Match

The first statement returns error code 1004 (which means, I gather, the equivalent of #N/A), even if I know that the searched value is  there. I assume that the seconds statement fails for the same reason.

I decided to try to figure out what I was doing wrong by running this simple, almost textbook, code and it still does not work, and I cannot figure why.

Changing the type declaration does not change the results.

In both case the Application.Match generates an error triggers the first MSGBOX But, when looping through the matrix, the correct row is found and the second MSGBOX is displayed.

I obtain the same results if I use  "Application.WorksheetFunction." .

This seems rather straight forward, but I must be doing something wrong, and I can't figure out what.

Thanks

 Sub XYZ()
'
' Range Toto is "A1:A6" and contains 6 simple character strings
        Dim MatrixData() As Variant
        Dim Res As Variant
        Dim SearchedText As Variant
        
        SearchedText = Range("A3").Value
        MatrixData = Range("Toto").Value
        SearchedText = MatrixData(3, 1)
        
' Bloc 1: Try Match
        Res = Application.Match(SearchedText, Toto, 0)
        If IsError(Res) = True Then MsgBox SearchedText & "  not found"
        For i = 1 To UBound(MatrixData)
            If MatrixData(i, 1) = SearchedText Then MsgBox i & ": SearchedText = " & SearchedText & "  |  MatrixData(i, 1)= " & MatrixData(i, 1)
        Next i

' Bloc 2: Try VLookup
        Res = Application.VLookup(SearchedText, Toto, 1, 0)
        If IsError(Res) = True Then MsgBox SearchedText & "  not found"
        For i = 1 To UBound(MatrixData)
            If MatrixData(i, 1) = SearchedText Then MsgBox i & ": SearchedText = " & SearchedText & "  |  MatrixData(i, 1)= " & MatrixData(i, 1)
        Next i

End Sub

推荐答案

你应该使用

        Res = Application.Match（SearchedText，
范围（"Toto"），0）

        Res = Application.Match(SearchedText, Range("Toto"), 0)

和

        Res = Application.VLookup（SearchedText，
范围（" Toto"），1,0）

        Res = Application.VLookup(SearchedText, Range("Toto"), 1, 0)

这篇关于在宏中使用Match或VLookup的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！