Using Match function output as a variable

Hello all!

I am having trouble using the MATCH function in my code. I am looking for a match in one worksheet from a cell in another. I have defined these cells and ranges as variables. However, it doesn't seem to be working with my application. I would like to find the row of the matching cell and use the output of the MATCH function to determine the next step in my code with an IF function.

Here is my code. (NOTE: this is my edited code, I removed irrelevant pieces. The major problem is the MATCH function.) If you could help me, I would be greatly appreciate your efforts.

Please Login or Register  to view this content.

hi,

The use of match returns a number (ie integer, Long etc) whereas your use of "Set CIMR = Application.Match(cBc, rB, 0)" attempts to "set" the result as an object (ie a "mismatch"). I think that you would be better using ".Find" & have included the Excel 2002 VBE Help Notes for you to consider.

Please Login or Register  to view this content.

I've attached a 1/2 modified example file - hopefully there's enough info in there to guide you through the rest of the necessary changes (variable declaration & other Set statements)...
Please note I have not included any error handling which should be used in case the search value is not found. There are plenty of Find examples on the website which you can check for error handling approaches. Also, I recommend changing your variables from variants to Range or String for efficiency.
Good luck & let us know if you do need more guidance.

hth
Rob

Pleased I could help - thanks for the feedback & the reputation :-)

If you have any questions about specific code/ways to code, quite often (but not always...) the best way is to record a macro of your actions of a manual step by step approach & then modify the resulting code. Also, to learn the specific details about properties or methods in the VBE, highlight the word & press [F1] to bring up the Help files for the relevant item.

Rob