Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 224
  • Last Modified:

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

0
gabrielPennyback
Asked:
gabrielPennyback
2 Solutions
 
jpaulinoCommented:
You can only look that for one value. Do that twice for the two possibilities
0
 
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
 
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
 
gabrielPennybackAuthor Commented:
I managed to put something together with elements of both your solutions. Thanks!

- John
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now