Worksheetfunction.Match Issue

**DAA** · 10-29-2009, 11:05 AM

Hi,

I'm creating a UDF in which I use WorksheetFunction.Match.

The Range I want to use is the first column of the user specified range.

I achieve this as follows (I am only including the relevant part of the function for simplicity's sake):

Please Login or Register  to view this content.

Does anyone know why that line does not work? Note that when I removed the sheet name it worked fine (as long as the lookup range was in the same sheet as the cell with the function)

**NBVC** · 10-29-2009, 11:09 AM

Welcome to the forum,

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

**lecxe** · 11-01-2009, 01:21 AM

Hi DAA

Try:

Please Login or Register  to view this content.

Don't forget the code tags around the code.

Remarks:

A -
In your code

Please Login or Register  to view this content.

you have a syntax error. In the instruction Range(Range(),Range()) you are not allowed to mix ranges from different worksheets. In your code the part in blue refers to the worksheet DSheet while the inside, the part in red refers to the active sheet. This is a syntax error.

B -

In the line

Please Login or Register  to view this content.

you should correct 2 things

1 - there are more than 32K rows even before excel 2007, so instead of defining the variables as Integer it's better to define theam as Long

2 - in vba you must declare the type for each variable. In your line you are declaring RCol as Integer and you are not specifying the type for the other variables and so vba defines them as Variant. It should be

Please Login or Register  to view this content.

HTH

**DonkeyOte** · 11-01-2009, 04:18 AM

DAA, on this occasion I have added the CODE tags for you - going forward please ensure you use them appropriately.

lecxe, as valued as your posts are I would ask that you still comply with the rules of this forum - more specifically rule 7 (to paraphrase):

Do not post a reply to a thread where a moderator has requested an action that has not been complied with e.g Title change or Code tags...etc

TIA for your co-operation.
D.O