Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creteria Help
Hi,
Thanks in advance The following code is set for Creteria1 to = "A" how do I make it for A, B or C With Worksheets("Panel Details").Range("A1:IV2500") .AutoFilter Field:=1, Criteria1:="A" .SpecialCells(xlCellTypeVisible).Copy _ Worksheets("SS A").Cells(1, 1) .AutoFilter End With Thanks again Trev |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creteria Help
Trev,
Maybe this will help: http://www.meadinkent.co.uk/xlfilter.htm Record a macro whilst you do this to generate the code. NickHK "Trever B" wrote in message ... Hi, Thanks in advance The following code is set for Creteria1 to = "A" how do I make it for A, B or C With Worksheets("Panel Details").Range("A1:IV2500") .AutoFilter Field:=1, Criteria1:="A" .SpecialCells(xlCellTypeVisible).Copy _ Worksheets("SS A").Cells(1, 1) .AutoFilter End With Thanks again Trev |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creteria Help
Thanks Nick,
Not much help It does not help me how to input multi creteria, Have tried:- 1) = "A" and "B" and "C" 2) = "A" or "B" or "C" Tried recording a macro but still need help on criteria Don't know what else to try. Hope someone else can help. Trev "NickHK" wrote: Trev, Maybe this will help: http://www.meadinkent.co.uk/xlfilter.htm Record a macro whilst you do this to generate the code. NickHK "Trever B" wrote in message ... Hi, Thanks in advance The following code is set for Creteria1 to = "A" how do I make it for A, B or C With Worksheets("Panel Details").Range("A1:IV2500") .AutoFilter Field:=1, Criteria1:="A" .SpecialCells(xlCellTypeVisible).Copy _ Worksheets("SS A").Cells(1, 1) .AutoFilter End With Thanks again Trev |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creteria Help
Did you read this section ?
<Quote a single item (e.g. 'SMITH') underneath one of the fields more than one criteria item in a single row - applies an 'AND' rule (e.g. 'SMITH' and 'XN41') more than one criteria on different rows - applies an 'OR' rule (e.g. 'XN41' or '<1500') </Quote So you have to create your criteria in a column of A B C NickHK "Trever B" wrote in message ... Thanks Nick, Not much help It does not help me how to input multi creteria, Have tried:- 1) = "A" and "B" and "C" 2) = "A" or "B" or "C" Tried recording a macro but still need help on criteria Don't know what else to try. Hope someone else can help. Trev "NickHK" wrote: Trev, Maybe this will help: http://www.meadinkent.co.uk/xlfilter.htm Record a macro whilst you do this to generate the code. NickHK "Trever B" wrote in message ... Hi, Thanks in advance The following code is set for Creteria1 to = "A" how do I make it for A, B or C With Worksheets("Panel Details").Range("A1:IV2500") .AutoFilter Field:=1, Criteria1:="A" .SpecialCells(xlCellTypeVisible).Copy _ Worksheets("SS A").Cells(1, 1) .AutoFilter End With Thanks again Trev |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creteria Help
Hi
Autofilter only lets you filter on two criteria at most. For 3 criteria you need advanced filter. I'll assume you are selecting your criteria from a multiselect listbox. If not, this should give you a start Private Sub OKButton_Click() Dim Grouplistdata As New Collection Dim i As Integer, GroupCount As Integer Dim Item As Variant Dim GroupCriteria As Range Application.ScreenUpdating = False With RegGroups.GroupList 'Get the data in the GroupList ListBox For i = 0 To .ListCount - 1 'for each Group in the list If .Selected(i) Then Grouplistdata.Add .List(i) End If Next i End With GroupCount = Grouplistdata.Count 'Remove any filter present With Worksheets("Panel Details") On Error Resume Next 'required if Advanced filter used .ShowAllData On Error GoTo 0 .AutoFilterMode = False 'Removes drop down arrows End With Select Case GroupCount 'Check the user hasn't clicked OK on the ViewList userform without selecting anything Case 0 Exit Sub 'nothing happens and form is still visible 'Use AutoFilter if GroupCount is 1 or 2 Case 1 Worksheets("Panel Details").Range("A1:IV2500").AutoFilter Field:=1, Criteria1:=CStr(Grouplistdata(1)) Case 2 Worksheets("Panel Details").Range("A1:IV2500").AutoFilter Field:=1, Criteria1:=CStr(Grouplistdata(1)), Operator:=xlOr, Criteria2:=CStr(Grouplistdata(2)) Case Is 2 'Start by inserting a worksheet and creating a Criteria Range to put into AdvancedFilter 'addSheet first removes the sheet, so it is always a fresh sheet AddSheet "WorkSpaceSheet" 'now the active sheet With ActiveWorkbook.Worksheets("WorkSpaceSheet") .Visible = False .Cells(1, 1).Value = "Group" 'same as heading on worksheet for "A", "B", "C",.. i = 1 'set a counter and put group items below "Group" For Each Item In Grouplistdata i = i + 1 .Cells(i, 1).Value = Item Next Item Set GroupCriteria = .Cells(1, 1).CurrentRegion End With 'worksheet 'Filter the list With Worksheets("Panel Details") .Activate .Range("A1:IV2500").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=GroupCriteria End with End Select End Sub regards Paul |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creteria Help
Yes I did read the section but still did not like it and still no joy.
However Paul Robinson seams to have the answer Thanks anyway Regards Trev "NickHK" wrote: Did you read this section ? <Quote a single item (e.g. 'SMITH') underneath one of the fields more than one criteria item in a single row - applies an 'AND' rule (e.g. 'SMITH' and 'XN41') more than one criteria on different rows - applies an 'OR' rule (e.g. 'XN41' or '<1500') </Quote So you have to create your criteria in a column of A B C NickHK "Trever B" wrote in message ... Thanks Nick, Not much help It does not help me how to input multi creteria, Have tried:- 1) = "A" and "B" and "C" 2) = "A" or "B" or "C" Tried recording a macro but still need help on criteria Don't know what else to try. Hope someone else can help. Trev "NickHK" wrote: Trev, Maybe this will help: http://www.meadinkent.co.uk/xlfilter.htm Record a macro whilst you do this to generate the code. NickHK "Trever B" wrote in message ... Hi, Thanks in advance The following code is set for Creteria1 to = "A" how do I make it for A, B or C With Worksheets("Panel Details").Range("A1:IV2500") .AutoFilter Field:=1, Criteria1:="A" .SpecialCells(xlCellTypeVisible).Copy _ Worksheets("SS A").Cells(1, 1) .AutoFilter End With Thanks again Trev |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creteria Help
Thanks Paul
Works a treat Trev " wrote: Hi Autofilter only lets you filter on two criteria at most. For 3 criteria you need advanced filter. I'll assume you are selecting your criteria from a multiselect listbox. If not, this should give you a start Private Sub OKButton_Click() Dim Grouplistdata As New Collection Dim i As Integer, GroupCount As Integer Dim Item As Variant Dim GroupCriteria As Range Application.ScreenUpdating = False With RegGroups.GroupList 'Get the data in the GroupList ListBox For i = 0 To .ListCount - 1 'for each Group in the list If .Selected(i) Then Grouplistdata.Add .List(i) End If Next i End With GroupCount = Grouplistdata.Count 'Remove any filter present With Worksheets("Panel Details") On Error Resume Next 'required if Advanced filter used .ShowAllData On Error GoTo 0 .AutoFilterMode = False 'Removes drop down arrows End With Select Case GroupCount 'Check the user hasn't clicked OK on the ViewList userform without selecting anything Case 0 Exit Sub 'nothing happens and form is still visible 'Use AutoFilter if GroupCount is 1 or 2 Case 1 Worksheets("Panel Details").Range("A1:IV2500").AutoFilter Field:=1, Criteria1:=CStr(Grouplistdata(1)) Case 2 Worksheets("Panel Details").Range("A1:IV2500").AutoFilter Field:=1, Criteria1:=CStr(Grouplistdata(1)), Operator:=xlOr, Criteria2:=CStr(Grouplistdata(2)) Case Is 2 'Start by inserting a worksheet and creating a Criteria Range to put into AdvancedFilter 'addSheet first removes the sheet, so it is always a fresh sheet AddSheet "WorkSpaceSheet" 'now the active sheet With ActiveWorkbook.Worksheets("WorkSpaceSheet") .Visible = False .Cells(1, 1).Value = "Group" 'same as heading on worksheet for "A", "B", "C",.. i = 1 'set a counter and put group items below "Group" For Each Item In Grouplistdata i = i + 1 .Cells(i, 1).Value = Item Next Item Set GroupCriteria = .Cells(1, 1).CurrentRegion End With 'worksheet 'Filter the list With Worksheets("Panel Details") .Activate .Range("A1:IV2500").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=GroupCriteria End with End Select End Sub regards Paul |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creteria Help
Hi
I forgot to insert the AddSheet macro, so it did well to work! Public Sub AddSheet(TheSheetName As String) Dim wsNew As Worksheet 'we will insert a fresh worksheet With ActiveWorkbook On Error Resume Next Application.DisplayAlerts = False .Worksheets(TheSheetName).Delete On Error GoTo 0 Application.DisplayAlerts = True 'If worksheet is not there the error in deletion is ignored 'Add a new sheet Set wsNew = .Worksheets.Add(after:=Worksheets("Panel Details")) 'Makes wsNew the active sheet wsNew.Name = TheSheetName End With Set wsNew = Nothing End Sub regards Paul |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Multiple Creteria Lookup(Between Dates) | Excel Worksheet Functions | |||
Sum data in a column if multi creteria met | Excel Worksheet Functions | |||
SUMIF with 2 creteria one Name one DAte | Excel Worksheet Functions | |||
How to use the IF function to resolved more than one creteria | Excel Worksheet Functions | |||
SUMIF with 2 creteria one Name one DAte | Excel Discussion (Misc queries) |