Need help on code - Macro on multiple worksheets

[J@Y]

Sub SheetSub()

Dim cel As Range
Dim myStr As String

For Each sh In Sheets(Array("Sheet1", "Sheet2", "Sheet3"))

For Each cel In Selection
If cel.HasFormula = True Then
If Not cel.Formula Like "=ROUND(*" Then
myStr = Right(cel.Formula, Len(cel.Formula) - 1)
cel.Value = "=ROUND(" & myStr & "," & "3" & ")"
End If
End If
Next
Next sh

End Sub

I am trying to have a macro that goes through all Sheets 1-3 and add
=Round() infront of a selection of cells. Right now, the code only performs
the function on the sheet I am currently on.

 

[Jim Cone]

Sub SheetSub()
Dim cel As Range
Dim myStr As String

Dim Sh As Object
Dim rng As Excel.Range
Set rng = Selection

For Each Sh In Sheets(Array("Sheet1", "Sheet2", "Sheet3"))
For Each cel In Sh.Range(rng.Address)
If cel.HasFormula = True Then
If Not cel.Formula Like "=ROUND(*" Then
myStr = Right(cel.Formula, Len(cel.Formula) - 1)
cel.Value = "=ROUND(" & myStr & "," & "3" & ")"
End If
End If
Next
Next 'Sh
End Sub
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"J@Y" <[email protected]>
wrote in message
Sub SheetSub()
Dim cel As Range
Dim myStr As String
For Each sh In Sheets(Array("Sheet1", "Sheet2", "Sheet3"))
For Each cel In Selection
If cel.HasFormula = True Then
If Not cel.Formula Like "=ROUND(*" Then
myStr = Right(cel.Formula, Len(cel.Formula) - 1)
cel.Value = "=ROUND(" & myStr & "," & "3" & ")"
End If
End If
Next
Next sh
End Sub

I am trying to have a macro that goes through all Sheets 1-3 and add
=Round() infront of a selection of cells. Right now, the code only performs
the function on the sheet I am currently on.

 

[Dave Peterson]

So it's based on the current selection in each sheet--and each sheet can have
different cells selected?

If yes:

Option Explicit

Sub SheetSub()

Dim cel As Range
Dim myStr As String
Dim sh As Worksheet
For Each sh In Sheets(Array("Sheet1", "Sheet2", "Sheet3"))
sh.Select
For Each cel In Selection
If cel.HasFormula = True Then
If Not cel.Formula Like "=ROUND(*" Then
myStr = Right(cel.Formula, Len(cel.Formula) - 1)
cel.Value = "=ROUND(" & myStr & "," & "3" & ")"
End If
End If
Next cel
Next sh


End Sub