Prev: Column to column of diff. wkbk
Next: Splitting Data from One Worksheet to Many Automatically
From: GoodyA10 on 27 Apr 2010 06:46
I am building an execl sheet that holds production data. Some pieces of
equipment only run occasionally and because of this they cause a #DIV/0!
error. I want to be able to look through the complete range (a1:di54) of
cells and whenver I find the error i want to delete the contents of the cell.
I guess this is possible to do using a Visual Basic module but my experience
of Visual Basic is limited.
From: Jacob Skaria on 27 Apr 2010 07:30
Its better to handle the error within the formula itself.

If =A1/B1 returns an error change the formula to

=IF(N(B1),A1/B1,"")


You can try out the below macro. If you are new to macros..

--Set the Security level to low/medium in (Tools|Macro|Security).
--From workbook launch VBE using short-key Alt+F11.
--From menu 'Insert' a module and paste the below code.
--Get back to Workbook.
--Run macro from Tools|Macro|Run <selected macro()>


Sub Macro()
Dim cell As Range
For Each cell In ActiveSheet.UsedRange
If cell.Text = "#DIV/0!" Then cell.ClearContents
Next
End Sub


--
Jacob (MVP - Excel)


"GoodyA10" wrote:

> I am building an execl sheet that holds production data. Some pieces of
> equipment only run occasionally and because of this they cause a #DIV/0!
> error. I want to be able to look through the complete range (a1:di54) of
> cells and whenver I find the error i want to delete the contents of the cell.
> I guess this is possible to do using a Visual Basic module but my experience
> of Visual Basic is limited.
From: Gary''s Student on 27 Apr 2010 07:37
Try this simple macro:

Sub ClearErrors()
Dim r As Range
Set r = Range("A1:DI54").Cells.SpecialCells(xlCellTypeFormulas, xlErrors)
r.Clear
End Sub



Macros are very easy to install and use:

1. ALT-F11 brings up the VBE window
2. ALT-I
ALT-M opens a fresh module
3. paste the stuff in and close the VBE window

If you save the workbook, the macro will be saved with it.

To remove the macro:

1. bring up the VBE window as above
2. clear the code out
3. close the VBE window

To use the macro from Excel:

1. ALT-F8
2. Select the macro
3. Touch RUN

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

--
Gary''s Student - gsnu201001


"GoodyA10" wrote:

> I am building an execl sheet that holds production data. Some pieces of
> equipment only run occasionally and because of this they cause a #DIV/0!
> error. I want to be able to look through the complete range (a1:di54) of
> cells and whenver I find the error i want to delete the contents of the cell.
> I guess this is possible to do using a Visual Basic module but my experience
> of Visual Basic is limited.
 | 
Pages: 1
Prev: Column to column of diff. wkbk
Next: Splitting Data from One Worksheet to Many Automatically