problem with rounding

littleme

Board Regular
Joined
Nov 15, 2004
Messages
156
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()
 
Upvote 0
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
 
Upvote 0
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...
 
Upvote 0
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)
 
Upvote 0
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...
 
Upvote 0
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 =)
 
Upvote 0
THANK YOU!!!! =) Got what i needed when i changed long to single... thank you thank you thank you
 
Upvote 0

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top