如何在VBA中使用索引/匹配？

I'm trying to create a macro that uses Index/match functions to match and pull data from one sheet into another. I did it in Excel and it works perfect. However the reports are "dynamic" (the size changes) so I need the last row of my code to be dynamic as well. The following is what I have done. I'm NOW getting a "type mismatch" error (I emphasize "now" since every time I find a solution for one error another pop's up).

我正在尝试创建一个宏,它使用索引/匹配函数来匹配数据并将数据从一个工作表拉到另一个工作表。我在Excel中完成它并且它完美无缺。然而,报告是“动态的”(大小更改)所以我需要我的代码的最后一行也是动态的。以下是我所做的。我现在遇到“类型不匹配”错误(我强调“现在”,因为每次我找到一个错误的解决方案另一个弹出窗口)。

   Dim prosheet As Worksheet

   Dim prosheet2 As Worksheet

   Set prosheet2 = ThisWorkbook.Sheets("shipstation")

   Set prosheet = ThisWorkbook.Sheets("macrotestfb")

   lr1 = prosheet.Cells(Rows.Count, 1).End(xlUp).Row

   lr2 = prosheet2.Cells(Rows.Count, 1).End(xlUp).Row

   lrship = prosheet.Cells(Rows.Count, 10).End(xlUp).Row

   lrindex = prosheet2.Cells(Rows.Column, 14).End(xlUp).Row

   'CALCULATE SHIPPING COST

   For x = prosheet.range("j6") To lrship

       x = Application.WorksheetFunction.Index(prosheet2.range("a1:n" &  lrindex), Application.WorksheetFunction.Match(prosheet.range("a6:a" & lr1), prosheet2.range("a1:a" & lr2), 0), prosheet2.range("f2"))

   Next x

2 个解决方案

#1

Match, in its non array form, only likes one value in the first criterion and not a range.

匹配,以非数组形式,仅在第一个标准中而不是范围中匹配一个值。

Also WorksheetFunction.Match will throw an error that will stop the code if a match is not found.

此外,如果找不到匹配项,WorksheetFunction.Match将抛出将停止代码的错误。

I like to pull the match into its own line and test for the error.

我喜欢将比赛拉到自己的线上并测试错误。

I also adjusted your For statement.

我也调整了你的For声明。

There is no detriment to searching an entire column so I got rid of a few of you last row searches as they are not needed.

搜索整个列没有任何不利因素,因此我删除了一些最后一行搜索,因为它们不需要。

Dim prosheet As Worksheet
Dim prosheet2 As Worksheet
Dim x As Long
Dim t As Long
Set prosheet2 = ThisWorkbook.Sheets("shipstation")
Set prosheet = ThisWorkbook.Sheets("macrotestfb")

lrship = prosheet.Cells(Rows.Count, 1).End(xlUp).Row

'CALCULATE SHIPPING COST

For x = 6 To lrship
    t = 0
    On Error Resume Next
        t = Application.WorksheetFunction.Match(prosheet.Range("A" & x), prosheet2.Range("A:A"), 0)
    On Error GoTo 0
    If t > 0 Then
        prosheet.Cells(x, "J").Value = prosheet2.Range("F"&t)
    Else
        prosheet.Cells(x, "J").Value = "Item does not Exist"
    End If
Next x

#2

Note:

Instead of an Index/Match combo which you might use on the worksheet, you can use Application.Match in VBA. Something like this:

您可以在VBA中使用Application.Match,而不是可能在工作表上使用的索引/匹配组合。像这样的东西:

Sub GetMatch
    Dim indexRng As Range, matchRng as Range

    Set indexRng = ThisWorkbook.Worksheets("Sheet1").Range("A1:A10")
    Set matchRng = ThisWorkbook.Worksheets("Sheet1").Range("B1:B10")

    debug.print indexRng.Cells(Application.Match("something",matchRng,0)).Value
End Sub

#1