|
Adv Filter fails in macro, works manually
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 |
Adv Filter fails in macro, works manually
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 |
Adv Filter fails in macro, works manually
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 |
Adv Filter fails in macro, works manually
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 |
Adv Filter fails in macro, works manually
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 |
Adv Filter fails in macro, works manually
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 |
Adv Filter fails in macro, works manually
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 |
Adv Filter fails in macro, works manually
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 |
Adv Filter fails in macro, works manually
Show me the code and state what the problem is.
-- Regards Dave Hawley www.ozgrid.com "--elizabeth" wrote in message ... 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 |
Adv Filter fails in macro, works manually
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 |
Adv Filter fails in macro, works manually
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/sh...d.php?t=192879 http://www.thecodecage.com/forumz |
Adv Filter fails in macro, works manually
Me???
Nope, I can't post them. joel wrote: 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/sh...d.php?t=192879 http://www.thecodecage.com/forumz -- Dave Peterson |
Adv Filter fails in macro, works manually
"ozgrid.com" wrote:
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'!A2:P5 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 |
Adv Filter fails in macro, works manually
"Dave Peterson" wrote:
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 |
Adv Filter fails in macro, works manually
joel" wrote:
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 |
Adv Filter fails in macro, works manually
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 wrote: "Dave Peterson" wrote: 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 -- Dave Peterson |
Adv Filter fails in macro, works manually
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 |
Adv Filter fails in macro, works manually
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. --elizabeth wrote: 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 |
Adv Filter fails in macro, works manually
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. Dave Peterson wrote: 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. --elizabeth wrote: 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 -- Dave Peterson |
Adv Filter fails in macro, works manually
|
Adv Filter fails in macro, works manually
Oops. The URL expired before I got to it.
Remember to change that "Expire in" box to some more reasonable <vbg. --elizabeth wrote: Okay, Dave, here's the link at sendut.com. Thanks so much. --elizabeth http://senduit.com/22b7cb -- Dave Peterson |
Adv Filter fails in macro, works manually
You code is creating a very large databae which slow down the code significantly. I added some code in THISWORKBOOK to eliviate this problem. Public Sub Workbook_Open() ' Set WS_INV = Worksheets("Inventory") ' Set WS_DATA = Worksheets("Data Validation Lists") ' Set WS_CRIT = Worksheets("Search Criteria") ' WS_DATA.Activate Dim LastRow With Worksheets("Inventory") LastRow = .Range("A" & Rows.Count).End(xlUp).Row .Range("Database").Name.RefersTo = "=Inventory!A1:A" & LastRow End With 'Load frmAddPipe Load frmSearchPipes 'frmAddPipe.Show frmSearchPipes.Show Th code above is redefining the named range Database to only include the lnumber of lines in the databae rather than every row in the worksheet (65536). See if this helps. -- joel ------------------------------------------------------------------------ joel's Profile: 229 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=192879 http://www.thecodecage.com/forumz |
Adv Filter fails in macro, works manually
|
Adv Filter fails in macro, works manually
First, I opened the workbook and saw a problem with a formula in the "Search
Criteria" worksheet in column D =AND(Inventory!Year=B3, Inventory!Year <=C3) I'm not sure where you're located and I'm not sure what you're trying to do, but Year shouldn't be used as a Name in English versions of excel. It looks way too much like the =year() worksheet function. But that wasn't important to the problem... Second, I only tested with two criteria (Maker:=Baldo-Baldi and Beg Yr:=1952). Then I added some dots to ranges that you missed qualifying. And I moved some code into the appropriate with/end with lines (adding dots <vbg). But that wasn't enough. I changed the way the that the rgDB was created (I wouldn't use the entire row--with all those empty cells in row 1: With Worksheets("Inventory") LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column Set rgDB = .Range("A1", .Cells(LastRow, LastCol)) rgDB.Name = "'" & .Name & "'!DataBase" End With But that didn't fix the problem either... So I made sure that the dates/numbers were really treated as numbers. With .Range("A3") .Value = cboMaker1 .Offset(0, 1) = CLng(txtBeginYear1) I only made this single change to the numeric entries. You'll want to validate the entries before you blindly use clng(), too. (But it was sufficient for my testing.) And then I clicked the button (I added a button to show the userform modelessly (so I could see behind it when I was looking for stuff) and I got info in the extract range. Here's the entire code from behind the userform: Option Explicit Private Sub cmdSearch_Click() Dim rgDB As Range Dim rgCriteria As Range Dim rgExtract As Range Dim LastRow As Long Dim LastCol As Long With Worksheets("Inventory") LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column Set rgDB = .Range("A1", .Cells(LastRow, LastCol)) rgDB.Name = "'" & .Name & "'!DataBase" End With Set rgCriteria = Worksheets("Search Criteria").Range("Criteria") Set rgExtract = Worksheets("Search Criteria").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") .Value = cboMaker1 .Offset(0, 1) = CLng(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 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 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 .Range("ExtractRows").Clear End With 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 cmdCriteria.Caption = "Multiple Criteria" CriteriaRow Worksheets("Search Criteria").Activate End Sub Private Sub cmdCriteria_Click() If cmdCriteria.Caption = "Multiple Criteria" Then MultipleCriteriaRows cmdCriteria.Caption = "Criteria" Else CriteriaRow cmdCriteria.Caption = "Multiple Criteria" End If End Sub Private Sub MultipleCriteriaRows() Dim ctl As Control For Each ctl In Me.Controls If TypeName(ctl) = "TextBox" Or TypeName(ctl) = "ComboBox" Then If ctl.Tag = 2 Or ctl.Tag = 3 Then ctl.Visible = True End If End If Next ctl End Sub Private Sub CriteriaRow() Dim ctl As Control For Each ctl In Me.Controls If TypeName(ctl) = "TextBox" Or TypeName(ctl) = "ComboBox" Then If ctl.Tag = 2 Or ctl.Tag = 3 Then ctl.Visible = False End If End If Next ctl End Sub --elizabeth wrote: Sorry. If at first you don't succeed.... http://senduit.com/6165d6 -- Dave Peterson |
Adv Filter fails in macro, works manually
One more thing...
I've always thought that it's better to use more sheets than try to jam things into a single sheet. You may want to consider moving the criteria range to its own worksheet (and hide it???) and the extraction range to its own worksheet. It shouldn't be too many changes to your code. And it may make creating the criteria range easier--just clear the entire sheet: with worksheets("criteria") .range("2:" & .rows.count).clear end with And with the extraction sheet: With worksheets("Extract") .cells.clear end with or even create the sheet from scratch each time: on error resume next application.displayalerts = false worksheets("extract").delete application.displayalerts = true on error goto 0 set rgextract = worksheets.add.range("A1") rgextract.parent.name = "Extract" Dave Peterson wrote: First, I opened the workbook and saw a problem with a formula in the "Search Criteria" worksheet in column D =AND(Inventory!Year=B3, Inventory!Year <=C3) I'm not sure where you're located and I'm not sure what you're trying to do, but Year shouldn't be used as a Name in English versions of excel. It looks way too much like the =year() worksheet function. But that wasn't important to the problem... Second, I only tested with two criteria (Maker:=Baldo-Baldi and Beg Yr:=1952). Then I added some dots to ranges that you missed qualifying. And I moved some code into the appropriate with/end with lines (adding dots <vbg). But that wasn't enough. I changed the way the that the rgDB was created (I wouldn't use the entire row--with all those empty cells in row 1: With Worksheets("Inventory") LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column Set rgDB = .Range("A1", .Cells(LastRow, LastCol)) rgDB.Name = "'" & .Name & "'!DataBase" End With But that didn't fix the problem either... So I made sure that the dates/numbers were really treated as numbers. With .Range("A3") .Value = cboMaker1 .Offset(0, 1) = CLng(txtBeginYear1) I only made this single change to the numeric entries. You'll want to validate the entries before you blindly use clng(), too. (But it was sufficient for my testing.) And then I clicked the button (I added a button to show the userform modelessly (so I could see behind it when I was looking for stuff) and I got info in the extract range. Here's the entire code from behind the userform: Option Explicit Private Sub cmdSearch_Click() Dim rgDB As Range Dim rgCriteria As Range Dim rgExtract As Range Dim LastRow As Long Dim LastCol As Long With Worksheets("Inventory") LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column Set rgDB = .Range("A1", .Cells(LastRow, LastCol)) rgDB.Name = "'" & .Name & "'!DataBase" End With Set rgCriteria = Worksheets("Search Criteria").Range("Criteria") Set rgExtract = Worksheets("Search Criteria").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") .Value = cboMaker1 .Offset(0, 1) = CLng(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 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 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 .Range("ExtractRows").Clear End With 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 cmdCriteria.Caption = "Multiple Criteria" CriteriaRow Worksheets("Search Criteria").Activate End Sub Private Sub cmdCriteria_Click() If cmdCriteria.Caption = "Multiple Criteria" Then MultipleCriteriaRows cmdCriteria.Caption = "Criteria" Else CriteriaRow cmdCriteria.Caption = "Multiple Criteria" End If End Sub Private Sub MultipleCriteriaRows() Dim ctl As Control For Each ctl In Me.Controls If TypeName(ctl) = "TextBox" Or TypeName(ctl) = "ComboBox" Then If ctl.Tag = 2 Or ctl.Tag = 3 Then ctl.Visible = True End If End If Next ctl End Sub Private Sub CriteriaRow() Dim ctl As Control For Each ctl In Me.Controls If TypeName(ctl) = "TextBox" Or TypeName(ctl) = "ComboBox" Then If ctl.Tag = 2 Or ctl.Tag = 3 Then ctl.Visible = False End If End If Next ctl End Sub --elizabeth wrote: Sorry. If at first you don't succeed.... http://senduit.com/6165d6 -- Dave Peterson -- Dave Peterson |
Adv Filter fails in macro, works manually
Dave, thank you so much. Our company from Spain is leaving tomorrow morning,
so I won't have time to try your code until then. --elizabeth "Dave Peterson" wrote: One more thing... I've always thought that it's better to use more sheets than try to jam things into a single sheet. You may want to consider moving the criteria range to its own worksheet (and hide it???) and the extraction range to its own worksheet. It shouldn't be too many changes to your code. And it may make creating the criteria range easier--just clear the entire sheet: with worksheets("criteria") .range("2:" & .rows.count).clear end with And with the extraction sheet: With worksheets("Extract") .cells.clear end with or even create the sheet from scratch each time: on error resume next application.displayalerts = false worksheets("extract").delete application.displayalerts = true on error goto 0 set rgextract = worksheets.add.range("A1") rgextract.parent.name = "Extract" Dave Peterson wrote: First, I opened the workbook and saw a problem with a formula in the "Search Criteria" worksheet in column D =AND(Inventory!Year=B3, Inventory!Year <=C3) I'm not sure where you're located and I'm not sure what you're trying to do, but Year shouldn't be used as a Name in English versions of excel. It looks way too much like the =year() worksheet function. But that wasn't important to the problem... Second, I only tested with two criteria (Maker:=Baldo-Baldi and Beg Yr:=1952). Then I added some dots to ranges that you missed qualifying. And I moved some code into the appropriate with/end with lines (adding dots <vbg). But that wasn't enough. I changed the way the that the rgDB was created (I wouldn't use the entire row--with all those empty cells in row 1: With Worksheets("Inventory") LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column Set rgDB = .Range("A1", .Cells(LastRow, LastCol)) rgDB.Name = "'" & .Name & "'!DataBase" End With But that didn't fix the problem either... So I made sure that the dates/numbers were really treated as numbers. With .Range("A3") .Value = cboMaker1 .Offset(0, 1) = CLng(txtBeginYear1) I only made this single change to the numeric entries. You'll want to validate the entries before you blindly use clng(), too. (But it was sufficient for my testing.) And then I clicked the button (I added a button to show the userform modelessly (so I could see behind it when I was looking for stuff) and I got info in the extract range. Here's the entire code from behind the userform: Option Explicit Private Sub cmdSearch_Click() Dim rgDB As Range Dim rgCriteria As Range Dim rgExtract As Range Dim LastRow As Long Dim LastCol As Long With Worksheets("Inventory") LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column Set rgDB = .Range("A1", .Cells(LastRow, LastCol)) rgDB.Name = "'" & .Name & "'!DataBase" End With Set rgCriteria = Worksheets("Search Criteria").Range("Criteria") Set rgExtract = Worksheets("Search Criteria").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") .Value = cboMaker1 .Offset(0, 1) = CLng(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 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 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 .Range("ExtractRows").Clear End With 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 cmdCriteria.Caption = "Multiple Criteria" CriteriaRow Worksheets("Search Criteria").Activate End Sub Private Sub cmdCriteria_Click() If cmdCriteria.Caption = "Multiple Criteria" Then MultipleCriteriaRows cmdCriteria.Caption = "Criteria" Else CriteriaRow cmdCriteria.Caption = "Multiple Criteria" End If End Sub Private Sub MultipleCriteriaRows() Dim ctl As Control For Each ctl In Me.Controls If TypeName(ctl) = "TextBox" Or TypeName(ctl) = "ComboBox" Then If ctl.Tag = 2 Or ctl.Tag = 3 Then ctl.Visible = True End If End If Next ctl End Sub Private Sub CriteriaRow() Dim ctl As Control For Each ctl In Me.Controls If TypeName(ctl) = "TextBox" Or TypeName(ctl) = "ComboBox" Then If ctl.Tag = 2 Or ctl.Tag = 3 Then ctl.Visible = False End If End If Next ctl End Sub --elizabeth wrote: Sorry. If at first you don't succeed.... http://senduit.com/6165d6 -- Dave Peterson -- Dave Peterson . |
Adv Filter fails in macro, works manually
Thanks, Joel. I had become aware of this but hadn't dealt with it yet. Thanks
for your code. --elizabeth "joel" wrote: You code is creating a very large databae which slow down the code significantly. I added some code in THISWORKBOOK to eliviate this problem. Public Sub Workbook_Open() ' Set WS_INV = Worksheets("Inventory") ' Set WS_DATA = Worksheets("Data Validation Lists") ' Set WS_CRIT = Worksheets("Search Criteria") ' WS_DATA.Activate Dim LastRow With Worksheets("Inventory") LastRow = .Range("A" & Rows.Count).End(xlUp).Row .Range("Database").Name.RefersTo = "=Inventory!A1:A" & LastRow End With 'Load frmAddPipe Load frmSearchPipes 'frmAddPipe.Show frmSearchPipes.Show Th code above is redefining the named range Database to only include the lnumber of lines in the databae rather than every row in the worksheet (65536). See if this helps. -- joel ------------------------------------------------------------------------ joel's Profile: 229 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=192879 http://www.thecodecage.com/forumz . |
Adv Filter fails in macro, works manually
Dave, I still can't get it to work. <sigh First, I tried correcting my code
to incorporate your changes. Then I gave up and just copied your code over mine. And it still doesn't work for me. I uploaded the file again: http://senduit.com/ef64fe Oh, and I changed the Year column headers in the database, criteria and extract ranges to "Year Made". When we do finally get it working, though, I will incorporate your suggestions of a separate sheet for the extract range. Will the following lines of code work in the WriteValues2CrigRng function? If Not .Offset(0, 1) = vbNullString Then .Offset(0, 1) = CLng(txtBeginYear1) If Not .Offset(0, 5) = vbNullString Then .Offset(0, 5) = FormatCurrency(txtMinValue1, 0) And why is long better than integer? I see long or doubles used a lot in people's codes, but rarely integer. One more question: I wrote the code at the bottom of WriteValues2CritRng, thinking that vbNullString could be the problem in that the form's empty comboboxes weren't transferring to the spreadsheet correctly somehow. So I changed the vbNullStrings to "". And in cmdNew, I again used "" instead of vbNullString. Is that necessary? A good idea? Which is better--vbNullString or ""? Thanks, again, Dave, for all your help. --elizabeth |
Adv Filter fails in macro, works manually
First, I forgot to mention that I unprotected the Inventory sheet.
But I think you've got a couple of problems with the criteria. If you have a field (like Year) that you want to check to see if it's at least 1945 and at most 1954, you have to use criteria like: Year Year =1945 <=1954 Yep. Two columns. Debra Dalgleish explains it he http://contextures.com/xladvfilter01.html look for: Extract Items in a Range She uses dates, but the technique is similar. I added some stuff that checks for numbers in those year fields and value fields and creates a couple of extra columns of info based on what's found (two pair of two columns, actually). I saved the workbook with the criteria in it--but you have to disable macros to see it (since you want to display that userform when you start). And since you used nice names (like txtMinYear1, txtminyear2, txtminyear3), it makes it pretty easy to loop through the controls. I can use a loop to get to the 3 comboboxes for the maker field with something like: for iRow = 1 to xxx .Offset(iRow, 1).Value = Me.Controls("cboMaker" & iRow).Value Then I don't have to use pretty much redundant code to get all those controls. (And adding the 4th line for your filter should be a bit easier <vbg.) I've uploaded a new copy of your workbook (with a different name) to that same site (with a 1 day time limit). http://senduit.com/0d0279 It seems to work ok for me in my little tests. If you decide you don't want to publicly share your workbook, you can send it direct to me. Remove XSpam from my email address. (It's munged so that the spambots that scrape the newsgroups get a fake email id.) --elizabeth wrote: Dave, I still can't get it to work. <sigh First, I tried correcting my code to incorporate your changes. Then I gave up and just copied your code over mine. And it still doesn't work for me. I uploaded the file again: http://senduit.com/ef64fe Oh, and I changed the Year column headers in the database, criteria and extract ranges to "Year Made". When we do finally get it working, though, I will incorporate your suggestions of a separate sheet for the extract range. Will the following lines of code work in the WriteValues2CrigRng function? If Not .Offset(0, 1) = vbNullString Then .Offset(0, 1) = CLng(txtBeginYear1) If Not .Offset(0, 5) = vbNullString Then .Offset(0, 5) = FormatCurrency(txtMinValue1, 0) And why is long better than integer? I see long or doubles used a lot in people's codes, but rarely integer. One more question: I wrote the code at the bottom of WriteValues2CritRng, thinking that vbNullString could be the problem in that the form's empty comboboxes weren't transferring to the spreadsheet correctly somehow. So I changed the vbNullStrings to "". And in cmdNew, I again used "" instead of vbNullString. Is that necessary? A good idea? Which is better--vbNullString or ""? Thanks, again, Dave, for all your help. --elizabeth -- Dave Peterson |
Adv Filter fails in macro, works manually
I forgot about your followup questions...
With modern computers, using Integers/singles doesn't make sense. I've read that the pc works with longs and doubles anyway--so why give it something to convert? And I've always used "", but that's simply because it's easier to type. I don't think it matters in most cases. One more thing I did do is eliminate populating the controls with those "" (or vbnullstring's) when it loads. Unless you did something strange (like changing a property in the property window), those controls will be blanks/null strings to start. --elizabeth wrote: <<snipped And why is long better than integer? I see long or doubles used a lot in people's codes, but rarely integer. One more question: I wrote the code at the bottom of WriteValues2CritRng, thinking that vbNullString could be the problem in that the form's empty comboboxes weren't transferring to the spreadsheet correctly somehow. So I changed the vbNullStrings to "". And in cmdNew, I again used "" instead of vbNullString. Is that necessary? A good idea? Which is better--vbNullString or ""? Thanks, again, Dave, for all your help. --elizabeth -- Dave Peterson |
Adv Filter fails in macro, works manually
Dave, I cannot thank you enough. It will take me a little while to absorb
your changes, but everything seems to be working. --elizabeth "Dave Peterson" wrote: I forgot about your followup questions... With modern computers, using Integers/singles doesn't make sense. I've read that the pc works with longs and doubles anyway--so why give it something to convert? And I've always used "", but that's simply because it's easier to type. I don't think it matters in most cases. One more thing I did do is eliminate populating the controls with those "" (or vbnullstring's) when it loads. Unless you did something strange (like changing a property in the property window), those controls will be blanks/null strings to start. --elizabeth wrote: <<snipped And why is long better than integer? I see long or doubles used a lot in people's codes, but rarely integer. One more question: I wrote the code at the bottom of WriteValues2CritRng, thinking that vbNullString could be the problem in that the form's empty comboboxes weren't transferring to the spreadsheet correctly somehow. So I changed the vbNullStrings to "". And in cmdNew, I again used "" instead of vbNullString. Is that necessary? A good idea? Which is better--vbNullString or ""? Thanks, again, Dave, for all your help. --elizabeth -- Dave Peterson . |
Adv Filter fails in macro, works manually
Make sure you keep backups when you're making changes <vbg.
--elizabeth wrote: Dave, I cannot thank you enough. It will take me a little while to absorb your changes, but everything seems to be working. --elizabeth "Dave Peterson" wrote: I forgot about your followup questions... With modern computers, using Integers/singles doesn't make sense. I've read that the pc works with longs and doubles anyway--so why give it something to convert? And I've always used "", but that's simply because it's easier to type. I don't think it matters in most cases. One more thing I did do is eliminate populating the controls with those "" (or vbnullstring's) when it loads. Unless you did something strange (like changing a property in the property window), those controls will be blanks/null strings to start. --elizabeth wrote: <<snipped And why is long better than integer? I see long or doubles used a lot in people's codes, but rarely integer. One more question: I wrote the code at the bottom of WriteValues2CritRng, thinking that vbNullString could be the problem in that the form's empty comboboxes weren't transferring to the spreadsheet correctly somehow. So I changed the vbNullStrings to "". And in cmdNew, I again used "" instead of vbNullString. Is that necessary? A good idea? Which is better--vbNullString or ""? Thanks, again, Dave, for all your help. --elizabeth -- Dave Peterson . -- Dave Peterson |
Adv Filter fails in macro, works manually
ps.
I forgot to tell you that I removed a reference to the Web component stuff. I don't have that and it was causing compile errors. --elizabeth wrote: Dave, I cannot thank you enough. It will take me a little while to absorb your changes, but everything seems to be working. --elizabeth "Dave Peterson" wrote: I forgot about your followup questions... With modern computers, using Integers/singles doesn't make sense. I've read that the pc works with longs and doubles anyway--so why give it something to convert? And I've always used "", but that's simply because it's easier to type. I don't think it matters in most cases. One more thing I did do is eliminate populating the controls with those "" (or vbnullstring's) when it loads. Unless you did something strange (like changing a property in the property window), those controls will be blanks/null strings to start. --elizabeth wrote: <<snipped And why is long better than integer? I see long or doubles used a lot in people's codes, but rarely integer. One more question: I wrote the code at the bottom of WriteValues2CritRng, thinking that vbNullString could be the problem in that the form's empty comboboxes weren't transferring to the spreadsheet correctly somehow. So I changed the vbNullStrings to "". And in cmdNew, I again used "" instead of vbNullString. Is that necessary? A good idea? Which is better--vbNullString or ""? Thanks, again, Dave, for all your help. --elizabeth -- Dave Peterson . -- Dave Peterson |
Adv Filter fails in macro, works manually
Dave, I copied/pasted all the code from the test file over to my "real" file.
If I search on Maker, MinVal and MaxVal criteria --i.e., Baldo Baldi, 200, 600--the results are displayed. However, if I search on Maker, MinYear and MaxYear criteria--i.e., Baldo Baldi, 1920, 1940--no results are displayed. The code works fine for both MinVal/MaxVal and MinYear/MaxYear in your test file, but not in mine. I've tried to discover why on my own, unsuccessfully. The Search Criteria and Extract Results sheets are created programmatically, so I don't see how that could be the problem. The MinYear/MaxYear values are written to the Search Criteria sheet the same as the MinVal/MaxVal values. I'm stumped. May I please have the benefit of your knowledge and experience again? I uploaded the file and it's good for three days. I'm going to be at my MIL's in the country and the internet there is dial-up, so I may not have access until I return on Monday. So if you get this before then and have time to address it, and I don't get back to you in a timely fasion, that's why. http://senduit.com/94d368 Thanks, --elizabeth |
Adv Filter fails in macro, works manually
In a private email...
First, I'm not sure if you saw this, but I removed the reference to the web component stuff. I don't have that installed and it was causing compile errors for me. Second, I'm not sure if I told you (sorry), but I converted those years in the inventory worksheet to real numbers (from text). When I did that with your newest workbook, it worked fine. You have some bad values in the inventory sheet, too. One way to fix these problems... Select an empty cell Edit|copy Select column C and E on the Inventory sheet Edit|paste special|check Add and values (with that worksheet unprotected) You should see excel's error checking warning (top left corner of the offending cell) go away -- well, if you have it turned on. And you may want to change your Workbook_open procedure. --elizabeth wrote: Dave, I copied/pasted all the code from the test file over to my "real" file. If I search on Maker, MinVal and MaxVal criteria --i.e., Baldo Baldi, 200, 600--the results are displayed. However, if I search on Maker, MinYear and MaxYear criteria--i.e., Baldo Baldi, 1920, 1940--no results are displayed. The code works fine for both MinVal/MaxVal and MinYear/MaxYear in your test file, but not in mine. I've tried to discover why on my own, unsuccessfully. The Search Criteria and Extract Results sheets are created programmatically, so I don't see how that could be the problem. The MinYear/MaxYear values are written to the Search Criteria sheet the same as the MinVal/MaxVal values. I'm stumped. May I please have the benefit of your knowledge and experience again? I uploaded the file and it's good for three days. I'm going to be at my MIL's in the country and the internet there is dial-up, so I may not have access until I return on Monday. So if you get this before then and have time to address it, and I don't get back to you in a timely fasion, that's why. http://senduit.com/94d368 Thanks, --elizabeth -- Dave Peterson |
| All times are GMT +1. The time now is 04:50 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com