Excel 2000/97 VBA: Using the Match from the WorksheetFunction class

Excel 2000 (although would like to know if 97 is any different), VBA 6

On the worksheet, it is easy

=MATCH(Q3,Measure,0)

Where measure is a named Range.

I thought I could do something like this in a VBA Sub/function:-

Debug.Print CStr(WorksheetFunction.Match(Q3, ActiveWorkbook.Names("Measure").RefersToRange.Areas(1).Address, 0))
(WRONG - Get "Unable to find match property in WorksheetFunction class")

Object browser says Match is in the WorksheetFunction class.

Set WF = CreateObject("Excel.WorksheetFunction")
NOR
Set WF = New Excel.WorksheetFunction
...do not seem to create the object.

Is it possible to call the functions in the WorksheetFunction class???  Others must have needed to do this before now, how did you solve it? Go to a empty cell and change the formula on the fly - messy but a possibility??



GJPerkinsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

DaveCommented:
Hi GJPerkins,

try something like

MsgBox Application.WorksheetFunction.Match(Range("Q3"), Range("Measure"), 0)


Cheers

Dave
0
DaveCommented:
or a bit tidier with error handler for the no match case

Dim MatchPos As Long
    On Error Resume Next
    MatchPos = Application.WorksheetFunction.Match(Range("Q3"), Range("Measure"), 0)
    If Err.Number <> 0 Then
        MsgBox "No match"
    Else
        MsgBox "Match in position " & MatchPos
    End If
    On Error GoTo 0

Cheers

Dave
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
GJPerkinsAuthor Commented:
Cheers Dave - I feel a bit of a plonker for not twigging that!!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.