代码之家 › 专栏 › 技术社区 › Asrakh

运行时错误1004使用两个索引/匹配时无法获取match属性

excel excel-vba vba

Asrakh · 技术社区 · 6 年前

试图使下面的代码工作,但不幸的是,第二个索引/匹配抛出了一个错误。如果我从第二部分删除“count”变量(在第一个索引/匹配中工作良好),并引用单个单元格,代码就会工作,不知道为什么。

尝试初始化一个新的计数器变量,第2部分仍然抛出一个错误。还有,有没有更好的方法来指代一个范围内、一个for循环中的单元格,而不是我使用的贫民区解决方案?

谢谢!

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

2 回复 | 直到 6 年前

QHarr 6 年前

很可能没有找到匹配项。未首先找到匹配项的测试。如果你使用 Application.Match 在尝试获取 i.value . 对两次匹配尝试执行相同的操作。

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”。找到已使用的范围/最后一行并进行计算。

更完整的版本:

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

Asrakh 6 年前

完整的代码顺便说一句,不知道为什么复制到cleanup sub时会出现复制错误,但有了解决方法,它就可以工作了。

Sub Cleanup()

Dim sToday As String
sToday = Format(Date, "mm-dd-yyyy")

ActiveSheet.Copy Before:=Worksheets("Park Reason")
Rows("1:12").Select
Selection.Delete Shift:=xlUp
Range("G:H").Select
Selection.UnMerge
Columns("H:H").Select
Selection.Delete Shift:=xlToLeft
Rows("1:1").Select
Range("A:I").AutoFilter Field:=6, Criteria1:="Yes"
Columns("G:G").EntireColumn.AutoFit
Range("A:I").AutoFilter Field:=7, Criteria1:=Array("1" _
    , "11", "12", "13", "14", "15", "16", "30", "40", "50", "51", "99"), Operator:= _
    xlFilterValues
Range("A:I").AutoFilter Field:=1, Criteria1:=Array( _
    *removed*), Operator:=xlFilterValues
Range("A:I").RemoveDuplicates Columns:=2, Header:= _
    xlYes

For Each i In Range(Range("G2"), Range("G2").End(xlDown))
    If IsNumeric(i) Then
        i.Value = CDec(i.Value)
    End If
Next

For Each i In Range(Range("B2"), Range("B2").End(xlDown))
     If IsNumeric(i) Then
        i.Value = CDec(i.Value)
    End If
Next

Columns("F:F").Select
Selection.Delete ' Shift:=xlToLeft
Selection.Insert ' Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("F1").Value = "Area"
Columns("H:H").Insert
Range("H1").Value = "Parking Reason"
ActiveSheet.Name = sToday

test

End Sub

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