Adv Filter fails in macro, works manually

[--elizabeth]

I created a form with comboboxes and textboxes so the user could select
search criteria. The selections are written to the criteria range of a
worksheet. Underneath the criteria range, on the same worksheet, is the
extract range. Both ranges include column headers. The code does not give any
run-time errors, but neither does it populate the extract range with any data.

If I hardcode the criteria--i.e., bypass the form and enter the criteria
directly on the spreadsheet--the extract range is populated with all the
data, not just the ones with criteria entered.

I thought perhaps the empty form comboboxes were being transferred to the
spreadsheet as empty variants, so I used a loop to set all empty cells to "".
No Go.

I copied the database field names to the extract range, so I don't think
there is a problem with the field names. There is a calculate field in the
criteria range for both dates and values, but I haven't done anything with
those yet.

I know I'm missing something basic here, but I can't figure out what. The
relevant code is below.

Any help is much appreciated. The relevant code is below. If you need me to
upload a test file, please let me know. Thanks.

--elizabeth

 

[Barb Reinhardt]

Please post your code.

Barb Reinhardt

 

[JLatham]

The OP said
"I know I'm missing something basic here, but I can't figure out what. The
relevant code is below."

So either --elizabeth is as forgetful as I am quite often, or there is no
relevant code. I'm betting on the CRS diagnosis, myself. <g>

 

[--elizabeth]

Sorry. Thought I had. Here it is (I hope):
--elizabeth

Private Sub cmdSearch_Click()
Dim rgDB As Range
Dim rgCriteria As Range
Dim rgExtract As Range

Set rgDB = Range("Database")
Set rgCriteria = Range("Criteria")
Set rgExtract = Range("Extract")

WriteValues2CritRng

rgDB.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=rgCriteria, _
CopyToRange:=rgExtract
End Sub

Private Sub WriteValues2CritRng()
Dim iRow, iCol As Integer
Dim rngCell As Range
With Worksheets("Search Criteria")
'columns 4 and 8 (offsets 3 and 7) are calculated fields
'first row of criteria
Range("$A$3").Activate
ActiveCell = cboMaker1
With ActiveCell
.Offset(0, 1) = txtBeginYear1
.Offset(0, 2) = txtEndYear1
.Offset(0, 4) = cboSmoked1
.Offset(0, 5) = txtMinValue1
.Offset(0, 6) = txtMaxValue1
.Offset(0, 8) = cboStyle1
.Offset(0, 9) = cboBowlFinish1
.Offset(0, 10) = cboGrain1
.Offset(0, 11) = cboStemMaterial1
.Offset(0, 12) = cboOriginalStem1
.Offset(0, 13) = cboMakerMark1
.Offset(0, 14) = cboBoxCase1
.Offset(0, 15) = cboCondition1
End With
'second row of criteria
Range("$A$4").Activate
ActiveCell = cboMaker2
With ActiveCell
.Offset(0, 1) = txtBeginYear2
.Offset(0, 2) = txtEndYear2
.Offset(0, 4) = cboSmoked2
.Offset(0, 5) = txtMinValue2
.Offset(0, 6) = txtMaxValue2
.Offset(0, 8) = cboStyle2
.Offset(0, 9) = cboBowlFinish2
.Offset(0, 10) = cboGrain2
.Offset(0, 11) = cboStemMaterial2
.Offset(0, 12) = cboOriginalStem2
.Offset(0, 11) = cboMakerMark2
.Offset(0, 14) = cboBoxCase2
.Offset(0, 15) = cboCondition2
End With
'third row of criteria
Range("$A$5").Activate
ActiveCell = cboMaker3
With ActiveCell
.Offset(0, 1) = txtBeginYear3
.Offset(0, 2) = txtEndYear3
.Offset(0, 4) = cboSmoked3
.Offset(0, 5) = txtMinValue3
.Offset(0, 6) = txtMaxValue3
.Offset(0, 8) = cboStyle3
.Offset(0, 9) = cboBowlFinish3
.Offset(0, 10) = cboGrain3
.Offset(0, 11) = cboStemMaterial3
.Offset(0, 12) = cboOriginalStem3
.Offset(0, 11) = cboMakerMark3
.Offset(0, 14) = cboBoxCase3
.Offset(0, 15) = cboCondition3
End With
End With
With Range("Criteria")
For iRow = 3 To 5
For iCol = 1 To 16
Set rngCell = Cells(iRow, iCol)
If IsEmpty(rngCell) Then
rngCell = ""
End If
Next iCol
Next iRow
End With
End Sub

 

[ozgrid.com]

You should clear "rgExtract" before applying AdvancedFilter.

 

[Dave Peterson]

Even though you used a with/end with construct, you didn't really use it
(huh??).

You have a few portions of code that look like:

With Worksheets("Search Criteria")
'columns 4 and 8 (offsets 3 and 7) are calculated fields
'first row of criteria
Range("$A$3").Activate

But Range("$A$3") doesn't necessarily refer to the "search criteria" worksheet.
Since you didn't qualify it, it refers to the activesheet.

You could use:

With Worksheets("Search Criteria")

.Select 'make that worksheet active first

'columns 4 and 8 (offsets 3 and 7) are calculated fields
'first row of criteria
Range("$A$3").Activate

But this can still be a problem (in general). Depending on where the code is,
it may refer to a different sheet. (I think this is a bad solution that could
cause damage if used in other code.)

Instead, you could use that with/end with structure and even drop the
selections:


Private Sub cmdSearch_Click()
Dim rgDB As Range
Dim rgCriteria As Range
Dim rgExtract As Range

Set rgDB = Range("Database")
Set rgCriteria = Range("Criteria")
Set rgExtract = Range("Extract")

WriteValues2CritRng

rgDB.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=rgCriteria, _
CopyToRange:=rgExtract
End Sub

Private Sub WriteValues2CritRng()
Dim iRow, iCol As Integer
Dim rngCell As Range
With Worksheets("Search Criteria")
'columns 4 and 8 (offsets 3 and 7) are calculated fields
'first row of criteria
With .Range("A3") 'You don't need the $ here
.Value = cboMaker1
.Offset(0, 1) = txtBeginYear1
.Offset(0, 2) = txtEndYear1
.Offset(0, 4) = cboSmoked1
.Offset(0, 5) = txtMinValue1
.Offset(0, 6) = txtMaxValue1
.Offset(0, 8) = cboStyle1
.Offset(0, 9) = cboBowlFinish1
.Offset(0, 10) = cboGrain1
.Offset(0, 11) = cboStemMaterial1
.Offset(0, 12) = cboOriginalStem1
.Offset(0, 13) = cboMakerMark1
.Offset(0, 14) = cboBoxCase1
.Offset(0, 15) = cboCondition1
End With
'second row of criteria
With .Range("A4")
.Value = cboMaker2
.Offset(0, 1) = txtBeginYear2
.Offset(0, 2) = txtEndYear2
.Offset(0, 4) = cboSmoked2
.Offset(0, 5) = txtMinValue2
.Offset(0, 6) = txtMaxValue2
.Offset(0, 8) = cboStyle2
.Offset(0, 9) = cboBowlFinish2
.Offset(0, 10) = cboGrain2
.Offset(0, 11) = cboStemMaterial2
.Offset(0, 12) = cboOriginalStem2
.Offset(0, 11) = cboMakerMark2
.Offset(0, 14) = cboBoxCase2
.Offset(0, 15) = cboCondition2
End With
'third row of criteria
With .Range("A5")
.Value = cboMaker3.Value
.Offset(0, 1) = txtBeginYear3
.Offset(0, 2) = txtEndYear3
.Offset(0, 4) = cboSmoked3
.Offset(0, 5) = txtMinValue3
.Offset(0, 6) = txtMaxValue3
.Offset(0, 8) = cboStyle3
.Offset(0, 9) = cboBowlFinish3
.Offset(0, 10) = cboGrain3
.Offset(0, 11) = cboStemMaterial3
.Offset(0, 12) = cboOriginalStem3
.Offset(0, 11) = cboMakerMark3
.Offset(0, 14) = cboBoxCase3
.Offset(0, 15) = cboCondition3
End With
End With
With Range("Criteria")
For iRow = 3 To 5
For iCol = 1 To 16
'this needs to be qualified, too!
Set rngCell = .Cells(iRow, iCol)
If IsEmpty(rngCell) Then
rngCell = ""
End If
Next iCol
Next iRow
End With
End Sub

========
All untested and uncompiled.

If this doesn't help, you may want to include the values in the combobox and
what you're filtering on.

I know that I've filtered to show a value and all the rows that start with that
value show up--not just the rows that equal that value.

Debra Dalgleish shares some sample code that creates the correct criteria string
here:

Code from Debra Dalgleish's site:
http://www.contextures.com/excelfiles.html
Look for:
Update Sheets from Master -- uses an Advanced Filter to send data from
Master sheet to individual worksheets -- replaces old data with current.
AdvFilterCity.xls 55 kb

It's this line:
TempWks.Range("D2").Value = "=" & Chr(34) & "=" & myCell.Value & Chr(34)

It builds a string that looks like a formula and that provides exact matches.

 

[--elizabeth]

Thanks, everyone, for your help.

I won't be able to try any of your suggestions until later this afternoon,
as I have to go into San Francisco for an appointment. In fact, it may be
tomorrow before I can get back to you, as we have company from Spain (couch
surfers!).

I am very pleasanty surprised by the response from this forum. It is much
appreciated.

--elizabeth

 

[--elizabeth]

Okay, took a look at Debra's code. Very interesting, but I don't see how it
would apply to my filter-by-form code. Am I just really dense?
--elizabeth

 

[ozgrid.com]

Show me the code and state what the problem is.

 

[Dave Peterson]

The sample from Debra's code was a specific fix for filtering for exact values.
It had nothing to do with the problem you have.

Did you try the suggested code?

Did it work?

How did it fail?

And a question from the previous post:

If this doesn't help, you may want to include the values in the combobox and
what you're filtering on.

 

[Dave Peterson]

Me???

Nope, I can't post them.

When you do advance filter manually how do you select the ranges.

Your code has the following objects

Set rgDB = Range("Database")
Set rgCriteria = Range("Criteria")
Set rgExtract = Range("Extract")

These are named ranges. Can you post the adress ranges of the 3
objects above by getting the referto address of the objects.

--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=192879

http://www.thecodecage.com/forumz

 

[--elizabeth]

Show me the code and state what the problem is.

Dave, I have a userform which gets the criteria from the user via comboboxes.

The WriteValues2CritRng function copies the combobox values from the form to
the criteria range of the Search Criteria worksheet.

In the cmdSearch event below, I have defined the variables for the range,
criteria, and extract ranges; the range addresses are in the comments to the
right of the Set range statements. Both the criteria and the extract range
have column headers. The extract headers are copied from the database
headers. The criteria headers are the same with the addition of two
calculated fields for date range and price range.

There is a cmdNew event/button that clears the criteria and extract ranges
before a new search.

The problem is that the advancedfilter does not extract anything.

Regards,
--elizabeth

Private Sub cmdSearch_Click()
Dim rgDB As Range
Dim rgCriteria As Range
Dim rgExtract As Range

Set rgDB = Range("Database") 'Inventory'!1:655356
Set rgCriteria = Range("Criteria") 'Search Criteria'!A25
Set rgExtract = Range("Extract") 'Search Criteria'!A9:M65536

WriteValues2CritRng

rgDB.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=rgCriteria, _
CopyToRange:=rgExtract
End Sub

Private Sub WriteValues2CritRng()
Dim iRow, iCol As Integer
Dim rngCell As Range
With Worksheets("Search Criteria")
'columns 4 and 8 (offsets 3 and 7) are calculated fields
'first row of criteria
With .Range("A3")
.VALUE = cboMaker1
.Offset(0, 1) = txtBeginYear1
.Offset(0, 2) = txtEndYear1
.Offset(0, 4) = cboSmoked1
.Offset(0, 5) = txtMinValue1
.Offset(0, 6) = txtMaxValue1
.Offset(0, 8) = cboStyle1
.Offset(0, 9) = cboBowlFinish1
.Offset(0, 10) = cboGrain1
.Offset(0, 11) = cboStemMaterial1
.Offset(0, 12) = cboOriginalStem1
.Offset(0, 13) = cboMakerMark1
.Offset(0, 14) = cboBoxCase1
.Offset(0, 15) = cboCondition1
End With
'second row of criteria
With .Range("A4")
.VALUE = cboMaker2
.Offset(0, 1) = txtBeginYear2
.Offset(0, 2) = txtEndYear2
.Offset(0, 4) = cboSmoked2
.Offset(0, 5) = txtMinValue2
.Offset(0, 6) = txtMaxValue2
.Offset(0, 8) = cboStyle2
.Offset(0, 9) = cboBowlFinish2
.Offset(0, 10) = cboGrain2
.Offset(0, 11) = cboStemMaterial2
.Offset(0, 12) = cboOriginalStem2
.Offset(0, 11) = cboMakerMark2
.Offset(0, 14) = cboBoxCase2
.Offset(0, 15) = cboCondition2
End With
'third row of criteria
With .Range("A5")
.VALUE = cboMaker3
.Offset(0, 1) = txtBeginYear3
.Offset(0, 2) = txtEndYear3
.Offset(0, 4) = cboSmoked3
.Offset(0, 5) = txtMinValue3
.Offset(0, 6) = txtMaxValue3
.Offset(0, 8) = cboStyle3
.Offset(0, 9) = cboBowlFinish3
.Offset(0, 10) = cboGrain3
.Offset(0, 11) = cboStemMaterial3
.Offset(0, 12) = cboOriginalStem3
.Offset(0, 11) = cboMakerMark3
.Offset(0, 14) = cboBoxCase3
.Offset(0, 15) = cboCondition3
End With
End With
With Range("Criteria")
For iRow = 3 To 5
For iCol = 1 To 16
Set rngCell = Cells(iRow, iCol)
If IsEmpty(rngCell) Then
rngCell = ""
End If
Next iCol
Next iRow
End With
End Sub

Private Sub cmdNew_Click()
Dim iRow, iCol As Integer
Dim ctl As Control
For Each ctl In Me.Controls
If TypeName(ctl) = "TextBox" Or TypeName(ctl) = "ComboBox" Then
ctl.VALUE = vbNullString
End If
Next ctl

Worksheets("Search Criteria").Activate
With Worksheets("Search Criteria")
For iRow = 3 To 5
For iCol = 1 To 14
If Not (iCol = 4 Or iCol = 8) Then
Cells(iRow, iCol) = ""
End If
Next iCol
Next iRow
End With
Range("ExtractRows").Clear
End Sub

Private Sub UserForm_Initialize()
'initialize all controls to vbNullString
Dim ctl As Control
For Each ctl In Me.Controls
Select Case TypeName(ctl)
Case "TextBox"
ctl = vbNullString
Case "ComboBox"
ctl = vbNullString
Case "ListBox"
ctl = vbNullString
End Select
Next ctl
Worksheets("Search Criteria").Activate
End Sub

 

[--elizabeth]

The sample from Debra's code was a specific fix for filtering for exact values.
It had nothing to do with the problem you have.

Did you try the suggested code?
Did it work?
How did it fail?

Are you referring to Debra's code here? or to your code suggestions? I
incorporated your suggestions (much appreciated, thank you) with no change in
the results. I did not try Debra's code, although I looked at it closely so I
could understand it. Since I am using a form, I did not try it in my
application.

And a question from the previous post:

If this doesn't help, you may want to include the values in the combobox and
what you're filtering on.

(I responded to this in a previous post and I don't know how to respond
otherwise, so I'll copy my previous response here.)

I'm not sure what you mean by the above. The form has comboboxes that are
fed by data validation lists. The function WriteValues2CritRng writes the
combobox values to the criteria range.

Can you explain further, please?

Regards,
--elizabeth

 

[--elizabeth]

When you do advance filter manually how do you select the ranges.

Joel, it turns out that the advancefilter is not working manually now, either.

These are named ranges. Can you post the adress ranges of the 3
objects above by getting the referto address of the objects.

I'm not sure what you mean by the referto address. If I enter a watch for
the range variable addresses the following values are shown:

Set rgDB = Worksheets("Inventory").Range("Database")
$1:$655356
Set rgCriteria = Worksheets("Search Criteria").Range("Criteria")
$A$2:$P$5
Set rgExtract = Worksheets("Search Criteria").Range("Extract")
$A$9:$M$210

The first row in rgCriteria and rgExtract are column headers, copied from
the Database column headers. rgCriteria has two extra columns for calculated
fields.

Regards,
--elizabeth

 

[Dave Peterson]

I meant the code I suggested.

But I haven't seen anything that describes how the macro version of the advanced
filter fails -- you just say that it doesn't work.

So in order to test it, I'd want to know what the data looks like, what is in
the userform when the advanced filter fails and what fail really means.

 

[--elizabeth]

But I haven't seen anything that describes how the macro version of the
advanced

filter fails -- you just say that it doesn't work.

Dave, I don't know how it fails. I wish I did. Maybe I could figure it out
then. There are no compile or run-time errors. Everything works fine except
the actual line of code for advancedfilter does nothing.

So in order to test it, I'd want to know what the data looks like, what is in
the userform when the advanced filter fails and what fail really means.

The pipe database has the following fields, all formatted as text, and I've
included sample data for three pipes (made up when I don't yet have any from
my friend).
The field names below are the database column headers.

The database is on Worksheet "Inventory" and the search and extract ranges
are on worksheet "Search Criteria").

Pipe ID 1, 2, 3, etc.
Maker Baldo Baldi, Radice, Fiamma di Re
Year 1924, 1952, 1878
Smoked No, Yes, No
Value $600, $250, $1200
Style Apple, Bent, Globe
Bowl Finish Tan Shell, Blast, Finish 3
Grain Grain 1, Grain 2, Grain 3
Stem Material Bakelite, Amber, Stem 3
Stem Original Yes, No, No
Maker Mark Good, Fair, Excellent
Box/Case No, Yes
Condition Box/Case (if Box/Case = Yes)
Good, Poor, Fair

The userform has comboboxes for the above fields, which are all fed by data
validation lists, and a couple of command buttons to run the search
(cmdSearch), clear the search criteria and extract range (cmdNew). There are
three rows of comboboxes (for three sets of criteria)

So far, I've only been testing the application with one criteria in the
first criteria row, and that has been the Maker field, generally, just
because it's the first field in the row.

The extract range column headers are the same as the inventory column
headers. The criteria range is also the same except instead for the following:

Start Year, End Year, and a calculated field Year [=AND(Inventory!Year>=B3,
Inventory!Year <=C3)]
Start Value, End Value and a calculated field Value
[=AND(Inventory!Value>=F3, Inventory!Value <=G3)]

The calculated fields have the same field name as the fields Year and Value
in the database and the extract range.

Let me know if you need anything else. I have a small test file I can
upload, as well, if you wish. Just let me know where and how.

Thanks,
--elizabeth

 

[Dave Peterson]

Instead of me taking the time to set up the test workbook -- and probably not
coming close, how about replacing any proprietary/top secret info with dummy
data. (Do this against a copy of the workbook!)

Either add some code to populate the userform -- or add a sheet that describes
what should be entered.

Then zip the file and send it to me directly.

Remove the "xspam" from my email address.

Alternatively, you could do the same thing and post it to one of the
file-sharing web sites and get lots of eyeballs to review it.

Let us know what you're going to do.

But I haven't seen anything that describes how the macro version of the advanced
filter fails -- you just say that it doesn't work.

Dave, I don't know how it fails. I wish I did. Maybe I could figure it out
then. There are no compile or run-time errors. Everything works fine except
the actual line of code for advancedfilter does nothing.

So in order to test it, I'd want to know what the data looks like, what is in
the userform when the advanced filter fails and what fail really means.

The pipe database has the following fields, all formatted as text, and I've
included sample data for three pipes (made up when I don't yet have any from
my friend).
The field names below are the database column headers.

The database is on Worksheet "Inventory" and the search and extract ranges
are on worksheet "Search Criteria").

Pipe ID 1, 2, 3, etc.
Maker Baldo Baldi, Radice, Fiamma di Re
Year 1924, 1952, 1878
Smoked No, Yes, No
Value $600, $250, $1200
Style Apple, Bent, Globe
Bowl Finish Tan Shell, Blast, Finish 3
Grain Grain 1, Grain 2, Grain 3
Stem Material Bakelite, Amber, Stem 3
Stem Original Yes, No, No
Maker Mark Good, Fair, Excellent
Box/Case No, Yes
Condition Box/Case (if Box/Case = Yes)
Good, Poor, Fair

The userform has comboboxes for the above fields, which are all fed by data
validation lists, and a couple of command buttons to run the search
(cmdSearch), clear the search criteria and extract range (cmdNew). There are
three rows of comboboxes (for three sets of criteria)

So far, I've only been testing the application with one criteria in the
first criteria row, and that has been the Maker field, generally, just
because it's the first field in the row.

The extract range column headers are the same as the inventory column
headers. The criteria range is also the same except instead for the following:

Start Year, End Year, and a calculated field Year [=AND(Inventory!Year>=B3,
Inventory!Year <=C3)]
Start Value, End Value and a calculated field Value
[=AND(Inventory!Value>=F3, Inventory!Value <=G3)]

The calculated fields have the same field name as the fields Year and Value
in the database and the extract range.

Let me know if you need anything else. I have a small test file I can
upload, as well, if you wish. Just let me know where and how.

Thanks,
--elizabeth

 

[Dave Peterson]

ps. This is one of the several billions(?) of these sites:

http://www.senduit.com/

You'll have to share the URL after you upload it, though.

Instead of me taking the time to set up the test workbook -- and probably not
coming close, how about replacing any proprietary/top secret info with dummy
data. (Do this against a copy of the workbook!)

Either add some code to populate the userform -- or add a sheet that describes
what should be entered.

Then zip the file and send it to me directly.

Remove the "xspam" from my email address.

Alternatively, you could do the same thing and post it to one of the
file-sharing web sites and get lots of eyeballs to review it.

Let us know what you're going to do.

But I haven't seen anything that describes how the macro version of the advanced
filter fails -- you just say that it doesn't work.

Dave, I don't know how it fails. I wish I did. Maybe I could figure it out
then. There are no compile or run-time errors. Everything works fine except
the actual line of code for advancedfilter does nothing.

So in order to test it, I'd want to know what the data looks like, what is in
the userform when the advanced filter fails and what fail really means.

The pipe database has the following fields, all formatted as text, and I've
included sample data for three pipes (made up when I don't yet have any from
my friend).
The field names below are the database column headers.

The database is on Worksheet "Inventory" and the search and extract ranges
are on worksheet "Search Criteria").

Pipe ID 1, 2, 3, etc.
Maker Baldo Baldi, Radice, Fiamma di Re
Year 1924, 1952, 1878
Smoked No, Yes, No
Value $600, $250, $1200
Style Apple, Bent, Globe
Bowl Finish Tan Shell, Blast, Finish 3
Grain Grain 1, Grain 2, Grain 3
Stem Material Bakelite, Amber, Stem 3
Stem Original Yes, No, No
Maker Mark Good, Fair, Excellent
Box/Case No, Yes
Condition Box/Case (if Box/Case = Yes)
Good, Poor, Fair

The userform has comboboxes for the above fields, which are all fed by data
validation lists, and a couple of command buttons to run the search
(cmdSearch), clear the search criteria and extract range (cmdNew). There are
three rows of comboboxes (for three sets of criteria)

So far, I've only been testing the application with one criteria in the
first criteria row, and that has been the Maker field, generally, just
because it's the first field in the row.

The extract range column headers are the same as the inventory column
headers. The criteria range is also the same except instead for the following:

Start Year, End Year, and a calculated field Year [=AND(Inventory!Year>=B3,
Inventory!Year <=C3)]
Start Value, End Value and a calculated field Value
[=AND(Inventory!Value>=F3, Inventory!Value <=G3)]

The calculated fields have the same field name as the fields Year and Value
in the database and the extract range.

Let me know if you need anything else. I have a small test file I can
upload, as well, if you wish. Just let me know where and how.

Thanks,
--elizabeth

 

[--elizabeth]

Okay, Dave, here's the link at sendut.com. Thanks so much.
--elizabeth

http://senduit.com/22b7cb

 

[Dave Peterson]

Oops. The URL expired before I got to it.

Remember to change that "Expire in" box to some more reasonable <vbg>.