myCell.Formula application unknown object error

[Guest]

I'm trying to use this macro but am having an application, unknown object
error:

Option Explicit
Sub round_made_easy()
Dim myCell As Range
For Each myCell In Selection.Cells
myCell.Formula = "=Round(" & myCell.Formula & ",2)"
Next myCell
End Sub

Now when I try the above it gives me an application error.
But with the following code that you gave me it works great.

Option Explicit
Sub round_made_easy()
Dim myCell As Range
For Each myCell In Selection.Cells
myCell.Formula = "=Round(" & myCell.Value & ",2)"
Next myCell
End Sub

why won't it work for a cell that is =1258/4569871 and keep the 1258/4569871
in the cell's formula of =Round(1258/4569871, 2) ???

Thanks!

 

[Guest]

mycell.formula starts with an = sign. Following seems to work
myCell.Formula = "=Round(" & Right(myCell.Formula, Len(myCell.Formula) - 1)
& ",2)"

 

[JE McGimpsey]

Using myCell.Formula brings along the "=" sign in the formula.

Try:

Public Sub Round_Made_Easy()
Dim myCell As Range
For Each myCell In Selection.Cells
With myCell
If Not IsEmpty(.Value) Then _
myCell.Formula = "=ROUND(" & _
Mid(.Formula, 1 - .HasFormula) & ",2)"
End With
Next myCell
End Sub

 

[Guest]

THank you!!!

mycell.formula starts with an = sign. Following seems to work
myCell.Formula = "=Round(" & Right(myCell.Formula, Len(myCell.Formula) - 1)
& ",2)"

 

[Gord Dibben]

What's wrong with keeping to your original thread in the other news group?

Wastes a lot of time and effort giving the same answers in more than one group.


Gord Dibben MS Excel MVP