英文:
VBA Index Match got a Type Mismatch Error
问题
我正在尝试使用Index＆Match在2个工作表之间查找值。一个工作表定义为“Destination”，另一个工作表定义为“Origin”。
我的代码出现了类型不匹配错误。我已经测试了函数逻辑，这个公式在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 = Worksheets("sheet2")
With Worksheets("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(WorksheetFunction.Index(Origin.Range("AZ:AZ"), Application.Match(Destination.Range("X" & i), Origin.Range("B:B"), 0)), 0)
End With
Next i
End Sub
英文:
I am trying to use index&Match to lookup values between 2 sheets. One sheet is defined as "Destination" and the other sheet is defined as "Origin".
My code got type mismatch error. I tested the function logic and the formula is working in the excel. I looked at other's similar issues here but still cannot solve it. It would be much appreciated for any suggestions.
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
End Sub
答案1
得分: 3
Sub CalculateTracker()
Const DST_FIRST_ROW As Long = 2
Dim wb As Workbook: Set wb = ThisWorkbook ' 包含此代码的工作簿
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 ' 没有数据
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
英文:
Index/Match in VBA (Late Bound)
A Quick Fix
<!-- language: lang-vb -->
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
答案2
得分: 2
使用 IsError 来测试 Match 是否返回了一个错误。
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
英文:
Use IsError to test if Match returned an error.
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
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论