Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Applying a macro to all worksheets | Excel Discussion (Misc queries) | |||
applying modules to mulitple worksheet | Excel Programming | |||
mulitple worksheets vlookup | Excel Discussion (Misc queries) | |||
Applying same macro to all worksheets in workbook | Excel Discussion (Misc queries) | |||
Changes to Mulitple Worksheets | Excel Worksheet Functions |