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

Please post your code.

Barb Reinhardt



"--elizabeth" wrote:

> 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

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>

"Barb Reinhardt" wrote:

> Please post your code.
>
> Barb Reinhardt
>
>
>
> "--elizabeth" wrote:
>
> > 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

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

You should clear "rgExtract" before applying AdvancedFilter.


--
Regards
Dave Hawley
www.ozgrid.com
"--elizabeth" <(E-Mail Removed)> wrote in message
news:71B857CF-1A78-4D92-A624-(E-Mail Removed)...
> 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
>
>

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 wrote:
>
> 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

--

Dave Peterson

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

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

Show me the code and state what the problem is.


--
Regards
Dave Hawley
www.ozgrid.com
"--elizabeth" <(E-Mail Removed)> wrote in message
news:56F198DD-07FA-45E9-9163-(E-Mail Removed)...
> 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
>

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.

--elizabeth wrote:
>
> 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

--

Dave Peterson