Round number in a range

  • Thread starter Thread starter JL
  • Start date Start date
J

JL

Hi,
Here is my problem.
I am trying to write a macro to round number in a range of
cell.
This is the macro.
========================================================
Sub Whole_Number()
Dim c As Range

For Each c In Worksheets("LC").Range("E5:AM9")
c.Value = Round(c.Value)
Next c
End Sub
=========================================================
But, it will lock up Excel (99% CPU utilized). Is there
something wrong with what I did?

Thank you for the help.
 
Try
c.Value = Clng(c.Value)
instead of Round.
Click to expand...
...

Is Round really this much of a slug? CLng would return errors if c.Value exceeds
2^31 (approx. 2.15E9) in absolute value. Maybe not a problem for the OP, but

c.Value = Int(c.Value + 0.5)

would be more robust. As for speed, it'd be slower than CLng, but I'm not going
to guess by how much.

Even so, I'd guess what the OP needs to do is bracket his/her loop with

Application.Calculation = xlCalculationManual
'loop here
Application.Calculation = xlCalculationAutomatic

because I'd guess it's other cells dependent on the ones being changed that are
affecting performance.
 
Is Round really this much of a slug? CLng would return errors if c.Value exceeds
2^31 (approx. 2.15E9) in absolute value. Maybe not a problem for the OP, but

c.Value = Int(c.Value + 0.5)

would be more robust.
Click to expand...

It might be more robust, but it won't always return the same answer as the VBA
round function.

n VBA Round Int(n+0.5)
3.5 4 4
4.5 4 5




--ron
 
c.Value = Int(c.Value + 0.5)
Thank you for the suggestions.
I have try both, it seems both locked up Excel (not the
computer).

Interesting results,
**c.Value = Int(c.Value + 0.5)** just change my cell
to "TRUE". And
**>>c.Value = CLng(c.Value +0.5)** just locked up Excel.

I can type faster than it is doing the roudning.
I guess I am asking if there is another way to do what I
need to do. I am just doing a small section of the sheet
(E5:AM9) and it is taking for ever.

Any ideas or suggestions is appreciated.
 
Thank you for the suggestions.
I have try both, it seems both locked up Excel (not the
computer).

Interesting results,
**c.Value = Int(c.Value + 0.5)** just change my cell
to "TRUE". And
**>>c.Value = CLng(c.Value +0.5)** just locked up Excel.

I can type faster than it is doing the roudning.
I guess I am asking if there is another way to do what I
need to do. I am just doing a small section of the sheet
(E5:AM9) and it is taking for ever.

Any ideas or suggestions is appreciated.
Click to expand...

Something is odd about your setup, then, I think. I pasted your macro into a
module in my VB Editor; named a worksheet LC, and put some random numbers in
some of the cells in the range.

When I executed the Sub, everything worked fine, and rapidly. If there was
text in one of the cells, I would get a type mis-match error, but no hanging.
--ron
 
Ron,
Thank you for testing it for me.
I guess coding wise there is no problem. I have to look
into my hardware or configuration setup of my environment.
Click to expand...

Try it first on a new workbook; new instance of Excel; with only that macro in
the new workbook and no data other than in the "Rounded Range". If it works
there, then perhaps there is something in your workbook -- perhaps an event
macro that is getting triggered.

Or perhaps there are other cells dependent on the cells that are getting
rounded, so every time you change one cell in that range, it triggers an entire
sheet recalculation. Or...


--ron
 
Ron Rosenfeld said:
It might be more robust, but it won't always return the same answer as
the VBA round function.
Click to expand...
....

I forgot VBA's Round and CLng both use banker's rounding. Still, I think the
OP's problem is more likely to be recalculation during macro execution than
whether Round or CLng is used.
 
Back
Top