IS ERROR Formula

[mwhit]

Hello, I need to add the IS ERROR formula to a lot of cells on a worksheet. Is there a quick way to do this; possibly through a macro…

Thanks

 

[btadams]

Here's a macro I use:

Sub ClearAllErrors()
Dim CellFormula As String

     If TypeName(Selection) <> "Range" Then
        MsgBox "Please select a range of cells.", vbInformation + vbOKOnly, "Invalid Range Selection"
        Exit Sub
    End If
    On Error GoTo exitsub
    intResponse = MsgBox("This macro will adjust all formulas in the currently selected cells" & vbCrLf & "so that they will not display error messages.", vbOKCancel, "Clear Error Messages")
    If intResponse = vbOK Then
        Application.ScreenUpdating = False
        If Selection.Rows.Count = 65536 Then SelectFirstCellLastCell
        Set rngMyRange = Selection  '.SpecialCells(xlCellTypeFormulas)
        'MsgBox rngMyRange.Address
        For Each cell In rngMyRange
'            If IsError(Cell.Value) Then
                CellFormula = "'" & cell.Formula
                CellFormula = Right(CellFormula, Len(CellFormula) - 2)
                cell.Formula = "=IF(ISERROR(" & CellFormula & "),""""," & CellFormula & ")"
'            End If
        Next cell
        Application.ScreenUpdating = True
    End If
exitsub:
    On Error GoTo 0
End Sub

 

[mwhit]

Btadams,

Appreciate your help. I am not too good with Macros. Since this is "code", what is the process for me to make this happen. I am assuming I will not do this like i was writing the macro myself...

Thanks again for your help.

 

[btadams]

Mark,

Copy the code. In Excel, open the visual basic editor (alt+F11). If you have a personal macro workbook (Personal.xls), select that on the left side of the VB editor. Otherwise select the workbook you're working with (It should show up as VBAProject(My Workbook.xls)). Go to the Insert menu and choose Module. Paste the code in the white area. Then select the cells in your worksheet and run the macro (alt-F8) named ClearAllErrors

 

[mwhit]

I am having trouble with the macro. I get a Compile error: Sub or Function not defined

Since I am not familiar with macros, i just copied the code as is. I would think I would need to change something to work with my worksheet...

Let me be a little more clear with what i am dealing with:

I have a worksheet with 2500 lines with formulas from cell C - cell N that doesn't have any errors. However, once I change some settings and query the data i will get errors due to not all sections are applicable to the new query.

Instead of me putting the ISERROR formula in each cell, I was looking to automate that feature. Each cell in column c will not have the same formula. I would greatly appreciate any more direction that anyone can offer.

 

[btadams]

Sorry, I've been away at a conference. Is there a particular line in the code highlighted when you get the error message?

 

[btadams]

My bad. There is another macro which the previous one calls in case the user has selected an entire column. Copy and paste the code below into the same code module as the previous code:

Sub SelectFirstCellLastCell()
Dim LastRow As Long, LastCol As Long
    Set rngMyRange = Selection
    
    With rngMyRange
    
    ' Find the last real row
    
      LastRow = .Cells.Find(What:="*", _
        SearchDirection:=xlPrevious, _
        SearchOrder:=xlByRows).Row
    
    ' Find the last real column
    
      LastCol = .Cells.Find(What:="*", _
        SearchDirection:=xlPrevious, _
        SearchOrder:=xlByColumns).Column
    
    End With

  Range(Cells(ActiveCell.Row, ActiveCell.Column), Cells(LastRow, LastCol)).Select

End Sub