problem with rounding

[littleme]

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 =)

 

[BrianB]

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()

 

[littleme]

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

 

[erik.van.geit]

Hello, guys,
the problem is here

Dim FloorStart As Integer

try this

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

 

[littleme]

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

 

[erik.van.geit]

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)

 

[littleme]

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

 

[tusharm]

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.

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 =)

 

[littleme]

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

 

[tusharm]

You are welcome.