<div>
Hi GJPerkins, 
 
try something like 
 
MsgBox Application.WorksheetFunction.Match(Range(&quot;Q3&quot;), Range(&quot;Measure&quot;), 0) 
 
 
Cheers 
 
Dave
</div>

Hi GJPerkins,

try something like

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


Cheers

Dave

<div>
Cheers Dave - I feel a bit of a plonker for not twigging that!!
</div>

Cheers Dave - I feel a bit of a plonker for not twigging that!!

<div>
<div class="content wysiwyg-content">
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(&quot;Measure&quot;).RefersToRange.Areas(1).Address, 0)) 
(WRONG - Get &quot;Unable to find match property in WorksheetFunction class&quot;) 
 
Object browser says Match is in the WorksheetFunction class. 
 
Set WF = CreateObject(&quot;Excel.WorksheetFunction&quot;) 
NOR 
Set WF = New Excel.WorksheetFunction 
...do not seem to create the object. 
 
Is it possible to call the functions in the WorksheetFunction class??? &nbsp;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?? 
 
 
 

</div>
</div>

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


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

Visual Basic is Microsoft’s event-driven programming language and integrated development environment (IDE) for its Component Object Model (COM) programming model. It is relatively easy to learn and use because of its graphical development features and BASIC heritage. It has been replaced with VB.NET, and is very similar to VBA (Visual Basic for Applications), the programming language for the Microsoft Office product line.