I have several employees who send me there budget numbers that are numbers
hard coded into cell. I need to round all of these numbers to the nearest
one hundred. I have a macro that I found on here that I can run but it only
works if the cell contains a formula. Is there a way that I can make the
hard coded numbers a formula so I can use the Rounding Macro or is there a
was to modify this macro to make it work with hard coded number.


Sub RoundAdd2()
Dim myStr As String
Dim Cel As Range
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 & "," & "-2" & ")"
End If
End If
Next
End Sub



Thanks for any suggestions!

Sub RoundAdd2()
Dim myStr As String
Dim Cel As Range
For Each Cel In Selection
If IsNumeric(Cel.Value) Then
If Cel.Value < "" Then
Cel.Value = Application.Round(Cel.Value, -2)
End If
End If
Next
End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Craig" wrote in message
...
I have several employees who send me there budget numbers that are numbers
hard coded into cell. I need to round all of these numbers to the nearest
one hundred. I have a macro that I found on here that I can run but it
only
works if the cell contains a formula. Is there a way that I can make the
hard coded numbers a formula so I can use the Rounding Macro or is there a
was to modify this macro to make it work with hard coded number.


Sub RoundAdd2()
Dim myStr As String
Dim Cel As Range
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 & "," & "-2" & ")"
End If
End If
Next
End Sub



Thanks for any suggestions!

Thanks! Exactly what I needed.

Craig


"Bob Phillips" wrote in message
...
Sub RoundAdd2()
Dim myStr As String
Dim Cel As Range
For Each Cel In Selection
If IsNumeric(Cel.Value) Then
If Cel.Value < "" Then
Cel.Value = Application.Round(Cel.Value, -2)
End If
End If
Next
End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Craig" wrote in message
...
I have several employees who send me there budget numbers that are
numbers
hard coded into cell. I need to round all of these numbers to the
nearest
one hundred. I have a macro that I found on here that I can run but it
only
works if the cell contains a formula. Is there a way that I can make the
hard coded numbers a formula so I can use the Rounding Macro or is there
a
was to modify this macro to make it work with hard coded number.


Sub RoundAdd2()
Dim myStr As String
Dim Cel As Range
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 & "," & "-2" & ")"
End If
End If
Next
End Sub



Thanks for any suggestions!

Hi Craig, if I understand your question, the following modification
to your code should work:

Sub RoundAdd2()
Dim myStr As String
Dim Cel As Range
For Each Cel In Selection
Cel.Value = Application.WorksheetFunction.Round(Cel.Value, -2)
Next
End Sub

HTH--Lonnie M.

Thanks, Lonnie!


"Lonnie M." wrote in message
oups.com...
Hi Craig, if I understand your question, the following modification
to your code should work:

Sub RoundAdd2()
Dim myStr As String
Dim Cel As Range
For Each Cel In Selection
Cel.Value = Application.WorksheetFunction.Round(Cel.Value, -2)
Next
End Sub

HTH--Lonnie M.