VBA - all excel sum functions doesn't work properly

Question

what's the issue? because of some strange reasons excel can't sum properly values from ranges.

please look in the

the proper sum of presented range is 0, excel tells that it sums to 1,818989E-12. when i select minor range (e.g. without first or last cell), it sums properly, when i change numberformat of range, it sums properly - but those workarounds works only in worksheet - when i use VBA (and actually this is a part of some macro) i'm still getting this strange number - every single WorksheetFunction like Sum, Subtotal, SumIf still returning inproper result, changing of numberformat of range doesn't work, multiplying by 1 also, so any suggestions will be welcome. i can tell also that this happens only in this specific case - probably the rest of analyzed data are fine (=sum works properly), also this is not a variable type issue, because first version of code didn't use any variable to mark the sum.

here's the part of code:

For Each kom In amountRng
    Set baza = Rows("" & prevKom.Offset(1, 0).Row & ":" & kom.Offset(-1, 0).Row & "")
    baza.Copy
    Sheets("roboczy").Activate
    Range("A2").PasteSpecial xlPasteValues

    'multipying by 1 doesn't work
    'Range("A1").End(xlToRight).Offset(0, 1).Select
    'Selection.Value = 1
    'Selection.Copy
    'Range(Range("V2"), Range("V2").End(xlDown)).PasteSpecial Paste:=xlPasteValues, Operation:=xlMultiply

    'changing of numberformat doesn't work
    'Columns("V:V").NumberFormat = "0.00"

        If IsEmpty(Range("A3")) Then
            Range("M2").Copy
            Range("A4").PasteSpecial xlPasteValues
        Else:
            Range(Range("M2"), Range("M2").End(xlDown)).Copy
            Range("A2").End(xlDown).Offset(2, 0).PasteSpecial xlPasteValues
        End If
    Selection.RemoveDuplicates 1
    Selection.CurrentRegion.Select

    Dim liczba As Single

    For Each zam In Selection
        Range("A1").CurrentRegion.AutoFilter field:=13, Criteria1:=zam.Value
        Set sumowanieRng = Range(Range("V" & Rows.Count).End(xlUp), Range("V2")).Cells.SpecialCells(xlCellTypeVisible)
        sumowanieRng.EntireRow.Copy
        liczba = WorksheetFunction.Sum(sumowanieRng)
        Debug.Print liczba
            If liczba = 0 Then
                Sheets("zerowe").Activate
                Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
            Else:
                Sheets("niezerowe").Activate
                Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
            End If
        Application.CutCopyMode = False
        Sheets("roboczy").Activate
        If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilterMode = False
    Next

    Range("A" & Rows.Count).End(xlUp).Select
    Range(Range("A2"), Selection).EntireRow.Clear
    Sheets(2).Activate
    Set prevKom = kom
Next

Look into SUbtotal(), you are using sum() and that may include the hidden columns — Davesexcel, Apr 25 '16 at 18:16
Hasn't this been discussed here a couple of times? It is [floating point arithmetic error](https://support.microsoft.com/en-us/kb/78113). — vacip, Apr 25 '16 at 18:22
[See this as well](http://stackoverflow.com/questions/177506/why-do-i-see-a-double-variable-initialized-to-some-value-like-21-4-as-21-3999996/177539#177539). — vacip, Apr 25 '16 at 18:25
thank you guys, sorry for duplicate the question. didn't hear earlier about floating points so i even didn't know what i should look for. — piwojsz, Apr 25 '16 at 19:00

score 0 · Answer 1 · answered Apr 25 '16 at 18:26

If you want a more accurate summation use a non-floating point data type like decimal

Dim d, c, i

For Each c In Range("B2:B25")
    d = d + CDec(c)
    i = i + CDbl(c)
Next c
Debug.Print d, i

Outputs

 0             1.81898940354586E-12

Keep in mind 1.81898940354586E-12 is VERY close to 0.

VBA - all excel sum functions doesn't work properly

please look in the

1 Answers1