Filter and sum variable length columns

[Charles_]

Hello,

I am writing a VBA macros and have run into an issue.

I need to be able to filter and sum specific rows. For example, on column "W" I have "CASH1" & "CASH2" and need to:

1: filter all CASH1, sum values in column "F" and paste in cell "AD1"
2. Filter all CASH2, sum values in column "F" and past in cell "AD2"

So far, I have been trying with the below without much success

Activesheet.range("w:w").autofilter field:=1, criteria1:="CASH1"
Columns("F:F").select
Range(F1048576").activate
active cell.formular1c1 = "=subtotal()"

Thanks for you help!

 

[Fajitas]

I think you only need to get the totals... no macro is needed.

Try this simple formulas:
in Ad1: =SUMIF(W:W,"CASH1",F:F)
in Ad2: =SUMIF(W:W,"CASH2",F:F)

FaJitAS here.

 

[Charles_]

I think you only need to get the totals... no macro is needed.

Try this simple formulas:
in Ad1: =SUMIF(W:W,"CASH1",F:F)
in Ad2: =SUMIF(W:W,"CASH2",F:F)

FaJitAS here.

Thank you Fajitas,

I am getting an Excel error however, "formula incorrect", any ideas why?

also, I need to write it in te VBA as it is part of my macros.

thanks!!

 

[Fajitas]

Check your 'localization' ... this formula is in english.
In spanish (my native language) it reads: SUMAR.SI(...) the function NAME changes between languages.

ANY WAY... you should write function names for formulas in macros in english:

Try this:

Range("AD1").Formula = "=SUMIF(W:W,"CASH1",F:F)"
Range("AD2").Formula = "=SUMIF(W:W,"CASH2",F:F)"

This code should put the formulas you want in place.

 

[Charles_]

Hi Fajitas!

Thanks for your help, sorry I did not reply earlier!

Your suggestion helped me figure out a sollution!

Thanks!