Forum Discussion
hrh_dash
Sep 10, 2022Iron Contributor
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 fo...
- Sep 10, 2022
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
Sep 10, 2022MVP
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_dashSep 10, 2022Iron Contributor, thanks for the assist!
- hrh_dashSep 27, 2022Iron Contributor
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!
- HansVogelaarSep 27, 2022MVP
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