We help IT Professionals succeed at work.

Banish #VALUE! in one fowl swoop

Canders_12 used Ask the Experts™
Hello to you all,

I have numbers in the range A1 to C3.  One or two have the value "#VALUE!" instead of a number.  I would like to add a button to the sheet so when they click it all the "#VALUE!" turn into a "0".  Please note, this is NOT a formula so I can't simply sneak an ISERROR() into the little fella.

Your help would be greatly appreciated.  

Many thanks in adVANCE, Alan.
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2011
Top Expert 2011
You basically just need:
Sub ReplaceValErrs()
   Range("A1:C3").replace "#VALUE!", 0
End Sub

Open in new window


Dear diary, JACKPOT :)

Many thanks, Alan