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:

Appreciate the help in advance!

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\nDim ws As Worksheet\n\nSet ws = Sheet1\n\nprevdate = Format(DateAdd(\"M\", -1, Now), \"mmm-yy\")\n\n    With ws.Range(\"G3\")\n        .NumberFormat = \"#,##0.00\"\n        .Formula = \"=SUMIFS(B:B,A:A, & \" >= \" & DATE(YEAR(prevdate),MONTH(prevdate),1),A:A, & \" <= \" &EOMONTH(prevdate,0))\"\n        .Value = .Value\n    End With

Appreciate the help in advance!

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

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

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()\n    \n    Dim DestwsC     As Worksheet\n    Dim DestwsP     As Worksheet\n    Dim DestwbC     As Workbook\n    Dim DestwbP     As Workbook\n    Dim rng         As Range\n    Dim DestwsC_lastRow As Long\n    Dim CMS_start   As Long\n    Dim CMS_end     As Long\n    Dim DestwsP_lastCol As Long\n    Dim i           As Long\n    \n    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\")\n    Set DestwsC = DestwbC.Sheets(\"Sheet1\")\n    \n    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\")\n    Set DestwsP = DestwbP.Sheets(\"DC2\")\n    \n    CMS_start = DestwsP.Range(\"B:B\").Find(What:=\"CMS\", After:=DestwsP.Range(\"B1\")).Row\n    CMS_end = DestwsP.Range(\"B:B\").Find(What:=\"CMS\", After:=DestwsP.Range(\"B1\"), SearchDirection:=xlPrevious).Row\n    \n    DestwsC_lastRow = DestwsC.Cells(DestwsC.Rows.Count, \"A\").End(xlUp).Row\n    \n    Set rng = DestwsC.Range(\"A2:B\" & DestwsC_lastRow)\n    \n    DestwsP_lastCol = DestwsP.Cells(2, Columns.Count).End(xlToLeft).Column + 1\n    \n    For i = CMS_start To CMS_end\n        \n        With Application\n            \n            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\n                \n                DestwsP.Cells(i, DestwsP_lastCol).Value = .IfNa(.VLookup(DestwsP.Range(\"A\" & i).Value, rng, 2, 0), 0)\n                \n            End With\n            \n        Next i\n        \n    End Sub

Appreciate the assistance in advance!

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