Program to make cell a formula

  • Thread starter Thread starter Craig
  • Start date Start date
C

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!
 
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)
 
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! Exactly what I needed.

Craig


Bob Phillips said:
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)
Click to expand...
 
Back
Top