Forum Discussion

Iron Contributor

Sep 28, 2022

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,">"&'[D...

excel

Formulas and Functions

Macros and VBA

HansVogelaar

Sep 29, 2022

hrh_dash

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

hrh_dash

Iron Contributor

Sep 29, 2022

HansVogelaar , no worries. the code works perfectly.

is it possible to include a Sheets.count in the variable below? This is because the Sheet name changes on a monthly basis. Tried to swap Aug22 to Sheets.Count and it populates an error "run time error 1004 application defined or object defined error"

sSheet2 = "'\\shfs01\FShare1\CorpSvcs2\Finance\B2Cash\Credit Control- Reporting & Debt Recovery\Recovery\All DC Files\Performance Rpting\[DC1 & DC2 Performance - 2022.xlsx]'!" & Sheets.Count

HansVogelaar

MVP

Sep 29, 2022

hrh_dash

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

hrh_dash
Iron Contributor
Oct 07, 2022
, thank you for the help..

HansVogelaar

MVP

Oct 06, 2022

hrh_dash

The problem is right at the end:

    sFormula_CMS_MISC_TC = "=SUMIF(" & sSheet1 & "$C" & CMS_start & ":$C" & CMS_end & ",""<""&" & sSheet2 & cell_ref & "," & sSheet1 & "$V" & CMS_start & ":$V" & CMS_end & ")"

hrh_dash
Iron Contributor
Oct 06, 2022
HansVogelaar , really sorry to bother u again on this SUMIF formula..

I have got another SUMIF formula would like to seek your help.. Have been staring at it for a while and still have no clue whether am i missing a double quotes or am i having an extra double quotes..

My variables placement looks fine and are not double quotes and have checked that the commas are quoted.. I am not sure why there is still an error..

sFormula_CMS_MISC_TC = "=SUMIF(" & sSheet1 & "$C" & CMS_start & ":$C" & CMS_end & ",""<""&" & sSheet2 & cell_ref & "," & sSheet1 & "$V" & CMS_start & ":$V" & CMS_end)"
hrh_dash
Iron Contributor
Oct 06, 2022
thanks and really appreciate your help. I am seeing stars as well..

The format of the report is making it difficult to implement the code...

HansVogelaar

MVP

Oct 05, 2022

hrh_dash

You're really hell-bent on making it ever more complicated! The instruction doesn't even fit on one line anymore.

cell_ref should be outside the quotes, just like sSheet2, since it is a VBA variable.

    sFormula_CMS_MISC = "=SUMIFS(" & sSheet1 & "$V" & CMS_start & ":$V" & CMS_end & _
        "," & sSheet1 & "$C" & CMS_start & ":$C" & CMS_end & ","">""&" & sSheet2 & _
        cell_ref & "," & sSheet1 & "$C" & CMS_start & ":$C" & CMS_end & ",""<=""&" & _
        "EOMONTH(" & sSheet2 & cell_ref & ",0))"

hrh_dash

Iron Contributor

Oct 05, 2022

HansVogelaar , i would like to replace the specific cell reference to an Input box, therefore how should i amend or remove the double quotes?

so instead of the user to amend the cell reference on a monthly basis from the macro, the user would just enter which cells to take reference from based from the Input box

    Dim cell_ref As String
    cell_ref = InputBox("Please indicate which cell date to take reference from?")

sFormula_CMS_MISC = "=SUMIFS(" & sSheet1 & "$V" & CMS_start & ":$V" & CMS_end & "," & sSheet1 & "$C" & CMS_start & ":$C" & CMS_end & ","">""&" & sSheet2 & " cell_ref," & sSheet1 & "$C" & CMS_start & ":$C" & CMS_end & ",""<=""& EOMONTH(" & sSheet2 & "  cell_ref,0))

hrh_dash
Iron Contributor
Oct 01, 2022
HansVogelaar , have managed to solve the issue. thanks for the help anyway
HansVogelaar
MVP
Sep 30, 2022
hrh_dash

It looks OK, so I have no idea why it fails without seeing the workbook.
hrh_dash
Iron Contributor
Sep 30, 2022
HansVogelaar , i have another similar problem and would need your advice. This time round i would like to add eomonth into the formula..

Excel formula as follows:
SUMIFS('\\shfs01\FShare1\CorpSvcs2\Finance\B2Cash\Credit Control- Reporting & Debt Recovery\Recovery\All DC Files\Performance Rpting\[DC collection Performance 2021.xlsx]MISC'!$V$2:$V$61,

'\\shfs01\FShare1\CorpSvcs2\Finance\B2Cash\Credit Control- Reporting & Debt Recovery\Recovery\All DC Files\Performance Rpting\[DC collection Performance 2021.xlsx]MISC'!$C$2:$C$61,
">"&'\\shfs01\FShare1\CorpSvcs2\Finance\B2Cash\Credit Control- Reporting & Debt Recovery\Recovery\All DC Files\Performance Rpting\[DC collection Performance 2021.xlsx]Performance 2021'!$L$2,

'\\shfs01\FShare1\CorpSvcs2\Finance\B2Cash\Credit Control- Reporting & Debt Recovery\Recovery\All DC Files\Performance Rpting\[DC collection Performance 2021.xlsx]MISC'!$C$2:$C$61,

"<="&EOMONTH('\\shfs01\FShare1\CorpSvcs2\Finance\B2Cash\Credit Control- Reporting & Debt Recovery\Recovery\All DC Files\Performance Rpting\[DC collection Performance 2021.xlsx]Performance 2021'!$L$2,0))

code as follows:
sFormula_OA_CMS_MISC = "=SUMIFS(" & sSheet1 & "$V" & CMS_start & ":$V" & CMS_end & "," & sSheet1 & "$C" & CMS_start & ":$C" & CMS_end & ","">""&" & sSheet2 & "$L$2," & sSheet1 & "$C" & CMS_start & ":$C" & CMS_end & ",""<=""& EOMONTH(" & sSheet2 & "$L$2,0))"

i did a debug.print of the code to cross check with the excel formula, everything is the same but not sure why an error is populated. Not sure whether the placement is wrong or am i missing another parenthesis..
hrh_dash
Iron Contributor
Sep 29, 2022
HansVogelaar, thank you! the code works perfectly. I was thinking of using ws.name as well but the placement of
& "'!" was tricky and confusing. Thanks again for the help!