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" wrote in message
...
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
he

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