Type 13 Runtime Error Using String Variable in Evaluate Function

I'm a first time contributor to this forum so thanks for letting me join the ranks.

I've got a sub that's evaluating each cell in the range "StoreNames" to see if it is in an adjacent list. If it is not in the list the cell value is added to the MissingStores variable and the counter is increased by one.

Everything works fine except for the "Store" variable in the OR function is giving me a "type mismatch" error. I can see the variable is set correctly (the first store is listed as "American Fork") yet the runtime error breaks the code.

Any advice?

Please Login or Register  to view this content.

Code Tags Added
Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between

Please Login or Register  to view this content.

tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found at http://www.excelforum.com/forum-rule...rum-rules.html



(Because you are new to the forum, I have added them for you today. Please take a few minutes to read all Forum Rules and comply in the future.)

Rather than fixing this problem, I'm suggesting an alternative solution to what you're trying to achieve

Although your user will be aware of the information they need, as it will appear in a message box, once OK has been clicked all that info has gone

How about once new data has been imported, they can just look at the StoreNames sheet

add a new column to test if data for all stores has been imported

Please Login or Register  to view this content.

this can be copied down do row 50 in column J, as this is your fixed list of stores

@VBADoesMyWork: what would make it easier for everyone would be if YOU uploaded a workbook with worksheets, data, formulas, Named Ranges and code ... complete with instructions as to how you can consistently produce the error. Then it might be possible to simulate the problem in a "life-like" scenario.

That beats second guessing, and trying to create a test environment for you.


Regards, TMS

Perhaps you require:

Please Login or Register  to view this content.

Originally Posted by VBADoesMyWork

However, I don't understand why adding the triple set of quotations solved the problem.

Formula will be =OR("Store"=$J$2:$J$50) with quotes included.
In VBA you must double quotes within formula string to escape quote - else VBA will see it as end of string.

Jindon is correct it will be better to use .Address if range may include number data also - if range is on separate sheet, you must include sheet name in address string.

Originally Posted by VBADoesMyWork

The .Address property would return the cell address rather than the value (which is not what I'm looking for).

It will produce same result.