I Have some code that uses the worksheetfunction SumIf by selecting some dates. When I use the datefunction Now() I get strange results.

The trick I want to do is calculate som subtotalls depending from a period between now() and some other date. However when I use Now() I get strange subtotalls While when I use I Date in the far future ( ie "01-01-2900") I get the right results

this code gives strange results

With Worksheets(1).Range("B:B")
    Set c = .Find("STG PENSIOENFONDS ABP", LookIn:=xlValues, Searchorder:=xlByColumns)
    If Not c Is Nothing Then
        firstaddress = c.Address
       **arg2 = Now()**
        Do
            arg1 = CDbl(c.Offset(0, -1).Value)
            If Month(arg1) + 1 = 13 Then
                'MsgBox (Day(arg1) & "-" & Replace(Month(arg1) + 1, "13", "1") & "-" & Year(arg1) + 1)
                c.Offset(0, 6).Value = CDate(Day(arg1) & "-" & Replace(Month(arg1) + 1, "13", "1") & "-" & Year(arg1) + 1)
            Else
                c.Offset(0, 6).Value = CDate(Day(arg1) & "-" & Replace(Month(arg1) + 1, "13", "1") & "-" & Year(arg1))
            End If

            som1 = WorksheetFunction.SumIf(Range("A:A"), ">=" & CDbl(c.Offset(0, -1).Value), Range("G:G"))
            som2 = WorksheetFunction.SumIf(Range("A:A"), ">=" & arg2, Range("G:G"))
            som3 = som1 - som2
            c.Offset(0, 7) = som3
            arg2 = CDbl(c.Offset(0, -1).Value)
            Set c = .FindNext(c)
        Loop While Not c Is Nothing And c.Address <> firstaddress
    End If

This works correct

With Worksheets(1).Range("B:B")
    Set c = .Find("STG PENSIOENFONDS ABP", LookIn:=xlValues, Searchorder:=xlByColumns)
    If Not c Is Nothing Then
        firstaddress = c.Address
      **arg2 = "01-01-2919"**
        Do
            arg1 = CDbl(c.Offset(0, -1).Value)
            If Month(arg1) + 1 = 13 Then
                'MsgBox (Day(arg1) & "-" & Replace(Month(arg1) + 1, "13", "1") & "-" & Year(arg1) + 1)
                c.Offset(0, 6).Value = CDate(Day(arg1) & "-" & Replace(Month(arg1) + 1, "13", "1") & "-" & Year(arg1) + 1)
            Else
                c.Offset(0, 6).Value = CDate(Day(arg1) & "-" & Replace(Month(arg1) + 1, "13", "1") & "-" & Year(arg1))
            End If

            som1 = WorksheetFunction.SumIf(Range("A:A"), ">=" & CDbl(c.Offset(0, -1).Value), Range("G:G"))
            som2 = WorksheetFunction.SumIf(Range("A:A"), ">=" & arg2, Range("G:G"))
            som3 = som1 - som2
            c.Offset(0, 7) = som3
            arg2 = CDbl(c.Offset(0, -1).Value)
            Set c = .FindNext(c)
        Loop While Not c Is Nothing And c.Address <> firstaddress
    End If

When I use Now() the subtotall is a figure I don't understand, when I use "01-01-2900" I get the expected subtotall

0 Answers