Re: Macro to sumifs a range of values based on dates

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!