Forum Discussion
Solved
SUMIFs macro with specific find values
How should the code below be amended so that the SUMIFS macro would populate the correct values? Currently the value populated is 0
Excel formula as follows:
SUMIFS($F$2:$F$74,$C$2:$C$74,">"&'[DC1 & DC2 Performance - 2022.xlsx]Aug22'!$B$5,$C$2:$C$74,"<="&'[DC1 & DC2 Performance - 2022.xlsx]Aug22'!$D$5)
Sub calTotal()
Dim DestwsR As Worksheet 'DC1 & DC2 Performance 2022
Dim DestwbR As Workbook 'DC1 & DC2 Performance 2022
Dim DestwsP As Worksheet 'DC collection performance
Dim DestwbP As Workbook 'DC collection performance
Dim CMS_start As Long
Dim CMS_end As Long
Set DestwbR = Workbooks.Open("\\shfs01\FShare1\CorpSvcs2\Finance\B2Cash\Credit Control- Reporting & Debt Recovery\Recovery\All DC Files\Performance Rpting\DC1 & DC2 Performance - 2022.xlsx")
Set DestwsR = DestwbR.Sheets(Sheets.Count)
Set DestwbP = Workbooks.Open("C:\Users\hrhquek\desktop\DC collection Performance 2021.xlsx")
Set DestwsP = DestwbP.Sheets("DC1")
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
DestwsR.Range("I7") = WorksheetFunction.SumIfs(DestwsP.Range("F" & CMS_start & ":F" & CMS_end), DestwsP.Range("C" & CMS_start & ":C" & CMS_end), "" > "" & DestwsR.Range("B5"), DestwsP.Range("C" & CMS_start & ":C" & CMS_end), "" <= "" & DestwsR.Range("D5"))
End Sub
appreciate the help in advance!
Try this:
Sub calTotal() Dim DestwsR As Worksheet 'DC1 & DC2 Performance 2022 Dim DestwbR As Workbook 'DC1 & DC2 Performance 2022 Dim DestwsP As Worksheet 'DC collection performance Dim DestwbP As Workbook 'DC collection performance Dim CMS_start As Long Dim CMS_end As Long Dim SheetName As String Dim sSheet1 As String Dim sSheet2 As String Dim sFormula As String Set DestwbR = Workbooks.Open("\\shfs01\FShare1\CorpSvcs2\Finance\B2Cash\Credit Control- Reporting & Debt Recovery\Recovery\All DC Files\Performance Rpting\DC1 & DC2 Performance - 2022.xlsx") Set DestwsR = DestwbR.Sheets(Sheets.Count) SheetName = DestwsR.Name Set DestwbP = Workbooks.Open("C:\Users\hrhquek\desktop\DC collection Performance 2021.xlsx") Set DestwsP = DestwbP.Sheets("DC1") 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 sSheet1 = "'\\shfs01\FShare1\CorpSvcs2\Finance\B2Cash\Credit Control- Reporting & Debt Recovery\Recovery\All DC Files\Performance Rpting\[DC collection Performance 2021.xlsx]DC1'!" sSheet2 = "'\\shfs01\FShare1\CorpSvcs2\Finance\B2Cash\Credit Control- Reporting & Debt Recovery\Recovery\All DC Files\Performance Rpting\[DC1 & DC2 Performance - 2022.xlsx]" & SheetName & "'!" sFormula = "=SUMIFS(" & sSheet1 & "$F" & CMS_start & ":$F" & CMS_end & "," & sSheet1 & "$C" & CMS_start & ":$C" & CMS_end & ","">""&" & sSheet2 & "$B$5," & sSheet1 & "$C" & CMS_start & ":$C" & CMS_end & ",""<=""&" & sSheet2 & "$D$5)" With DestwsR.Range("I7") .Formula = sFormula .Value = .Value End With End Sub
Does this work?
DestwsR.Range("I7") = Evaluate("SUMIFS($F$" & CMS_start & ":$F$" & CMS_end & ",$C$" & CMS_start & ":$C$" & CMS_end & ","">""&'[DC1 & DC2 Performance - 2022.xlsx]Aug22'!$B$5,$C$" & CMS_start & ":$C$" & CMS_end & ",""<=""&'[DC1 & DC2 Performance - 2022.xlsx]Aug22'!$D$5)")- , I think my excel formula isn’t clear. Am sorry for that. I am actually running the code on a separate workbook. Which means there are total 3 workbooks involved. DestwbR and DestwbP will not be containing the macro due to them being saved in the shared drive thus I would execute the macro from my personal workbook saved on my desktop. The evaluate macro would work but my filepath is too long..
And this?
With DestwsR.Range("I7") .Formula = "=SUMIFS($F$" & CMS_start & ":$F$" & CMS_end & ",$C$" & CMS_start & ":$C$" & CMS_end & ","">""&'[DC1 & DC2 Performance - 2022.xlsx]Aug22'!$B$5,$C$" & CMS_start & ":$C$" & CMS_end & ",""<=""&'[DC1 & DC2 Performance - 2022.xlsx]Aug22'!$D$5)" .Value = .Value End With
