Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 228
  • Last Modified:

Identify rows that have generated errors through their calculations



I have columns B through IV, rows 4 -45

all of the results for these fields are generated through calculations.  Some experience errors and result in #Value! or #DIV/0!  I don't want to change the numbers being generated and know why those errors occur.  But I would like to run a macro that woudl go through all the rows and evrytime it runs into one that generated an error change it to blank, without it removing the calculation that still sits in the cell - is this possible?
0
Mosquitoe
Asked:
Mosquitoe
1 Solution
 
KurtVonCommented:
I'm assuming you are using Excel.

If so, just change the formulas to use error checking:

=if(ISERR(originalformula), "", originalformula)

I suppose you could write a macro to change that.  I know of no other way to "blank" a cell and leave the formula intact at the same time.

Hoep this helps.
0
 
Dave BrettVice President - Business EvaluationCommented:
Hi Mosquitoe,

If you run this maco it will convert all error cells in the activesheet to blanks without removing the formula

I suggest saving yur work before running this

Cheers

Dave

Sub MakeErroBlank()
Dim myrange    As Range
Dim cel        As Range
Dim cf As String
    On Error Resume Next
    Set myrange = ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas, xlErrors)
    On Error GoTo 0
    If myrange Is Nothing Then Exit Sub
    For Each cel In myrange
        cf = Right(cel.Formula, Len(cel.Formula) - 1)
        cel.FormulaR1C1 = "=IF(ISERROR(" & cf & "),""""," & cf & ")"
    Next
End Sub

Cheers

Dave
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now