[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 233
  • Last Modified:

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??



0
GJPerkins
Asked:
GJPerkins
  • 2
1 Solution
 
Dave BrettCommented:
Hi GJPerkins,

try something like

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


Cheers

Dave
0
 
Dave BrettCommented:
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
 
GJPerkinsAuthor Commented:
Cheers Dave - I feel a bit of a plonker for not twigging that!!
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now