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

[Science Boy]

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

Sub roundnumbers()

Dim number As Integer
Dim number2 As Integer


i = 1

Do

 
Cells(i + 1, 2).Value = number
number2 = Round(number, 0)
Cells(i + 1, 2).Value = number2

i = i + 1


Loop Until Cells(i + 1, 1).Value = ""



End Sub

[Ikaabod]

Give this a try:

Sub roundNumbers()
    Application.ScreenUpdating = False
    Dim rng As Range
    For Each rng In Range("B:B")
        If rng.Value <> "" Then rng.Value = Round(rng, 0)
    Next rng
    Application.ScreenUpdating = True
End Sub

[Science Boy]

Thank you very much, Ikaabod

It works!!


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

[Ikaabod]

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]

I thought that

 Cells(i + 1, 2).Value = number

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]

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

MsgBox Int(123.456)

[mikerickson]

I thought that

 Cells(i + 1, 2).Value = number

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

number = Cells(i + 1, 2).Value