myCell.Formula application unknown object error

O

orangie

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!
 
G

Gleam

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

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
 
O

orangie

THank you!!! :)

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

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top