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

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))

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 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...