# problem with rounding

#### littleme

##### Board Regular
Have the following code to sum a chaning range of values. Works fine except, excel always rounds the decimal up or down regardless if its ,2 or ,5. Always makes so that the sum end in a nice even ,0. Is there something in the code or is there a general excel preference that ive missed?

Cannot post the sheet as I am working on a mac. Here is the code though:

Sub GetTotals2()
Dim FloorStart As Integer, LastRow As Integer, FloorFinish As Integer, iCol As Byte
Dim FloorArea As Long

LastRow = Worksheets("Med LGH namn").Cells(Rows.Count, 1).End(xlUp).Row - 5
FloorStart = 4
FloorFinish = 4
FloorArea = 0
Do Until FloorStart = LastRow
Do Until Cells(FloorStart, 1) <> ""
FloorStart = FloorStart + 1
Loop
FloorFinish = FloorStart
Do Until Cells(FloorFinish + 1, 1) <> ""
FloorFinish = FloorFinish + 1
Loop
For iCol = 3 To 11 Step 2
FloorArea = FloorArea + WorksheetFunction.Sum(Range(Cells(FloorStart, iCol), Cells(FloorFinish, iCol)))
Next iCol
Range("L" & FloorFinish) = FloorArea
FloorArea = 0
FloorStart = FloorFinish + 1
Loop
Cells(FloorFinish + 4, 12) = WorksheetFunction.Sum(Range("L2", "L" & FloorFinish))
End Sub

Would be grateful if someone would help me get my boss off my back =)

### Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
Not sure what you mean. As far as I can see, all you need to do is reformat the cells to show the decimal points. Or do you want to round numbers ?
There are 3 basic round functions (there are others)
ROUND() where .5 and up go to next highest (same as fomatting cell)
ROUNDUP()
ROUNDDOWN()

Sorry.. should have mentioned that the cell is formatted as number with 1 decimal...

Hello, guys,
the problem is here
Code:
``Dim FloorStart As Integer``

try this
Code:
``````Sub test()
Dim FloorStart As Integer
FloorStart = 2.55
MsgBox FloorStart
Dim J As Single 'or Double
J = 2.55
MsgBox J
End Sub``````

kind regards,
Erik

suspected it had something to do with Integer...

Ran the test and got first 3 then 2,55 in the msg box... What is that supposed to tell me? =S sorry.. am really not good at this so am very glad when people take the time to help...

take a look in the helpfiles for "datatypes" (or something like that)
you will find it quickly when clicking in any of those words like "integer" "long" ...

you can compare it with bitmaps
you tell in advance "this is black&white"
then you add a colorphoto ==> no colors will be displayed

when you tell to your machine: "please reserve me some space for variable FloorStart: I just need an Integer (number without decimals)" you will not be able anymore to put decimals in that variable (since your reserved memory is to small)

Ok... i get what you.re saying but cant get it to work. Tried changing Dim Floorstart as Integer to Decimal, to Long, to Double... and no change... still rounds 13,2 to 13...

It isn't clear from your description as to what the problem is but if it is that the sum computed for FloorStart,...:FloorFinish,... is always a whole number it is because FloorArea is declared as long. If you check the Long data type you will see it can only contain whole numbers. Instead, use either Single or Double. Check each in XL VBA help to see which might be the most appropriate.

littleme said:
Have the following code to sum a chaning range of values. Works fine except, excel always rounds the decimal up or down regardless if its ,2 or ,5. Always makes so that the sum end in a nice even ,0. Is there something in the code or is there a general excel preference that ive missed?

Cannot post the sheet as I am working on a mac. Here is the code though:

Sub GetTotals2()
Dim FloorStart As Integer, LastRow As Integer, FloorFinish As Integer, iCol As Byte
Dim FloorArea As Long

LastRow = Worksheets("Med LGH namn").Cells(Rows.Count, 1).End(xlUp).Row - 5
FloorStart = 4
FloorFinish = 4
FloorArea = 0
Do Until FloorStart = LastRow
Do Until Cells(FloorStart, 1) <> ""
FloorStart = FloorStart + 1
Loop
FloorFinish = FloorStart
Do Until Cells(FloorFinish + 1, 1) <> ""
FloorFinish = FloorFinish + 1
Loop
For iCol = 3 To 11 Step 2
FloorArea = FloorArea + WorksheetFunction.Sum(Range(Cells(FloorStart, iCol), Cells(FloorFinish, iCol)))
Next iCol
Range("L" & FloorFinish) = FloorArea
FloorArea = 0
FloorStart = FloorFinish + 1
Loop
Cells(FloorFinish + 4, 12) = WorksheetFunction.Sum(Range("L2", "L" & FloorFinish))
End Sub

Would be grateful if someone would help me get my boss off my back =)

THANK YOU!!!! =) Got what i needed when i changed long to single... thank you thank you thank you

You are welcome.

Replies
0
Views
410
Replies
10
Views
604
Replies
17
Views
2K
Replies
4
Views
602
Replies
6
Views
346

1,214,627
Messages
6,120,610
Members
448,973
Latest member
ChristineC

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back