vba sumifs with date range dosen't work

[przemek]

Hi

I'm trying to use vba to go through list of items and get a sum of receipts/commitment for each item. It worked fine till I added date condition st_date and end_date. date_rng consist dates in format dd/mm/yyyy eg. 08/05/2017
first sumifs works OK, but second one fails.

Appreciate help or suggestions.

    str_date = Application.InputBox(prompt:="Please enter focus week - dd/mm/yyyy", Type:=2)
    st_date = Format(CDate(str_date), "dd-mmm")
    end_date = DateAdd("d", 14, st_date)
    
    Cells(2, 10) = st_date
    Cells(3, 10) = end_date
    x = Cells(Rows.Count, "A").End(xlUp).Row
    
    For i = 2 To x
    sku = Cells(i, 1)
    mp = Cells(i, 5)
    dd = WorksheetFunction.SumIfs([comm_rng], [sku_rng], sku, [plant_rng], mp, [doc_rng], ">7", [date_rng], ">=" & st_date)
   comm = WorksheetFunction.SumIfs([rece_rng], [sku_rng], sku, [plant_rng], mp, [doc_rng], "<3", [date_rng], "<=" & end_date)
    
    Cells(i, 6) = dd
    Cells(i, 7) = comm
        
    Next

when vba puts st_date and end_date to worksheet results have 2 different formats, thought both are date type
[TABLE="width: 170"]
<tbody>[TR]
[TD]cells(2,10)[/TD]
[TD="align: right"]01-May[/TD]
[/TR]
[TR]
[TD]cells(3,10) [/TD]
[TD="align: right"]15/05/2017[/TD]
[/TR]
</tbody><colgroup><col span="2"></colgroup>[/TABLE]


THANKS

 

[Worf]

Hi

Sub sdates()
Dim sku, st_date$, i%, mp


st_date = Format(CDate(Application.InputBox(prompt:="Please enter focus week - dd/mm/yyyy", _
Type:=2)), "dd-mm")
[j2] = Format(st_date, "dd/mm/yyyy")
[j3] = Format(CDate(DateAdd("d", 14, st_date)), "dd/mm/yyyy")


For i = 2 To Cells(Rows.count, "A").End(xlUp).Row
    sku = Cells(i, 1)
    mp = Cells(i, 5)
    Cells(i, 6) = WorksheetFunction.SumIfs([comm_rng], [sku_rng], sku, _
    [plant_rng], mp, [doc_rng], ">7", [date_rng], ">=" & st_date)
    Cells(i, 7).Formula = "=SUMIFS(rece_rng,sku_rng," & sku & ",plant_rng," & _
    mp & ",doc_rng,""<3"",date_rng,""<=""&J3)"
Next


End Sub

 

[przemek]

Thanks Worf

Initially I had #NAME? error from Cells(i, 7).Formula = "=SUMIFS(rece_rng,sku_rng," & sku & ",plant_rng," & _
mp & ",doc_rng,""<3"",date_rng,""<=""&J3)" - but form that after further reading on this I ended with converting column A from date to general format and then used CDbl to convert input date to double. My end code is below - and works nice for me.

    str_date = Application.InputBox(prompt:="Please enter focus week - dd/mm/yyyy", Type:=2)
    sd = CDbl(CDate(str_date))
    fd = CDbl(DateAdd("d", 14, str_date))
    
    x = Cells(Rows.Count, "A").End(xlUp).Row
    For i = 2 To x
    sku = Cells(i, 1)
    mp = Cells(i, 5)
        dd = WorksheetFunction.SumIfs([comm_rng], [sku_rng], sku, [plant_rng], mp, [doc_rng], ">7", [date_rng], ">=" & sd, [date_rng], "<=" & fd)
        prod = WorksheetFunction.SumIfs([rece_rng], [sku_rng], sku, [plant_rng], mp, [doc_rng], "<3", [date_rng], ">=" & sd, [date_rng], "<=" & fd)
        Cells(i, 6) = dd
        Cells(i, 7) = prod
    Next

thanks again