Convert Excel Formula to VBA code

Question

Could you please advise how to convert a formula to VBA code

=SUMPRODUCT(SUBTOTAL(3,OFFSET((AT2:AT644),ROW(AT2:AT644)-MIN(ROW(AT2:AT644)), ,1)) * (AT2:AT644="6"))

i do like this, but question how to use "offset" in current code?

Application.WorksheetFunction.SumProduct(Application.WorksheetFunction.Subtotal(3, Application.Range.Offset(("AT2:AT644"), Application.Rows("AT2:AT644") - Application.WorksheetFunction.Min(Application.Rows("AT2:AT644")), , 1)) * ("AT2:AT644" = "6"))

Thanks,
Regards,
Lure

Tim Williams · Accepted Answer · 2016-05-25 18:33:51Z

1

Migrated from comment...

Debug.Print Sheet1.Evaluate("SUMPRODUCT(SUBTOTAL(3,OFFSET((AT2:AT644),ROW(AT2:AT644)-MIN(ROW‌(AT2:AT644)), ,1)) * (AT2:AT644=""6""))")

or whichever sheet you need to use, just don't use Application.Evaluate unless you want it to always evaluate in the context of the ActiveSheet.

answered May 25, 2016 at 18:33

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Convert Excel Formula to VBA code

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related