In VBA, a 'Find' routine that looks for multiple possibilities

How do I rewrite this line of code so that it will look for #N/A errors as well? In this particular situation it can be either one.

Thanks,
John
Set top = Columns("B").Find("#REF!", , xlValues, xlWhole)

Open in new window

LVL 1
John CarneyReliability Business Tools Analyst IIAsked:
Who is Participating?
 
Michael FowlerSolutions ConsultantCommented:
You could incorporate ISERROR into your page formula and that way you could ensure that you only need to search for one item or even avoid doing the find at all

http://www.techonthenet.com/excel/formulas/iserror.php

Michael
0
 
Jorge PaulinoIT Pro/DeveloperCommented:
You can only look that for one value. Do that twice for the two possibilities
0
 
Davy2270Commented:
What are the actions you take in your code after you find the #REF or #N/A?
Will different actions need to be taken, depending on #REF or #N/A?
If so, follow ipaulino's answer. If not go with Michael's answer.
The formula in column B on your worksheet would be something like:

= IF(ISERROR(your current formula),"ERROR",your current formula)
Adjust your code to:
Set top = Columns("B").Find("ERROR!", , xlValues, xlWhole)

Regards
Davy
0
 
John CarneyReliability Business Tools Analyst IIAuthor Commented:
I managed to put something together with elements of both your solutions. Thanks!

- John
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.