Error 2042 for application.match

Please Login or Register  to view this content.

It keeps giving me an error 2042 even if the value from b2 exists in Range("b:b") from 2nd sheet.

Hi a.hudrea,

My version of Excel (2003) is very picky about data types when using Application.Match. This includes how you define the data on the spreadsheet, and how you define the values in VBA. Using Variant type, I duplicated your problem almost every time.

The following test code may point you in the right direction.

Please Login or Register  to view this content.

It is a best practice to declare all variables. If you misspell a variable in your code, VBA will silently assume it is a Variant variable and go on executing with no clue to you that you have a bug. Go to the VBA development window, click Tools, Options, and check "Require Variable Declaration." This will insert the following line at the top of all new modules:

Please Login or Register  to view this content.

This option requires all variables to be declared and will give a compiler error for undeclared variables.

Lewis

Thanks you so much for the help, and thank you for taking the time to explain everything. Solved.