Applying macro to mulitple worksheets?
 

Hello -

How do I apply the following macro to multiple worksheets at the same time?



Sub ErrorTrapAddDDL()

' Adds =If(IsError() around formulas

Dim cel As Range
Dim rng As Range
Dim Check As String

Const Equ As String = "=IF(ISERROR(_x) ,"""", _x)"

Check = Left$(Equ, 12) & "*" ' Check for =IF(ISERROR(

On Error Resume Next

Set rng = Selection.SpecialCells(xlFormulas, 23)
If rng Is Nothing Then Exit Sub

With WorksheetFunction
For Each cel In rng
If Not cel.Formula Like Check Then
cel.Formula = .Substitute(Equ, "_x", Mid$(cel.Formula, 2))
End If
Next
End With
End Sub

Applying macro to mulitple worksheets?
 

Here is my version of ErrorTrapAdd

Work it in with yours to achieve your ends.

Sub ErrorTrapAdd()
Dim mystr As String
Dim cel As Range
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
For Each cel In ws.UsedRange
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 & "),""""," & mystr & ")"
End If
End If
Next cel
Next ws
End Sub


Gord


On Mon, 6 Feb 2012 21:43:14 +0000, kb0621
wrote:


Hello -

How do I apply the following macro to multiple worksheets at the same
time?



Sub ErrorTrapAddDDL()

' Adds =If(IsError() around formulas

Dim cel As Range
Dim rng As Range
Dim Check As String

Const Equ As String = "=IF(ISERROR(_x) ,"""", _x)"

Check = Left$(Equ, 12) & "*" ' Check for =IF(ISERROR(

On Error Resume Next

Set rng = Selection.SpecialCells(xlFormulas, 23)
If rng Is Nothing Then Exit Sub

With WorksheetFunction
For Each cel In rng
If Not cel.Formula Like Check Then
cel.Formula = .Substitute(Equ, "_x", Mid$(cel.Formula, 2))
End If
Next
End With
End Sub