Forum Discussion

hrh_dash's avatar
hrh_dash
Iron Contributor
Sep 10, 2022
Solved

Macro to sumifs a range of values based on dates

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!

excel
Formulas and Functions
Macros and VBA
  • HansVogelaar's avatar
    HansVogelaar
    Sep 10, 2022

    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))"

     

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Sep 10, 2022

    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))"

     

      • hrh_dash's avatar
        hrh_dash
        Iron Contributor
        Sep 27, 2022

        HansVogelaar , 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!

         

Resources