Excell worksheet functions in VBA

[Brianwarnock]

In order to develop a bit of code for a poster in the EXCEL forum I tried to use the Match worksheet function, everything is fine is there is a match but if there isn't I get

Runtime error '1004'

Unable to get the Match property of the Worksheetfunction class

The code does not run so error handling is not the issue.

can anybody help?
The code for what it is worth.

Private Sub worksheet_change(ByVal target As Range)
On Error Resume Next

myvar = Application.WorksheetFunction.Match(target.Value, Worksheets("Sheet2").Range("a1:a100"), 0)

If myvar = "#N/A" Then
MsgBox "nomatch"
Else: MsgBox "match"
End If

End Sub

Brian

 

[chergh]

AFAIK the runtime error 1004 is the function working as intended. When the function is called via the worksheet it returns '#n/a" but when called via vba it is intended to return the run time error.

 

[chergh]

Try handling with:

myvar = Application.WorksheetFunction.Match(target.Value, worksheets("Sheet2").Range("a1:a100"), 0)

If iserror(myvar) 
MsgBox "nomatch"
Else
MsgBox "match"
End If

End Sub

 

[Brianwarnock]

Thanks for the reply, but the code actually stops with the earlier mentioned error on

myvar = Application.WorksheetFunction.Match(target.Value, worksheets("Sheet2").Range("a1:a100"), 0)

I have tried all sorts of error handling not just Resume next, it just seems to hang.


Brian

Ps I did try what you suggested, I didn't ignore that.

 

[chergh]

Have you tried using 'application.match' instead of worksheet function?

 

[Brianwarnock]

No, I'll give it a go, but have to go now so will look at it tomorrow, thanks.

Brian

 

[chergh]

or

if iserror(Application.WorksheetFunction.Match(target.Value, worksheets("Sheet2").Range("a1:a100"), 0)) then
MsgBox "nomatch"
Else
MsgBox "match"
End If

 

[Brianwarnock]

Couldn't resist another go , and guess what I wrote the identical code to you thanks to your info.

I will have to look at it in more detail tomorrow so that I understand the difference, but thanks a bunch.

Brian

 

[Brianwarnock]

Just looked again at your code, I took your advice and dropped WorksheetFunction and it worked, with Application.WorksheetFunction.Match it always hangs when there is no match with Application.Match it works but this is beyond my knowledge, Help doesn't and I have no manuals.
I guess I'll have to browse a bookstore some time.

Brian