Worksheet Function error when running - Xtreme Visual Basic Talk

10-10-2003, 11:41 AM

ehllhjh123 ehllhjh123 is offline

Newcomer

Join Date: Aug 2003

Posts: 7

Worksheet Function error when running

I have taken some sample code from another thread to integrate in my program.

The code compiles fine, but when it is ran I get the error:

Unable to get the Match property of the WorksheetFunction class.

I get the same error when trying to implement the Vlookup worksheet function from within VB.

The code:
<vb/>
Public Sub getdesc()
Dim s As String, result As String

s = Range("C1").Value

result = WorksheetFunction.Index(Columns(2), WorksheetFunction.match(s, Columns(1), 0))

Range("D1").Value = result

End Sub
</vb>

Any help would be greatly appreciated! You guys are incredible at debugging these issues!

Thanks!

10-10-2003, 12:48 PM

mathiasb

Centurion

Join Date: Jul 2003

Location: San Francisco, CA

Posts: 183

In the result expression, you use columns(1) or columns(2) which are not defined. I guess you want to use the worksheet function columns(), but then you would have to specify that this is a worksheet function, otherwise it won't be recognized as such in your code.

Hope that helps,

Quote:
Originally Posted by ehllhjh123
I have taken some sample code from another thread to integrate in my program.

The code compiles fine, but when it is ran I get the error:

Unable to get the Match property of the WorksheetFunction class.

I get the same error when trying to implement the Vlookup worksheet function from within VB.

The code:
<vb/>
Public Sub getdesc()
Dim s As String, result As String

s = Range("C1").Value

result = WorksheetFunction.Index(Columns(2), WorksheetFunction.match(s, Columns(1), 0))

Range("D1").Value = result

End Sub
</vb>

Any help would be greatly appreciated! You guys are incredible at debugging these issues!

Thanks!

__________________
Mathias Brandewinder,
Applied Strategies Technology

10-10-2003, 01:28 PM

ehllhjh123 ehllhjh123 is offline

Newcomer

Join Date: Aug 2003

Posts: 7

continuing issues

For some reason I feel like this error is being generated because my excel application isn't configured correctly (ie. missing a plugin). Does the error look familiar to something like that?

I haven't been successful running any code w/ "worksheetfunction" objects.

10-10-2003, 01:46 PM

herilane

Unashamed geek

Retired Moderator
* Expert *

Join Date: Jul 2003

Location: London, England

Posts: 8,988

As Mathias points out, you're just not referring to the ranges correctly.
Try this:

Code:

result = WorksheetFunction.Index(Range("B:B"), WorksheetFunction.match(s, Range("A:A"), 0))

10-10-2003, 02:35 PM

ehllhjh123 ehllhjh123 is offline

Newcomer

Join Date: Aug 2003

Posts: 7

continuing issues

hmmm... unfortunately I get the same error even w/ your editions herilane... it's very possible that I'm making a dumb mistake, but for some reason I still feel like the error refers to some missing link for Worksheetfunctions w/in VB.

THanks for your help!

10-12-2003, 05:56 AM

herilane

Unashamed geek

Retired Moderator
* Expert *

Join Date: Jul 2003

Location: London, England

Posts: 8,988

OK, let's take a step back... are you doing this in VB6 or VBA?

10-13-2003, 09:18 PM

tboltfrank tboltfrank is offline

Senior Contributor

Join Date: Jul 2003

Posts: 1,022

*** See edit at end, as I finaly realized that the variables are declared
as strings, so indications are from that, that the data is strings. * And naturaly numbers would not work with the code that way

***

ehllhjh123,

Are you searching for strings or numbers?

I experimented with this code and when I used numbers in columns(A) & (B) and cell C1, I got the same error that you're describing. But when I used string data, it worked.

Another possibility might be if your Data is dates. If it is, I think that you may have to convert them to strings, before using the function.

I'm very weak on my knowledge of formulas, (Herilane is far my superior in that and all coding) but if you are using dates with this and he doesn't happen back before you need to try something new, I'd be happy to share what I successfully used as a work around, if no better solutions are offered.

*Edit* Based on the fact that you have your variables declared as strings, I guess I should have made the assumtion that you are using string data. - If you are using numbers though, just change your variable declarations to Integer, or Variant, instead of String, like you have it. And if they are dates, change the date to it's serial number and change the format to a number with zero decimal places, run your routine, then change the format back to a date. If that does the trick, that can all be added to your code.

Last edited by tboltfrank; 10-14-2003 at 12:33 AM.

10-14-2003, 11:02 AM

Insomniac

Contributor

* Expert *

Join Date: Mar 2003

Location: Adelaide,Australia

Posts: 681

I think tboltfrank may have a point here with the data type!

I got both these ways to work on sample data:

Code:

[D1] = WorksheetFunction.Index(Columns(2), WorksheetFunction.Match([C1], Columns(1), 0))

[D1] = WorksheetFunction.Index([B:B], WorksheetFunction.Match([C1], [A:A], 0))

If its a data type issue you can just pass the range itself to the function and let Excel worry about the data type:

Code:

Public Sub getdesc()
Dim s As Range, result As Variant

Set s = [C1]

On Error Resume Next

result = WorksheetFunction.Index([B:B], WorksheetFunction.Match(s, [A:A], 0))

If result = Empty Then MsgBox "no match"

[D1] = result

Set s = Nothing

End Sub

10-14-2003, 02:31 PM

tboltfrank tboltfrank is offline

Senior Contributor

Join Date: Jul 2003

Posts: 1,022

As you already know, that works great Insomniac.

This has been an issue that has caused me to write extra code, to work around it, a few times.

As I'm sure you know, using DateValue can be implemented for some similar situations, too. - I learned that from one of Herilanes other post today. - Just out of curiosity, do you see that as something that could be used here? - You would likely know the answer to that in an instant, whereas it would take me awhile to play around with it, to decide. If it didn't work, it might take quite awhile, before I could conclude that it couldn't be done.

Thanks