There may be a fast, non-looping way to do this, but a couple of questions first...I would like a macro to clear all items in Col E containing #N/A
Sub Replace()
Columns("E:E").Select
Selection.Replace What:="#N/A", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End Sub
Yep... that's the one I was aiming for with my questions.Or
Code:Sub test() Columns("E").SpecialCells(xlCellTypeFormulas, xlErrors).ClearContents End Sub
Option Explicit
Sub AddIFERROR()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim xCell As Range
Dim xFormula As String
For Each xCell In Selection
If xCell.HasFormula Then
xFormula = Right(xCell.Formula, Len(xCell.Formula) - 1)
xCell.Formula = "=IFERROR(" & xFormula & ","""")"
End If
Next xCell
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub