Program to make cell a formula
 

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!

Program to make cell a formula
 

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!

Program to make cell a formula
 

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.

Program to make cell a formula
 

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!

Program to make cell a formula
 

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.