VBA Calculation for filtered data

[Jan]

Using Excel 97.

I run a macro to put the header on a worksheet. Prior to the header, VBA
code is run to filter a list. The problem I am having with the formulla is
that it counts all records, not the records based on the filter that had been
set. How can I revise the formula to return the count the number of records
based on the filtered data & specific criteria. The formulas are under the
CSRTitle() below but the 2 in question are:

"Projects: At Printer=" & Application.CountIf(Range("U3:U5000"), 3) & _
" • Completed Last 30 Days=" &
Application.CountIf(Range("r3:r5000"), "Completed")




****************
Sub CSRFilter()
'
' CSRFilter Macro
'
ActiveWindow.ScrollColumn = 18
Selection.AutoFilter Field:=21, Criteria1:="<>4", Operator:=xlAnd
End Sub
***************************************
Sub CSRTitle()
'With ActiveSheet.PageSetup
' .PrintTitleRows = "$2:$2"
' .PrintTitleColumns = ""
' End With
With ActiveSheet.PageSetup
.LeftHeader = "&""Cushing Book/Bold,Bold""&T"
.CenterHeader = _
"&""Cushing Book/Bold,Bold""&16Perpetual Art Status Report (PAS) by
Label Number" & _
Chr(10) & "Total Projects in List = " & Application.Subtotal(3,
Range("c3:c5000")) _
& Chr(10) & "Projects: At Printer=" &
Application.CountIf(Range("U3:U5000"), 3) & _
" • Completed Last 30 Days=" &
Application.CountIf(Range("r3:r5000"), "Completed")
.RightHeader = "&""Cushing Book/Bold,Bold Italic""Printed: &D &14 "
.LeftFooter = "&A"
.CenterFooter = "&""Cushing Book/Bold,Bold""Chase Products
Confidential"
.RightFooter = "Page &P of &N"
.PrintGridlines = True
.CenterHorizontally = True
.CenterVertically = False
.Orientation = xlPortrait
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = False
End With
End Sub

TIA
Jan

 

[Tom Ogilvy]

Countif pays no attention to the filter (as you have discovered).

It seems to me, it would be easiest to apply filters to additional columns
and then use subtotal. Store the results in a variable, unfilter on
that/those additional columns (repeat for other subsets). Then use those
variables in your headers.

alternately, you could use Evaluate and set up a sumproduct formula to
calculate on all conditions including the filter condition.

 

[Jan]

Tom,

Thank you for your suggestions, but as someone who is not that familiar with
VBA, I don't have a clue where to begin to change the formula based on your
suggestions. This is a project that will be done weekly (per "boss" request)
to have 2 separate reports from one list going to 2 separate departments with
different columns displayed depending on which department. I wanted to
create macro(s) to automate the process. There are additional macros not
discussed in this message that perform what is needed for each report, except
for this current issue with the formulas for the header.

I don't want to use the subtotal function from the menu because my boss
wants a continous list.

Jan

 

[Tom Ogilvy]

I didn't say use the subtotal from the Data list. I said use the subtotal
worksheet function same as you are already doing:

"Total Projects in List = " & Application.Subtotal(3, Range("c3:c5000"))

You would put additional filters on your data so only the rows you want to
sum or count would be visible. Get your sum or count, then remove the
additional filters to place it back at the level of detail for your report.

 

[Jan]

Duh! Thank you.

I didn't say use the subtotal from the Data list. I said use the subtotal
worksheet function same as you are already doing:

"Total Projects in List = " & Application.Subtotal(3, Range("c3:c5000"))

You would put additional filters on your data so only the rows you want to
sum or count would be visible. Get your sum or count, then remove the
additional filters to place it back at the level of detail for your report.