Can a macro capture this?

  • Thread starter Thread starter Sherry
  • Start date Start date
S

Sherry

Everyday I open a spreadsheet, turn on autofilter and filter out some things
and record the number of rows remaining in another spreadsheet. For example,
if Widget type looks like this:

Widget_Type
small
small
small
medium
small
large
small
medium
small

With autofilter I say I only want to see items that are "medium" and then in
the botom bottom left of my screen it'll say "2 of 9 records found." I know
I can record a macro of me doing this manually and then play it back. If I
add a pause I can stop the macro and manually make a note of the number of
records found. How can I capture that number and put it in a cell?
 
I don't know what I'm doing. I added this code to the macro I recorded and
it give me a
Run-time rrror '1004':
No cells were found.

Below is a piece of the macro - If I do Step Into the macro the line that
starts with the word Range is the one that's hilited.

Sub Sandra1()
Selection.AutoFilter Field:=8, Criteria1:="PVC_HRLY_STATS_FOUND"
Dim vRange As Range
Set vRange = ActiveSheet.AutoFilter.Range
vRows = vRange.Columns(1).SpecialCells(xlVisible).Count - 1
Range("Q1").Value = vRows ' ---> This will put the number
of records in cell "Q1"
Selection.AutoFilter Field:=8
Selection.AutoFilter Field:=3, Criteria1:="<>TWC*", Operator:=xlAnd
Selection.AutoFilter Field:=5, Criteria1:="<>"
End Sub


Can someone please help. You guys are great.
 
There's a lot of things I filter for so I wouldn't want to use an input box.
I tried the other solution posted but being a novice I messed it up.


Don Guillett said:
try
Sub filterem()
mycriteria = InputBox("Enter small,medium,or large", "medium")
Range("A17:a27").AutoFilter Field:=1, Criteria1:=mycriteria
x = Application.Subtotal(3, Range("a18:a27"))
Y = Application.CountA([a18:a27])
[a16] = x & " " & mycriteria & " of " & Y & " records found"
Range("a1:a27").AutoFilter Field:=1
End Sub


--
Don Guillett
SalesAid Software
(e-mail address removed)
Sherry said:
Everyday I open a spreadsheet, turn on autofilter and filter out some things
and record the number of rows remaining in another spreadsheet. For example,
if Widget type looks like this:

Widget_Type
small
small
small
medium
small
large
small
medium
small

With autofilter I say I only want to see items that are "medium" and
Click to expand...
then
in
the botom bottom left of my screen it'll say "2 of 9 records found." I know
I can record a macro of me doing this manually and then play it back. If I
add a pause I can stop the macro and manually make a note of the number of
records found. How can I capture that number and put it in a cell?
Click to expand...
Click to expand...
 
I have to do this for several columns and some are a combination of
filters - I would know where to begin to do that with a countf. Thanks for
the suggestion, though.
 
=SUMPRODUCT((RngA="medium")*(RngB="abc")*(RngC=2003)*(RngD="xyz")*(RngE))

If abc, xyz etc are text then they should be enclosed in quotes, but if the
criteria is numeric such as the 2003 then leave the quotes off.

All ranges need to be the same length, eg:-

=SUMPRODUCT((A1:A100="medium")*(B1:B100="abc")*(C1:C100="cde")*(D1:D100="defghij
")*(E1:E100))

This is the same as filtering on each of those columns and then SUMMING the
visible values in Col E and will give you the SUM of all values in Col E where
the other columns meet the filters. Leaving out the ColE range bit will give
you a COUNT of all records that meet the filters
 
How about a count of just the visible rows--no matter what you filter by.

Pick a column that always has data--no empty cells.

Then =subtotal(3,$a$3:$a$9999)

I like to put these kinds of formulas in Row 1, headers in Row 2 and data in
rows 3...

Take a look at =subtotal() in help. You'll find that you can do a bunch of
counts/sums/averages with it.
 
One more thing.

In some worksheets when I'm not sure I can pick a column that always has data,
I'll use an extra column and just fill it with 1's. Then I can count that one
to see how many records showed because of the filter.

Turns out it's nice for other things, too.
 
What I'm looking for is something that'll populate cells elsewhere in the
spreadsheet. I have a few hundred filters I have to do. The code below
(between the comments) was suggested by some other kind person. I believe if
I could get this to work it would save me a LOT of time and eliminate the
possibility of errors. Does anybody know what I'm doing wrong here?
---------------------------------

I don't know what I'm doing. I added this code to the macro I recorded and
it give me a
Run-time rrror '1004':
No cells were found.

Below is a piece of the macro - If I do Step Into the macro the line that
starts with the word Range is the one that's hilited.

Sub Sandra1()
Selection.AutoFilter Field:=8, Criteria1:="PVC_HRLY_STATS_FOUND"
'-------copy the number to Q1
Dim vRange As Range
Set vRange = ActiveSheet.AutoFilter.Range
vRows = vRange.Columns(1).SpecialCells(xlVisible).Count - 1
Range("Q1").Value = vRows ' ---> This will put the number
of records in cell "Q1"
'---------Go get next number, etc....
End Sub


Can someone please help. You guys are great.
 
Sherry,
Has it been established that you must autofilter?? Or, do you just need the
info? If you only need the info, the sumproduct solution seems to be what
you should use. Feel free to send me a SMALL workbook with COMPLETE
explanation of what you need, and why!

--
Don Guillett
SalesAid Software
(e-mail address removed)
Sherry said:
What I'm looking for is something that'll populate cells elsewhere in the
spreadsheet. I have a few hundred filters I have to do. The code below
(between the comments) was suggested by some other kind person. I believe if
I could get this to work it would save me a LOT of time and eliminate the
possibility of errors. Does anybody know what I'm doing wrong here?
---------------------------------

I don't know what I'm doing. I added this code to the macro I recorded and
it give me a
Run-time rrror '1004':
No cells were found.

Below is a piece of the macro - If I do Step Into the macro the line that
starts with the word Range is the one that's hilited.

Sub Sandra1()
Selection.AutoFilter Field:=8, Criteria1:="PVC_HRLY_STATS_FOUND"
'-------copy the number to Q1
Dim vRange As Range
Set vRange = ActiveSheet.AutoFilter.Range
vRows = vRange.Columns(1).SpecialCells(xlVisible).Count - 1
Range("Q1").Value = vRows ' ---> This will put the number
of records in cell "Q1"
'---------Go get next number, etc....
End Sub


Can someone please help. You guys are great.








Dave Peterson said:
One more thing.

In some worksheets when I'm not sure I can pick a column that always has data,
I'll use an extra column and just fill it with 1's. Then I can count
Click to expand...
that
one
to see how many records showed because of the filter.

Turns out it's nice for other things, too.
Click to expand...
I
know
Click to expand...
If
I
Click to expand...
number
Click to expand...
 
Problem solved with
=COUNTIF(INDIRECT("pvc!"&A2&":"&A2),B2)

Column Criteria Count
H PVC_HRLY_STATS_FOUND 8
H PVC_HRLY_STATS_NOT_FOUND 1
O PVC_HRLY_STATS_FOUND 8
O PVC_HRLY_STATS_NOT_FOUND 1
O OTHER_SUBSC_ENDPT 0
F <> 3
m <> 3
f = 7
c TWC* 3
a company a 5

============================================
Beer, Hell. I thought everyone knew, by now, that I only drink Wild Turkey
101.

Don Guillett
SalesAid Software
(e-mail address removed)
----- Original Message -----
From: "BRACKNEY, STEVE (ASI)" <[email protected]>
To: "Don Guillett" <[email protected]>
Sent: Thursday, January 08, 2004 2:17 PM
Subject: RE: Can a macro capture this?

I owe you a beer. This is great. Thank you so much!

-----Original Message-----
From: Don Guillett [mailto:[email protected]]
Sent: Thursday, January 08, 2004 02:07 PM
To: BRACKNEY, STEVE (ASI)
Subject: Re: Can a macro capture this?


Try this.
Click to expand...
 
I have a couple of guesses:

#1. Was the worksheet filtered before you ran the macro? If no, then maybe
the selection.autofilter line failed because you weren't close to the
real range.

#2. From your earlier post:

Below is a piece of the macro - If I do Step Into the macro the line that
starts with the word Range is the one that's hilited.

Are you using xl97? And are you calling the code from a control from the
controltoolbox toolbar (placed on the worksheet)?

If yes to both, then try changing its .takefocusonclick property to false.
(If it's a control from that toolbar that doesn't have that property,
try adding:

activecell.activate

to the top of your routine (a bug fixed in xl2k).)

I'm guessing #2 (but keeping #1 just in case).
 
I saw your post, but didn't understand that "problem solved" was in the mind of
the OP. I just thought you were adding it for emphasis!

I didn't scroll down to read the OP's message back to you.

(Peo, if you read this, stop laughing!)



Don said:
Dave,
See my post. OP sent me a wb and I sent a solution. OP happy camper.
Click to expand...
 
Back
Top