WorksheetFunction.Match Question

[brans1982]

Hi,

Please see example data below...

Sub MatchValue()
Dim wf As WorksheetFunction, _
rng As Range

Set wf = Application.WorksheetFunction
Set rng = Range("A1:A2000")

FinalRow = Cells(Rows.Count, 2).End(xlUp).Row
PasteRow = Cells(Rows.Count, 4).End(xlUp).Row

For i = 1 To FinalRow

If IsError(wf.Match(Cells(i, 2), rng, 0)) Then

Cells(i, 2).Copy Destination:=Cells(PasteRow, 4)
PasteRow = PasteRow + 1

End If

Next i

End Sub

Using normal formulas I would use the MATCH function like this...

=IF(ISERROR(MATCH(A1,B1:B10,0)),"No Match","Match")

...to find if there is certain data in a column but when trying something similar in VBA, when the code does find a value that isn't in the MATCH range, it throws an error instead of following on with e.g Copy/Paste like in the VBA code above.

Is there anyway around this?

I'm not to up on Error handlers, but I've tried Resume Next ect. but to no avail.

Thanks!

 

[brans1982]

I think I have it...

I used Application.Match instead of Application.WorksheetFunction.Match

Why does this work?