Problem calling WorksheetFunction.Match

[PTW]

Hi. I'm trying to open an Excel Worksheet from VB and search through a range using the Excel MATCH function. Unfortunately, I keep getting the "Unable to get the Match Property of the Worksheet Function class" error.


(Worksheet is already open in Excel)


Dim MyRange As Range

Set MyRange = Application.Worksheets("Sheet1&quot.Range("A1:A10&quot
If Not IsError(Application.WorksheetFunction.Match("Find This", MyRange, 0)) Then
Msgbox "Found it"
End If


If I go to the Immediate Window and do a ? Worksheets("Sheet1&quot.Range("A1&quot I get the value of A1 in that spreadsheet.

Any idea what I'm doing wrong? Thanks!


PTW

 

[acron]

Try,

If Not IsError(Application.WorksheetFunction.Match("Find This", [A1:A13], 0)) Then
MsgBox "Found it"
End If

If you require the range to be a variable name, something like

If Not IsError(Application.WorksheetFunction.Match("Find This", _
Evaluate(MyRange.Address), 0)) Then
MsgBox "Found it"
End If

A.C.

 

[PTW]

Thanks for the reply acron. I still cannot seem to get it working if I use a Range with a variable name, but that's not a big deal in this case since I can simply use a hardcoded "A:A". I'm just not sure why it doesn't work since myrange.Address returns the value "$A:$A", which does work if I hardcode it. (I had to drop the Evaluate function to get it to return that value, but it still doesn't seem to work.)

PTW

 

[acron]

I do not why it does not work for you. I just tested in Excel 97 and both methods worked fine.

Have you a working solution ?

A.C.

 

[PTW]

I'll just leave it hardcoded for now and try to get the Range working later (if I need it). Thanks!

 

[bscheidel]

I had the same problem, but only in worksheets where the values I was trying to match against were numeric values. I changed the format from "General" to "Text" for the entire worksheet, but that didn't do the trick. Then I changed all the numeric values to words, and suddenly it worked as expected! Then I went back and changed those values back to the numerical values, and everything still worked as expected. This fix (change format to Text, change numeric values to strings and then back to numbers) worked for me on both worksheets where I was experiencing this problem.