Iserror/Index/Match in VBA

Hi All,

I am wondering if you could please help me with the below code I have.

For Each cell In Range("H4:H60")

If IsError(Application.WorksheetFunction.Index(Sheets("PAN").Range("I1:K50"), Application.WorksheetFunction.Match(Range("B" & cell.Row), Sheets("PAN").Range("K1:K50"), 0), 1)) = False Then
Range("B" & cell.Row).Text = "HA"

End If
Next

I've never done an iserror, index or match in vba, so it's something new to me and I'm not exactly sure as to how to go about it.

Also, while I'm here can you please explain when to use "Application." before "WorksheetFunction" and when not to, as I'm having trouble finding the differences.

Your help is greatly appreciated.

Benno

What exactly do you want to do? There is probably a better approach using VBA.

In Loop Terms

Dim vMatch As Variant
Dim rngCell As Range
Dim wsSource As Worksheet
Set wsSource = Sheets("PAN")
For Each rngCell In Range("H4:H60")
    vMatch = Application.Match(Cells(rngCell.Row, "B"), wsSource.Range("K1:K50"), 0)
    If IsNumeric(vMatch) Then
        rngCell.Value = Application.Index(wsSource.Range("I1:I50"), vMatch)
    Else
        rngCell.Value = "HA"
    End If
Next rngCell
Set wsSource = Nothing

However, an alternate route...somewhat shorter

With Range("H4:H60")
    .FormulaR1C1 = "=IF(ISNA(MATCH(RC2,PAN!R1C11:R50C11,0)),""HA"",INDEX(PAN!R1C9:R50C9,MATCH(RC2,PAN!R1C11:R50C11,0)))"
    .Value = .Value 'override formulae with value
End With

Why not test it and/or read the annotation ?