Macro to sumifs a range of values based on dates

[hrh_dash]

I would like to translate the following excel formula to a macro for the sumifs of values based on dates.

Currently I am getting an error msg pop up "Run time error 13 Type mismatch"

Excel formula: 

SUMIFS(B:B,A:A,">="&DATE(YEAR(TEXT(TODAY()-DAY(TODAY()),"Mmm-YY")),MONTH(TEXT(TODAY()-DAY(TODAY()),"Mmm-YY")),1),A:A,"<="&EOMONTH(TEXT(TODAY()-DAY(TODAY()),"Mmm-YY"),0))

Macro as follows:

Dim prevdate As String
Dim ws As Worksheet

Set ws = Sheet1

prevdate = Format(DateAdd("M", -1, Now), "mmm-yy")

    With ws.Range("G3")
        .NumberFormat = "#,##0.00"
        .Formula = "=SUMIFS(B:B,A:A, & " >= " & DATE(YEAR(prevdate),MONTH(prevdate),1),A:A, & " <= " &EOMONTH(prevdate,0))"
        .Value = .Value
    End With

Appreciate the help in advance!

[#2 · Accepted solution]

@hrh_dash 

You have to concatenate with the variable prevdate, and double the quotes in the string.

        .Formula = "=SUMIFS(B:B,A:A,"">=""&DATE(YEAR(" & prevdate & "),MONTH(" & prevdate & "),1),A:A,""<=""&EOMONTH(" & prevdate & ",0))"

[#3]

, thanks for the assist!

[#4]

@Hans Vogelaar , i have a similar issue regarding on this thus did not create another new thread..

I am not sure how to correct the code below to make it work on the IF Function portion..

Sub performance_vlkup()
    
    Dim DestwsC     As Worksheet
    Dim DestwsP     As Worksheet
    Dim DestwbC     As Workbook
    Dim DestwbP     As Workbook
    Dim rng         As Range
    Dim DestwsC_lastRow As Long
    Dim CMS_start   As Long
    Dim CMS_end     As Long
    Dim DestwsP_lastCol As Long
    Dim i           As Long
    
    Set DestwbC = Workbooks.Open("\\shfs01\FShare1\CorpSvcs2\Finance\B2Cash\Credit Control- Reporting & Debt Recovery\Recovery\All DC Files\Collection & Invoices\2022 - All DC Collection\CMS - Aug 2022.xlsx")
    Set DestwsC = DestwbC.Sheets("Sheet1")
    
    Set DestwbP = Workbooks.Open("\\shfs01\FShare1\CorpSvcs2\Finance\B2Cash\Credit Control- Reporting & Debt Recovery\Recovery\All DC Files\Performance Rpting\DC collection Performance 2021.xlsx")
    Set DestwsP = DestwbP.Sheets("DC2")
    
    CMS_start = DestwsP.Range("B:B").Find(What:="CMS", After:=DestwsP.Range("B1")).Row
    CMS_end = DestwsP.Range("B:B").Find(What:="CMS", After:=DestwsP.Range("B1"), SearchDirection:=xlPrevious).Row
    
    DestwsC_lastRow = DestwsC.Cells(DestwsC.Rows.Count, "A").End(xlUp).Row
    
    Set rng = DestwsC.Range("A2:B" & DestwsC_lastRow)
    
    DestwsP_lastCol = DestwsP.Cells(2, Columns.Count).End(xlToLeft).Column + 1
    
    For i = CMS_start To CMS_end
        
        With Application
            
            If DestwsP.Range("D" & i) > DATE(YEAR(TEXT(TODAY(),""dd-Mmm-yy"")),MONTH(TEXT(TODAY()-DAY(TODAY()),""dd-Mmm-yy"")),1) Then '<--- got stuck at this portion
                
                DestwsP.Cells(i, DestwsP_lastCol).Value = .IfNa(.VLookup(DestwsP.Range("A" & i).Value, rng, 2, 0), 0)
                
            End With
            
        Next i
        
    End Sub

Appreciate the assistance in advance!

[#5]

@hrh_dash 

You're trying to use an Excel formula as VBA code. That won't work. You also forgot the End If belonging to the If ... Then.

            If DestwsP.Range("D" & i).Value > DateSerial(Year(Date), Month(Date) - 1, 1) Then

[#6]

thanks for the help!!