Jun 24 2022 07:11 AM
How should i write the SUMIFS macro code by using the .Formula method so that the SUMIFS will execute correctly?
With ws.Range("M2")
.Formula = "=SUMIFS('C:\Users\hrhquek\Desktop\[calculate aging days macro.XLSM]Sheet2'!$I2:$I," _
,'C:\Users\hrhquek\Desktop\[calculate aging days macro.XLSM]Sheet2'!$F2:$F,"">60"" _
,'C:\Users\hrhquek\Desktop\[calculate aging days macro.XLSM]Sheet2'!$G2:$G, ""USD"")"
.Value = .Value
End With
thanks in advance and appreciate the help!
Jun 24 2022 08:51 AM
For example:
With ws.Range("M2")
.Formula = "=SUMIFS('C:\Users\hrhquek\Desktop\[calculate aging days macro.XLSM]Sheet2'!I2:I1000," & _
"'C:\Users\hrhquek\Desktop\[calculate aging days macro.XLSM]Sheet2'!F2:F1000,"">60""," & _
"'C:\Users\hrhquek\Desktop\[calculate aging days macro.XLSM]Sheet2'!G2:G1000,""USD"")"
.Value = .Value
End With
Jun 24 2022 07:25 PM - edited Jun 24 2022 07:26 PM
@HansVogelaar , thanks and appreciate for helping out again. The code is populating an error message:
Run-time error '1004':
Application-defined or object-defined error
Excel formula would be: SUMIFS($I:$I,$F:$F,"">60"",$G:$G, ""USD"")
Jun 25 2022 04:03 AM
The code should work, unless you misspelled the file path or sheet name. I tested it.
Jun 25 2022 10:40 PM
@HansVogelaar , i tried to remove the filepath as the code is running the workbook that is containing the code., there is no error fortunately but there is no data populating in the sheet. I tried replacing it with a range; ie I2:I10000; no data is populating as well.
With ws.Range("M2")
.Formula = "=SUMIFS('I:I," & "'F:F,"">60""," & "'G:G,""USD"")"
.Value = .Value
End With
Currently i am using this code but it is showing the formulas which i do not want to.
ws.Range("M2").Formula = "=SUMIFS($I:$I,$F:$F,"">60"",$G:$G, ""USD"")"
ws.Range("M2").NumberFormat = "#,##0.00"
Jun 26 2022 03:09 AM
SolutionHow about
With ws.Range("M2")
.NumberFormat = "#,##0.00"
.Formula = "=SUMIFS($I:$I,$F:$F,"">60"",$G:$G, ""USD"")"
.Value = .Value
End With
or
With ws.Range("M2")
.NumberFormat = "#,##0.00"
.Value = Evaluate("=SUMIFS($I:$I,$F:$F,"">60"",$G:$G, ""USD"")")
End With
or
With ws.Range("M2")
.NumberFormat = "#,##0.00"
.Value = Application.SumIfs(Range("I:I"), Range("F:F"), ">60", Range("G:G"), "USD")
End With
Jun 26 2022 04:38 AM
Jun 26 2022 03:09 AM
SolutionHow about
With ws.Range("M2")
.NumberFormat = "#,##0.00"
.Formula = "=SUMIFS($I:$I,$F:$F,"">60"",$G:$G, ""USD"")"
.Value = .Value
End With
or
With ws.Range("M2")
.NumberFormat = "#,##0.00"
.Value = Evaluate("=SUMIFS($I:$I,$F:$F,"">60"",$G:$G, ""USD"")")
End With
or
With ws.Range("M2")
.NumberFormat = "#,##0.00"
.Value = Application.SumIfs(Range("I:I"), Range("F:F"), ">60", Range("G:G"), "USD")
End With