Macro to clear #VALUE! error cells

Resolved · Medium Priority · Version 2003

Fri 4 Apr 2008, 11:21 Delegate 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:23 Trainer 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:04 Delegate 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:09 Trainer 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 courses

	98.7 % recommend us
	More live stats...

Training information:

Excel tip:

Use RANDBETWEEN to generate random numbers

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

The function looks like this:

=randbetween(LOW,HIGH)

where LOW is the lowest number you want generated; and HIGH is the highest number you want generated.

This formula will work with both positive and negative LOWs and HIGHs. Also it will only generate integer numbers unless forced to do otherwise by the following:

=randbetween(LOW*10^PRECISION,
HIGH*10^PRECISION)/(10^PRECISION).

where PRECISION represents the levels of decimal precision needed (i.e. if you need numbers with one decimal place, PRECISION would be 1; 2 for two decimal places and so on).

One final note, if the RANDBETWEEN formula does not work in your spreadsheet or returns a "#NAME" error, you need to install the Analysis Toolpak Add-In. You will need to press F2 then Enter following the installation.