Inserting a function

[Don]

I have a spreadsheet with a variety of formulas on it that I now need
to round to thousands. I tried to build a macro that edits the
selected cell and inserts the Round function around the existing
formulas as follows

Public Sub AddRound()

SendKeys ("{F2}")
SendKeys ("{HOME}")
SendKeys ("=round(")
SendKeys ("{END}")
SendKeys (",)")
SendKeys ("{ENTER}")


End Sub

I get and invalid procedure call error message. Any suggestions and is
this a good approach?

Thanks
Don

 

[Dave Peterson]

It looks like you're an old 123 user.

With excel, you can manipulate the formula in the cell and skip all those
keystrokes.

Option Explicit
Sub testme()

Dim myCell As Range
Dim myRng As Range
Dim myStr As String

Set myRng = Nothing
On Error Resume Next
Set myRng = Intersect(Selection, _
Selection.Cells.SpecialCells(xlCellTypeFormulas))
On Error GoTo 0

If myRng Is Nothing Then
MsgBox "No formulas in selection!"
Exit Sub
End If

For Each myCell In myRng.Cells
With myCell
myStr = Mid(.Formula, 2)
.Formula = "=round(" & myStr & ",-3)"
End With
Next myCell

End Sub

Select your range and try the macro.

You may want to try it on a few cells--just to see if I understood what you
meant.

 

[Guest]

Public Function CvtFormula(rng As Range)
For Each Cell In rng
s = Cell.Formula
s = Right(s, Len(s) - 1)
s = "=round(" & s & ",-3)"
Cell.Formula = s
Next
CvtFormula = rng(1).Formula
End Function

Demo'd from the immediate window:
? ActiveCell.Formula
=TRUNC(RAND()*500000)
? Cvtformula(activecell)
=ROUND(TRUNC(RAND()*500000),-3)
? activeCell.Formula
=ROUND(TRUNC(RAND()*500000),-3)

the returned value of the function was just for demo purposes. It doesn't
need to return any value.

 

[Don]

Thanks guys, it work like a charm!

Don

 

[Don]

Wow, error handling and all. Also, spot on for the 123 user comment (
I won't say anything about "old"). Thanks for the help.

Don