Program to make cell a formula

[Craig]

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!

 

[Bob Phillips]

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)

 

[Lonnie M.]

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.

 

[Craig]

Thanks! Exactly what I needed.

Craig

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]

Thanks, Lonnie!