使用两个索引/匹配时，运行时错误1004无法获取match属性 [英] Runtime error 1004 unable to get the match property when using two index/match

问题描述

试图使以下代码正常工作，但不幸的是第二个索引/匹配项引发错误.如果我从第二部分中删除"count"变量(在第一个索引/匹配项中工作正常)，并且引用单个单元格，则不知道为什么，该代码有效.

Trying to make the below code work, but unfortunately the second index/match throws an error. The code works if I remove the "count" variable from the second part (works fine in the first index/match), and refer to a single cell, no idea why.

尝试初始化新的计数器变量，第2部分仍然引发错误.另外，是否有更好的方法可以在for循环中引用某个区域中的单元，而不是我使用的贫民窟解决方案?

Tried initing a new counter variable, part 2 still throws an error. Also, is there a better way to refer to cells in a range, in a for loop, instead of the ghetto solution I used?

谢谢！

Dim sht As Worksheet
Dim LastRow As Long
Dim count As Integer

Set sht = ActiveSheet
LastRow = sht.Cells(sht.Rows.count, "A").End(xlUp).Row

count = 2

    For Each i In Range("f2:f" & LastRow)
        With Application.WorksheetFunction
            i.Value = .Index(Worksheets("Area").Range("c:c"), .Match(Range("E" & count), Worksheets("Area").Range("a:a")))
        End With
        count = count + 1
    Next

count = 2

    For Each i In Range("h2:h" & LastRow)
    i.Value = count
        With Application.WorksheetFunction
            i.Value = .Index(Worksheets("Park reason").Range("C:C"), .Match(Range("G" & count), Worksheets("Park reason").Range("A:A")))
        End With
        count = count + 1
    Next

End Sub

推荐答案

最有可能找不到匹配项.测试找不到的匹配项.如果使用 Application.Match ，则可以使用测试中返回的错误来尝试找到 i.value 之前是否找到匹配项.对两次匹配"尝试都做同样的事情.

Most likely a match was not found. Test for a match not found first. If you use Application.Match you can use the error returned in a test to see if a match was found before attempting to get your i.value. Do the same for both Match attempts.

With Application.WorksheetFunction
    Dim test As Variant
    test = Application.Match(Range("E" & count), Worksheets("Area").Range("a:a"), 0)
    If Not IsError(test) Then
        i.Value = .Index(Worksheets("Area").Range("c:c"), test)
    End If
End With

我可能会改写为:

With sht
    Dim test As Variant
    test = Application.Match(.Range("E" & count), Worksheets("Area").Range("A:A"), 0)
    If Not IsError(test) Then
        i.Value = Application.WorksheetFunction.Index(Worksheets("Area").Range("C:C"), test)
    End If
End With

我还希望使用比整个列更小的范围，例如不是"C:C".找到使用的范围/最后一行并进行处理.

I would also look to work with smaller ranges than entire columns i.e. not "C:C" for example. Find the used range/last row and work up to that.

完整版:

Option Explicit
Sub test()

    Dim sht As Worksheet
    Dim LastRow As Long
    Dim count As Long
    Set sht = ActiveSheet
    LastRow = sht.Cells(sht.Rows.count, "A").End(xlUp).Row

    With sht
        count = 2
        Dim i As Range, test As Variant
        For Each i In .Range("F2:F" & LastRow)
            test = Application.Match(.Range("E" & count), Worksheets("Area").Range("A:A"), 0)
            If Not IsError(test) Then
                i.Value = Application.WorksheetFunction.Index(Worksheets("Area").Range("C:C"), test)
            End If
            count = count + 1
        Next
        count = 2
        Dim test2 As Variant
        For Each i In .Range("H2:H" & LastRow)
            test2 = Application.Match(.Range("G" & count), Worksheets("Park reason").Range("A:A"))
            If Not IsError(test2) Then
                i.Value = Application.WorksheetFunction.Index(Worksheets("Park reason").Range("C:C"), test2)
            End If
            count = count + 1
        Next
    End With
End Sub

这篇关于使用两个索引/匹配时，运行时错误1004无法获取match属性的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！