提问人：vivianna 提问时间：6/13/2023 更新时间：6/13/2023 访问量：62
VBA 索引匹配出现类型不匹配错误
VBA Index Match got a Type Mismatch Error
问：
我正在尝试使用 index&Match 在 2 张工作表之间查找值。一个工作表被定义为“目的地”，另一个工作表被定义为“原点”。
我的代码出现类型不匹配错误。我测试了函数逻辑，公式在 excel 中起作用。我在这里查看了其他人的类似问题，但仍然无法解决。如有任何建议，将不胜感激。
Sub CalculateTracker()
Dim LastRow As long
Dim Destination As Worksheet
Set Destination =Worksheets("sheet1")
Dim i As Integer
Dim Origin As Worksheet
Set Origin = Worksheet("sheet2")
With Worksheet("sheet1")
LastRow=.Cells(.Rows.Count,"A").End(xlUp).Row
End With
For i=2 To LastRow
With Worksheets("sheet1")
Cells(i,28).Value=WorksheetFunction.ifError(WorksheetFuncion.Index(Origin.Range("AZ:AZ"),Application.Match(Destination.Range("X" & i),Origin.Range("B:B"),0)),0)
End With
Next i
结束副
答：
2赞
BigBen
6/13/2023
#1
用于测试是否返回错误。IsErrorMatch
Dim result As Variant
result = Application.Match(Destination.Range("X" & i),Origin.Range("B:B"),0)
If Not IsError(result) Then
Worksheets("sheet1").Cells(i,28).Value = Origin.Range("AZ:AZ").Cells(result)
Else
Worksheets("sheet1").Cells(i,28).Value = 0
End If
3赞
VBasic2008
6/13/2023
#2
VBA 中的索引/匹配（后期绑定）
快速修复
Option Explicit
Sub CalculateTracker()
Const DST_FIRST_ROW As Long = 2
Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
Dim Origin As Worksheet: Set Origin = wb.Sheets("Sheet2")
Dim Destination As Worksheet: Set Destination = wb.Sheets("Sheet1")
Dim dlRow As Long
With Destination
dlRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With
If dlRow < DST_FIRST_ROW Then Exit Sub ' no data
Dim r As Long
For r = DST_FIRST_ROW To dlRow
With Application
Destination.Cells(r, "AB").Value _
= .IfError(.Index(Origin.Range("AZ:AZ"), _
.Match(Destination.Cells(r, "X"), Origin.Range("B:B"), 0)), 0)
End With
Next r
End Sub
评论
IsErrorApplication.MatchWorksheetFuncion.IndextWorksheetFunction.ifErrorWorksheetFunction.xxxWith Worksheet("Sheet1")sWorksheetSet Origin= Worksheet("sheet2")