VBA Index Match got a Type Mismatch Error

Question

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

Answer 1

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

Answer 2

Index/Match in VBA (Late Bound)

A Quick Fix

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