Why does SumIf returns 0 with Double and returns true with integer in VBA?

741 views Asked by At

The below is a simple SumIf calculation where I want to sumif >= the variable k that is declared as a Double.

This, however returns 0. If I change k for any integer, 3, 4 etc, it returns the true value. But if I change k for 4.23983 it returns 0.

I already tried to declare another Double variable (lets say a) and put a = sumif(...). But that gives me 0 as well. I also tried "">="& k &""" but that doesn't work either (found that on Google).

Worksheets("ES").Cells(j + 10, 6).Value = Application.WorksheetFunction.SumIf(Worksheets("help").Range("A1:A4273"), ">=" & k, Worksheets("help").Range("A1:A4273"))
1

There are 1 answers

6
tbm0115 On

Last edit. Based on comments, circumstances require conversion from European format to US for calculations. Please refer to this post for bulk of code used: VBA - Evaluate Conditions as String

Here's a function that can be added to a module and used freely in the worksheet:



    Function EuroSumIf(ByVal CellRange As Range, ByVal Condition As String, ByVal CompareVal As Double) As Double
        Dim cond As String

        Dim vx As MSScriptControl.ScriptControl
        Set vx = New MSScriptControl.ScriptControl
        Dim sum As Double

        For Each cell In CellRange.Cells
            a = cell.Value
            b = CompareVal
            If InStr(1, a, ".", vbTextCompare) > -1 Then
                a = Replace(a, ".", "")
            End If
            If InStr(1, a, ",", vbTextCompare) > -1 Then
                a = Replace(a, ",", ".")
            End If
            cond = "(a" & Condition & "b)"
            'MsgBox (cond & vbLf & a & Condition & b)
            With vx
                .Language = "VBScript"
                .AddCode "function stub(a,b): stub=" & cond & ": end function"
                If .Run("stub", a, b) Then
                    sum = sum + a
                End If
            End With
        Next
        EuroSumIf = sum
    End Function

Use as follows:



    =EuroSumIf(A1:A3,">",0)