How should i write the sumifs .formula vba so that the code runs smoothly

Iron Contributor

Jun 26, 2022

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"

HansVogelaar

MVP

Jun 26, 2022

hrh_dash

How about

    With ws.Range("M2")
        .NumberFormat = "#,##0.00"
        .Formula = "=SUMIFS($I:$I,$F:$F,"">60"",$G:$G, ""USD"")"
        .Value = .Value
    End With

    With ws.Range("M2")
        .NumberFormat = "#,##0.00"
        .Value = Evaluate("=SUMIFS($I:$I,$F:$F,"">60"",$G:$G, ""USD"")")
    End With

    With ws.Range("M2")
        .NumberFormat = "#,##0.00"
        .Value = Application.SumIfs(Range("I:I"), Range("F:F"), ">60", Range("G:G"), "USD")
    End With

hrh_dash
Iron Contributor
Jun 26, 2022
thanks for the help! code is working perfectly and it is also working perfectly for another line of code. Appreciate the help!

Forum Discussion

How should i write the sumifs .formula vba so that the code runs smoothly

Share

Resources