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
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
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
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
