Hi Brandon,
Ah alright, that makes more sense and did fix my issue. Is there a nice central location where I can look up standard functions, their parameters, and their return types?
If you call the worksheet functions via Application, eg Application.Match() then, for VBA management purposes, you can consider them all to have a Variant return type because they can return an Error.
If you call the worksheet functions via Application.WorksheetFunction, eg Application.WorksheetFunction.Match() then you can check the return type checking the relevant topic in the VBA helpfile. For example, the 'WorksheetFunction.Match method' topic has full details about the syntax, parameters and even says the return type:
When you call the worksheet functions via Application.WorksheetFunction they cannot return an Error value (unless specified) so if you pass in arguments which result in an internal error then you will get a runtime error in VBA which will need error handling.
If you have the OLE/COM Object Viewer then you can see the C++ signatures of the functions, for example the Application.WorksheetFunction.Match() method:
I am running into another match related function problem, I have the following code.....
This one is a little easier to spot. If you run the following procedure:
You will see the following output in the Immediate Window:
Cells(4,3) returns a reference to cell D1, not cell A4, so you're checking the wrong row. Error 2042 is the equivalent of a #N/A error value in a worksheet function cell, meaning a match cannot be found. For reference, here are the Error_Val / Value equivalents:
Bookmarks