Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Advancedfilter returning all rows instead of ones that match crit
Hi;
I have a workbook with two sheets: Data and Results. My search button on the Results sheet will see what criteria has been entered in the criteria section of Results and then display rows in the Data sheet that match the Criteria. The matching rows are displayed in the Result section of the Result sheet. I have the following declarations: Dim MaxResults As Integer, MyCol As Integer, ResultsRng As String Dim MyRow As Integer, LastDataRow As Integer, DataRng As String Dim CritRow As Integer, CritRng As String, RightCol As Integer Dim TopRow As Integer, BottomRow As Integer, LeftCol As Integer DataRng = "$A$2:$H$9" 'range of cells to searched on the data sheet CritRng = "$B$3:$I$3" ' range of cells for Criteria table ResultsRng = "$B$9:$I$1000" ' range of headers for Results table Towards the end of the code is the following line: Worksheets("Data").Range(DataRng).AdvancedFilter Action:=xlFilterCopy, _ CriteriaRange:=Range(CritRng), CopyToRange:=Range(ResultsRng), _ Unique:=True Instead of returning only the rows in the "Data" sheet that match the criteria range, all rows in "Data" are returned to the Results section of the Results sheet. DataRng ,CritRng and ResultsRng all seem to have the proper values. This occurs whether I set Unique in the AdvancedFilter statement to true or false. Any help is greatly appreciated. -- JJFJR |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Advancedfilter returning all rows instead of ones that matchcrit
Your criteria range needs to include the heading cells, and the criteria
cells. Your range is only one row: CritRng = "$B$3:$I$3" ' range of cells for Criteria table jjfjr wrote: Hi; I have a workbook with two sheets: Data and Results. My search button on the Results sheet will see what criteria has been entered in the criteria section of Results and then display rows in the Data sheet that match the Criteria. The matching rows are displayed in the Result section of the Result sheet. I have the following declarations: Dim MaxResults As Integer, MyCol As Integer, ResultsRng As String Dim MyRow As Integer, LastDataRow As Integer, DataRng As String Dim CritRow As Integer, CritRng As String, RightCol As Integer Dim TopRow As Integer, BottomRow As Integer, LeftCol As Integer DataRng = "$A$2:$H$9" 'range of cells to searched on the data sheet CritRng = "$B$3:$I$3" ' range of cells for Criteria table ResultsRng = "$B$9:$I$1000" ' range of headers for Results table Towards the end of the code is the following line: Worksheets("Data").Range(DataRng).AdvancedFilter Action:=xlFilterCopy, _ CriteriaRange:=Range(CritRng), CopyToRange:=Range(ResultsRng), _ Unique:=True Instead of returning only the rows in the "Data" sheet that match the criteria range, all rows in "Data" are returned to the Results section of the Results sheet. DataRng ,CritRng and ResultsRng all seem to have the proper values. This occurs whether I set Unique in the AdvancedFilter statement to true or false. Any help is greatly appreciated. -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Advancedfilter returning all rows instead of ones that match
Hi;
I changed critRng to $B$3:$I$3 so that the header cells are included. I tried a simple criteria with only one criteria cell with text to search on. It seemed to work in that I now get only one row as a hit however, when I clear out the criteria cells and type in some different info in another cell(s) for another search, I keep getting the same row that was brought up on my first search. Is there any way to clear out the advanced filter mechanism? Could there be another problem? I also looked at the webpage URL that you sent with your response. Any help is very appreciated. -- JJFJR "Debra Dalgleish" wrote: Your criteria range needs to include the heading cells, and the criteria cells. Your range is only one row: CritRng = "$B$3:$I$3" ' range of cells for Criteria table jjfjr wrote: Hi; I have a workbook with two sheets: Data and Results. My search button on the Results sheet will see what criteria has been entered in the criteria section of Results and then display rows in the Data sheet that match the Criteria. The matching rows are displayed in the Result section of the Result sheet. I have the following declarations: Dim MaxResults As Integer, MyCol As Integer, ResultsRng As String Dim MyRow As Integer, LastDataRow As Integer, DataRng As String Dim CritRow As Integer, CritRng As String, RightCol As Integer Dim TopRow As Integer, BottomRow As Integer, LeftCol As Integer DataRng = "$A$2:$H$9" 'range of cells to searched on the data sheet CritRng = "$B$3:$I$3" ' range of cells for Criteria table ResultsRng = "$B$9:$I$1000" ' range of headers for Results table Towards the end of the code is the following line: Worksheets("Data").Range(DataRng).AdvancedFilter Action:=xlFilterCopy, _ CriteriaRange:=Range(CritRng), CopyToRange:=Range(ResultsRng), _ Unique:=True Instead of returning only the rows in the "Data" sheet that match the criteria range, all rows in "Data" are returned to the Results section of the Results sheet. DataRng ,CritRng and ResultsRng all seem to have the proper values. This occurs whether I set Unique in the AdvancedFilter statement to true or false. Any help is greatly appreciated. -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Advancedfilter returning all rows instead of ones that match
FYI;
In my previous response I meant to say that I changed critrng to $B$2:$I$3 to include the header cells. -- JJFJR "Debra Dalgleish" wrote: Your criteria range needs to include the heading cells, and the criteria cells. Your range is only one row: CritRng = "$B$3:$I$3" ' range of cells for Criteria table jjfjr wrote: Hi; I have a workbook with two sheets: Data and Results. My search button on the Results sheet will see what criteria has been entered in the criteria section of Results and then display rows in the Data sheet that match the Criteria. The matching rows are displayed in the Result section of the Result sheet. I have the following declarations: Dim MaxResults As Integer, MyCol As Integer, ResultsRng As String Dim MyRow As Integer, LastDataRow As Integer, DataRng As String Dim CritRow As Integer, CritRng As String, RightCol As Integer Dim TopRow As Integer, BottomRow As Integer, LeftCol As Integer DataRng = "$A$2:$H$9" 'range of cells to searched on the data sheet CritRng = "$B$3:$I$3" ' range of cells for Criteria table ResultsRng = "$B$9:$I$1000" ' range of headers for Results table Towards the end of the code is the following line: Worksheets("Data").Range(DataRng).AdvancedFilter Action:=xlFilterCopy, _ CriteriaRange:=Range(CritRng), CopyToRange:=Range(ResultsRng), _ Unique:=True Instead of returning only the rows in the "Data" sheet that match the criteria range, all rows in "Data" are returned to the Results section of the Results sheet. DataRng ,CritRng and ResultsRng all seem to have the proper values. This occurs whether I set Unique in the AdvancedFilter statement to true or false. Any help is greatly appreciated. -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Advancedfilter returning all rows instead of ones that match
I've done some more inspection on the behavior of the macro and it seems to
be always returning the first row of the "Data" sheet no matter what criteria I put in. -- JJFJR "jjfjr" wrote: FYI; In my previous response I meant to say that I changed critrng to $B$2:$I$3 to include the header cells. -- JJFJR "Debra Dalgleish" wrote: Your criteria range needs to include the heading cells, and the criteria cells. Your range is only one row: CritRng = "$B$3:$I$3" ' range of cells for Criteria table jjfjr wrote: Hi; I have a workbook with two sheets: Data and Results. My search button on the Results sheet will see what criteria has been entered in the criteria section of Results and then display rows in the Data sheet that match the Criteria. The matching rows are displayed in the Result section of the Result sheet. I have the following declarations: Dim MaxResults As Integer, MyCol As Integer, ResultsRng As String Dim MyRow As Integer, LastDataRow As Integer, DataRng As String Dim CritRow As Integer, CritRng As String, RightCol As Integer Dim TopRow As Integer, BottomRow As Integer, LeftCol As Integer DataRng = "$A$2:$H$9" 'range of cells to searched on the data sheet CritRng = "$B$3:$I$3" ' range of cells for Criteria table ResultsRng = "$B$9:$I$1000" ' range of headers for Results table Towards the end of the code is the following line: Worksheets("Data").Range(DataRng).AdvancedFilter Action:=xlFilterCopy, _ CriteriaRange:=Range(CritRng), CopyToRange:=Range(ResultsRng), _ Unique:=True Instead of returning only the rows in the "Data" sheet that match the criteria range, all rows in "Data" are returned to the Results section of the Results sheet. DataRng ,CritRng and ResultsRng all seem to have the proper values. This occurs whether I set Unique in the AdvancedFilter statement to true or false. Any help is greatly appreciated. -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Advancedfilter returning all rows instead of ones that match
What's in the criteria area, and what is some sample data that you
expect would be returned for that criteria? jjfjr wrote: I've done some more inspection on the behavior of the macro and it seems to be always returning the first row of the "Data" sheet no matter what criteria I put in. -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Advancedfilter returning all rows instead of ones that match
Hi;
Thanks for the response. My workbook captures information about aircraft components. In the "Data" sheet the row headings are Location, Section, Shelf, Manufacturer Number, Part Serial Number, Item Description, Comment and Condition. The "Results" sheet has the Criteria in the top three rows and the Results are displayed in the lower portion. When I enter "F6F" in the Item Description column in the Criteria section, I would expect to get all rows with F6F in that column. However, while the appropriate row was returned, after I clear out the criteria and enter something else the same row from the first search (it also happens to be the first row) appears. I experimented by adding a new row at the top of the Data sheet and sure enough when ever I enter any info into any column of the criteria range this top row appears in the results area. Ultimately, what I want to be able to do is put info in so that I can AND and OR criteria as stated in the website URL you sent. If neeccessary, I can email you the workbook for your inspection. Thanks -- JJFJR "Debra Dalgleish" wrote: What's in the criteria area, and what is some sample data that you expect would be returned for that criteria? jjfjr wrote: I've done some more inspection on the behavior of the macro and it seems to be always returning the first row of the "Data" sheet no matter what criteria I put in. -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Advancedfilter returning all rows instead of ones that match
You mention that criteria are in the top three rows, but your code only
mentions rows 2:3. Are the criteria range headings in cells B2:I2? CritRng = "$B$2:$I$3" ' range of cells for Criteria table Also, your data range is only 8 rows. Is that what you intended? DataRng = "$A$2:$H$9" 'range of cells to searched on the data sheet jjfjr wrote: Hi; Thanks for the response. My workbook captures information about aircraft components. In the "Data" sheet the row headings are Location, Section, Shelf, Manufacturer Number, Part Serial Number, Item Description, Comment and Condition. The "Results" sheet has the Criteria in the top three rows and the Results are displayed in the lower portion. When I enter "F6F" in the Item Description column in the Criteria section, I would expect to get all rows with F6F in that column. However, while the appropriate row was returned, after I clear out the criteria and enter something else the same row from the first search (it also happens to be the first row) appears. I experimented by adding a new row at the top of the Data sheet and sure enough when ever I enter any info into any column of the criteria range this top row appears in the results area. Ultimately, what I want to be able to do is put info in so that I can AND and OR criteria as stated in the website URL you sent. If neeccessary, I can email you the workbook for your inspection. Thanks -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Advancedfilter returning all rows instead of ones that match
Hi;
The logic has changed a bit. The following is the bulk of the code: Private Sub Search_Click() Dim MaxResults As Integer, MyCol As Integer, ResultsRng As String Dim MyRow As Integer, LastDataRow As Integer, DataRng As String Dim CritRow As Integer, CritRng As String, RightCol As Integer Dim TopRow As Integer, BottomRow As Integer, LeftCol As Integer ' the source data is in a worksheet called 'Data' ' *** MODIFY AND SET YOUR OWN RANGES ON THE FOLLOWING DECLARATIONS *** ' cell Data!E1 contains the last row number of data [=COUNT(E4:E100)+3] MsgBox "Before declarations" MsgBox LastDataRow MsgBox Worksheets("Data").Range("E1").Value LastDataRow = Worksheets("Data").Range("E1").Value MsgBox LastDataRow DataRng = "A3:H3" ' range of column headers for Data table CritRng = "B3:I5" ' range of cells for Criteria table ResultsRng = "B8:I8" ' range of headers for Results table MaxResults = 1000 ' any value higher than the number of possible results MsgBox "After declarations" ' **************** END OF DECLARATIONS ********************* ' fix the data range to incorporate the last row MsgBox "Before data range fix" TopRow = Worksheets("Data").Range(DataRng).Row MsgBox "TopRow= " & TopRow LeftCol = Range(DataRng).Column RightCol = LeftCol + Range(DataRng).Columns.Count - 1 DataRng = Range(Cells(TopRow, LeftCol), Cells(LastDataRow, RightCol)).Address MsgBox "After data range fix" ' fix the results range to incorporate the last row TopRow = Worksheets("Data").Range(ResultsRng).Row MsgBox "TopRow= " & TopRow LeftCol = Range(ResultsRng).Column RightCol = LeftCol + Range(ResultsRng).Columns.Count - 1 ResultsRng = Range(Cells(TopRow + 1, LeftCol), Cells(MaxResults, RightCol)).Address Range(ResultsRng).ClearContents ' clear any previous results but not headers ResultsRng = Range(Cells(TopRow + 1, LeftCol), Cells(MaxResults, RightCol)).Address ' fix the criteria range and identify the last row containing any items TopRow = Range(CritRng).Row MsgBox "TopRow= " & TopRow BottomRow = TopRow + Range(CritRng).Rows.Count - 1 MsgBox "BottomRow= " & BottomRow LeftCol = Range(CritRng).Column MsgBox "LeftCol= " & LeftCol RightCol = LeftCol + Range(CritRng).Columns.Count - 1 MsgBox "RightCol= " & RightCol CritRow = 0 For MyRow = TopRow To BottomRow MsgBox "MyRow=" & MyRow For MyCol = LeftCol To RightCol MsgBox "MyCol=" & MyCol MsgBox "Cells(MyRow,MyCol).Value=" & Cells(MyRow, MyCol).Value If Cells(MyRow, MyCol).Value < "" Then CritRow = MyRow MsgBox "CritRow= " & CritRow Next Next MsgBox " Before If CritRow= " & CritRow If CritRow = 0 Then MsgBox "No Criteria detected" Else CritRng = Range(Cells(TopRow - 1, LeftCol), Cells(CritRow, RightCol)).Address MsgBox "DataRng= " & DataRng MsgBox "CritRng= " & CritRng MsgBox "ResultsRng= " & ResultsRng MsgBox "Worksheets(Results).Range(CritRng)= " & Worksheets("Results").Range(CritRng).Address Worksheets("Data").Range(DataRng).AdvancedFilter Action:=xlFilterCopy, _ CriteriaRange:=Worksheets("Results").Range(CritRng ), CopyToRange:=Worksheets("Results").Range(ResultsRn g), _ Unique:=True End If Range("A5").Select End Sub The MsgBox messages are there to echo back the various values that variables currently hold. Any ideas? -- JJFJR "Debra Dalgleish" wrote: You mention that criteria are in the top three rows, but your code only mentions rows 2:3. Are the criteria range headings in cells B2:I2? CritRng = "$B$2:$I$3" ' range of cells for Criteria table Also, your data range is only 8 rows. Is that what you intended? DataRng = "$A$2:$H$9" 'range of cells to searched on the data sheet jjfjr wrote: Hi; Thanks for the response. My workbook captures information about aircraft components. In the "Data" sheet the row headings are Location, Section, Shelf, Manufacturer Number, Part Serial Number, Item Description, Comment and Condition. The "Results" sheet has the Criteria in the top three rows and the Results are displayed in the lower portion. When I enter "F6F" in the Item Description column in the Criteria section, I would expect to get all rows with F6F in that column. However, while the appropriate row was returned, after I clear out the criteria and enter something else the same row from the first search (it also happens to be the first row) appears. I experimented by adding a new row at the top of the Data sheet and sure enough when ever I enter any info into any column of the criteria range this top row appears in the results area. Ultimately, what I want to be able to do is put info in so that I can AND and OR criteria as stated in the website URL you sent. If neeccessary, I can email you the workbook for your inspection. Thanks -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Match returning #N/A | Excel Worksheet Functions | |||
index match array function-returning only first match, need last. | Excel Worksheet Functions | |||
Count the number of rows where more than one column meets set crit | Excel Worksheet Functions | |||
How to use AdvancedFilter wildcard to match end of string? | Excel Discussion (Misc queries) | |||
How to count rows in a user-defined AutoFilter or AdvancedFilter is active? | Excel Programming |