[SOLVED] VBA Index Match got a Type Mismatch Error

VBA Index Match got a Type Mismatch Error

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

Solution

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