If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
macro - replace
hi, i have various worksheets and within that worksheet there are cells
having #DIV/0!. I want this to be replace by zero. i know there is a formula which will give out zero but if someone can write a macro would good. Thanks |
#2
|
|||
|
|||
macro - replace
Sub ErrorTrapAdd()
Dim mystr As String Dim cel As Range For Each cel In Selection If cel.HasFormula = True Then If Not cel.Formula Like "=IF(ISERROR*" Then mystr = Right(cel.Formula, Len(cel.Formula) - 1) cel.Value = "=IF(ISERROR(" & mystr & "),0," & mystr & ")" End If End If Next End Sub Gord Dibben MS Excel MVP On Thu, 25 Mar 2010 20:11:01 -0700, Rohit wrote: hi, i have various worksheets and within that worksheet there are cells having #DIV/0!. I want this to be replace by zero. i know there is a formula which will give out zero but if someone can write a macro would good. Thanks |
#3
|
|||
|
|||
macro - replace
hi
the formula is the microsoft way of dealing with the problem. see this site. http://support.microsoft.com/kb/182188 i know of no macro command that would make the #DEV/0! error go away other than to replace the formula with zero or replace the formula with the above formula. regards FSt1 "Rohit" wrote: hi, i have various worksheets and within that worksheet there are cells having #DIV/0!. I want this to be replace by zero. i know there is a formula which will give out zero but if someone can write a macro would good. Thanks |
Thread Tools | |
Display Modes | |
|
|