98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Forum home » Delegate support and help forum » Microsoft Excel Training and help » Macro to clear #VALUE! error cells
Macro to clear #VALUE! error cells
Resolved · Medium Priority · Version 2003
Fri 4 Apr 2008, 11:21Delegate Calum said...
Calum has attended:
Excel Intermediate course
Excel Advanced course
Macro to clear #VALUE! error cells
Please advise on how I can write a macro to clear ALL cells in a spreadsheet that contain the #VALUE! error messages that are created in cells that lack a reference number as part of their formula once I have dragged formulas down a column.
Thanks,
Calum
Mon 7 Apr 2008, 05:23Trainer Mara said...
RE: Macro to clear #VALUE! error cells
Hello Calum,
I hope you enjoyed your Excel training sessions with us.
In response to your question, I would suggest that rather than use a macro to correct your errors, that instead you use a formula from the start that will hide the errors. We can do this with the IF and ISERROR functions.
For example, let's assume your formula is a basic multiplication:
=A1*B1
To correct errors for this formula, your new formula would be:
=IF(ISERROR(A1*B1),"",A1*B1)
The empty quotes will return a blank cell. However, you can add any messaging or number in this cell. Text must be entered within the quotation marks, but numbers can just be on their own.
I hope this resolves your question. If it has, please mark this question as resolved. If you require further assistance, please reply to this post. And please feel free to ask any other questions you may have.
Have a great day.
Regards,
Mara
Microsoft Office Specialist Master Trainer
Mon 7 Apr 2008, 10:04Delegate Calum said...
RE: Macro to clear #VALUE! error cells
Hi Mara,
Thanks for yours.
The formula I frequently use is finding the geometric difference between two percentages, for instance:
=((1+A10/100)/(1+$A$5/100)-1)*100
Am I right in interpreting your suggestion - that I should use:
=(IF(ISERROR(((1+A10/100)/(1+$A$5/100)-1)*100),"",((1+A10/100)/(1+$A$5/100)-1)*100) ?
If so, it's pretty unwieldy...even if it works!
I was really looking for a method of clearing all the error messages in a spreadsheet once I have completed dragging down the formulas...often 1000 rows and 100 columns per table?
Thanks,
Calum
Mon 7 Apr 2008, 17:09Trainer Mara said...
RE: Macro to clear #VALUE! error cells
Hi Calum,
Unfortunately I'm not aware of a way of clearing the errors otherwise, since find and replace does not work for errors. Although the formula you have written looks unwieldy, it is written correctly, and woud clear the errors up front.
Sorry I cannot be of more help.
Regards,
Mara
Training information:
See also:
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:Use RANDBETWEEN to generate random numbersThere may be occassions where you need to generate random numbers in your spreadsheet. Use the RANDBETWEEN function to generate random numbers between two values that you specify. |
