How can use round in a macro to get rid of the decimals???

**Science Boy** · 11-28-2007, 07:45 PM

Hi

I try to round a bunch of numbers like 832.3536367 to 832. So I want get rid of the decimals not just formatting to show less decimals

I want to do this in a macro by using a loop to go through each cell in a column until there are no more entrys (and it should carry on if the cell is empty) I've come up with the code below but this doesn't cut it even if all cells are NOT empty

Column A is filled with ref numbers like 1,2,3,4 etc
Column b is filled with the values like 4.1468 ; 540.4844; 1001.8811

Thanks in advance

Science boy

Please Login or Register  to view this content.

**Ikaabod** · 11-28-2007, 07:59 PM

Give this a try:

Please Login or Register  to view this content.

**Science Boy** · 11-28-2007, 08:12 PM

Thank you very much, Ikaabod

It works!!

Can you also (or anyone else) tell me what is wrong with my code?

**Ikaabod** · 11-28-2007, 08:43 PM

I. You never assign "number" a value so it defaults to zero.
____i. Thereby assigning "number2" the value "number" rounded, which is still zero
____ii. Then assigning the cell value "number2"'s value which is, again, zero

II. Your Do/Loop ends when it finds a blank cell in Column A
____i. Therefore, cells in Column B that were empty, now become zero if the corresponding Column A is not empty
____ii. If you have one empty cell in your list of data in Column A the macro may end prematurely.

III. By defining "number" and "number2" as Integers you may run into errors with larger numbers. The largest an integer may be is approximately +/- 32,800. Long and/or Double are able to be much larger. Be aware that Long and Double function differently than Integer and are not restricted to whole numbers.

**Science Boy** · 11-29-2007, 02:26 AM

I thought that

Please Login or Register  to view this content.

would assign the value in the cell to number.

There will always be a continuous series of numbers in Column A so when there are no numbers there the macro is allowed to stop.

**royUK** · 11-29-2007, 02:41 AM

You could also use Int to return the integer value of the number, eg

Please Login or Register  to view this content.

**mikerickson** · 11-29-2007, 04:24 AM

Originally Posted by Science Boy

I thought that

Please Login or Register  to view this content.

would assign the value in the cell to number.

That code puts the value of number into the cell.

If you want number to get its value from the cell, this would be the syntax

Please Login or Register  to view this content.

**Science Boy** · 11-29-2007, 05:55 AM

thanks mike,

That's what happens when you try things when you really should be sleeping

All,

Thanks all suggestions were of use and as far as i'm concerned this can be locked

How can use round in a macro to get rid of the decimals???

LinkBack

Thread Tools

Rate This Thread

Display

How can use round in a macro to get rid of the decimals???

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions