Round number in a range

[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.

 

[Harlan Grove]

Try
c.Value = Clng(c.Value)
instead of Round.

...

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.

 

[Ron Rosenfeld]

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.

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

 

[JL]

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.

 

[Ron Rosenfeld]

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.

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 Rosenfeld]

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.

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

 

[Harlan Grove]

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

....

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.

 

[Ron Rosenfeld]

Still, I think the
OP's problem is more likely to be recalculation during macro execution

Agree


--ron