Trying to modify an macros

[teodormircea]

Hello every one

I'm tring to modify an macros to add more combobox to have more columns to filter and criteria.
I found this macros her, but i'm not so good modifying it.
Her the userform code:

Option Explicit

Private Sub CommandButton1_Click()
Dim rng As Range
Dim ctrl, ctrl1 As MSForms.Control
Dim Field As String

Field = ComboBox1.ListIndex + 1
Field = ComboBox2.ListIndex + 1
Field = ComboBox3.ListIndex + 1

'Set Error Handling
On Error GoTo ws_exit:
Application.EnableEvents = False

'Set Range
Set rng = ActiveSheet.UsedRange

For Each ctrl In UserForm1.Controls




If Left(ctrl.Name, 4) = "Text" Then
If Left(ctrl1.Name, 4) = "Text" Then
If ctrl.Value And ctrl1.Value <> "" Then
CreateSheet ctrl.Value, ctrl1.Value
FilterAndCopy rng, ctrl.Value, Field
FilterAndCopy1 rng, ctrl1.Value, Field
rng.AutoFilter

End If
End If
End If
Next
Unload Me
Exit Sub
ws_exit:
Set rng = Nothing
Application.EnableEvents = True
Unload Me
End Sub

Private Sub CommandButton2_Click()
'Cancel Button
Unload Me
End Sub




Private Sub UserForm_Initialize()

Dim FillRange As Range
Dim Cel As Range
Dim iLastRow As Long
Dim iLastColumn As Long

'Find Last Row
iLastRow = 1
'Find Last Column
iLastColumn = Cells(1, Columns.Count).End(xlToLeft).Column
'Set Range from A1 to Last Row/Column
Set FillRange = Range("A1", Cells(iLastRow, iLastColumn))

For Each Cel In FillRange
Me.ComboBox1.AddItem Cel.Text
Me.ComboBox2.AddItem Cel.Text
Me.ComboBox3.AddItem Cel.Text
Next

ComboBox1.ListIndex = 0
ComboBox2.ListIndex = 0
ComboBox3.ListIndex = 0


Set Cel = Nothing
Set FillRange = Nothing

End Sub


and the module code



Sub formshow()
'Show Search Form
UserForm1.Show
End Sub
Function FilterAndCopy(rng As Range, Choice As String, Field As String)

Dim FiltRng As Range
Worksheets(Choice).Cells.ClearContents
rng.AutoFilter Field:=Field, Criteria1:=Choice

On Error Resume Next
Set FiltRng = rng.SpecialCells(xlCellTypeVisible).EntireRow
On Error GoTo 0
FiltRng.Copy Worksheets(Choice).Range("A1")

Set FiltRng = Nothing

End Function
Function FilterAndCopy1(rng As Range, Choice1 As String, Field As String)

Dim FiltRng As Range
Worksheets(Choice).Cells.ClearContents
rng.AutoFilter Field:=Field, Criteria2:=Choice1

On Error Resume Next
Set FiltRng = rng.SpecialCells(xlCellTypeVisible).EntireRow
On Error GoTo 0
FiltRng.Copy Worksheets(Choice1).Range("B1")


Set FiltRng = Nothing

End Function
Function CreateSheet(Choice, Choice1 As String)

Dim NewSheet As Worksheet

On Error GoTo Err:
Worksheets(Choice).Select
Exit Function

Err:
Set NewSheet = Worksheets.Add
On Error Resume Next
NewSheet.Name = Choice + Choice1
On Error GoTo 0
End Function

also i attached the example file.
I will appreciate your help

[teodormircea]

Here what i've dine so far , but i'm lost

[GTO]

teodormirccea,

Thank you for attaching an example workbook. Please list your goals completely, as your code has some 'issues'. Oner is either learning, or losing info throughout life, so this is not a 'bad thing'. I just need some details to assist you.

Mark

Private Sub CommandButton1_Click()
Dim rng As Range
'// 'ctrl' is not dimensioned as a control just to bring this to your attention. EX://
'// "Dim strOne, strTwo as String" //
'// ..results in 'strOne' being dimensioned as a variant and 'strTwo' being //
'// dimensioned as a String. You need to specify ea variable, like: //
'// ctrl As MSforms.Control, ctrl1 As MSForms.Control //
Dim ctrl, ctrl1 As MSForms.Control
Dim Field As String
'// Okay, you dim'd 'Field' as a String, but it refers to a ListIndex value; a number.//
'// Then, 'Field' is only one (non array) variable, so it can only hold one value at //
'// a time. Here, you assign it to the return of the list index (+1) of combo box one, //
'// but you end up setting it to the val of.....combo box three. //
Field = ComboBox1.ListIndex + 1
Field = ComboBox2.ListIndex + 1
Field = ComboBox3.ListIndex + 1
'Set Error Handling
On Error GoTo ws_exit:
'// Here, events are disabled, but presuming no errors, they are never re-enabled //
Application.EnableEvents = False
'Set Range
Set rng = ActiveSheet.UsedRange
For Each ctrl In UserForm1.Controls
If Left(ctrl.Name, 4) = "Text" Then
'// 'ctrl' can return a value for the name property, but 'ctrl1' cannot, as //
'// 'ctrl1' is never identified/selected as an individual control. //
If Left(ctrl1.Name, 4) = "Text" Then
If ctrl.Value And ctrl1.Value <> "" Then
CreateSheet ctrl.Value, ctrl1.Value
FilterAndCopy rng, ctrl.Value, Field
FilterAndCopy1 rng, ctrl1.Value, Field
rng.AutoFilter

End If
End If
End If
Next
Unload Me
Exit Sub
ws_exit:
Set rng = Nothing
'// Note again, that if no error occurred, this is never reached. //
Application.EnableEvents = True
Unload Me
End Sub

[teodormircea]

I'm learning, so the best way is to adapt a code to other needs.
I found the code here on this forum.
Here I'm gone attach the original file.
Basically i understood how the code works.

What the inital code do;

I can choose a column by header and then apply criteria(s) to this column, a new sheet is created with the name of the criteria.

What i'm trying to do now , add a new combobox, that's no difficult to do,in order to make a second filter search.
BUT THE SECOND FILTER HAVE TO WORK ON THE RESULTS OF THE FIRST ONE, LIKE AN AUTO FILTER IN EXCEL.
THEN CREATE A NEW SHEET WITH THE NAME OF CRITERIA.
I HOPE I WAS CLEAR.

[GTO]

I'm learning, so the best way is to adapt a code to other needs.
I found the code here on this forum.
Here I'm gone attach the original file.
Basically i understood how the code works.

What the inital code do;

I can choose a column by header and then apply criteria(s) to this column, a new sheet is created with the name of the criteria.

What i'm trying to do now , add a new combobox, that's no difficult to do,in order to make a second filter search.
BUT THE SECOND FILTER HAVE TO WORK ON THE RESULTS OF THE FIRST ONE, LIKE AN AUTO FILTER IN EXCEL.
THEN CREATE A NEW SHEET WITH THE NAME OF CRITERIA.
I HOPE I WAS CLEAR.

Okay, I don't normally use filtering, but after reading thru the original, believe I have a grasp on what it currently does.

That said - as to adding a combo box, and presumably more text boxes, were you looking to do this programatically? For instance, did you want a button on the form, that if selected, adds another combo box and text boxes? Or did you just want to know how to add controls to the form that would be usable for the deired results?

[teodormircea]

In case if a button to add a new combobox and texbox, in the userform, the 2nd filtering has to be done on the result of the first filtering.

[GTO]

Okay - I didn't add a button, but added some cheesy code to the <Search> button.

I also included a second file that shows the result of a 'primary' filtering, as you mention that you'd like the additional filter to be based on the results of the first filtering.

Now I don't understand what there would be to filter? Whether the 'primary' filter returns all cases of a given letter or all cases of a given number, what would the secondary filter return? If you can show me what I'm missing, maybe we could come up with a better way to 'add' or reassign the return of the various text/combo box(es).

Mark

[teodormircea]

HERE in details what i want to make

iN MY EXAMPLE.


i WANT TO FIN DATA ON THE COLUMN Letter, i choose like criteria B(normally in an auto filter i will have like result
all value that contain B in column Letter), then i want to find in column NUMBER all B (from column Letter) that have only 5 like criteria in NUMBER
and make a new sheet with these results, named B_5(all criterias used)
Here is an ex with only 2 columns but it can be 3 or 4

I hope i was more clear this time, sorry if i don't made my self clear at the beginning
I attached the file with this example

[GTO]

Oh it's not really you, more of a "Doh!" on me; now I get it!

As its awful late here, I'll check the post tomorrow, and if not answered, will try coming up with a nifty way of either doing both in one fell swoop or adding the second option...

Mark

[teodormircea]

Don't forget me PLS !!
:beerchug:

[GTO]

Greetings Teodormircea,

I have not forgotten; I thought I PM'd you. Will try again leter this afternoon, activities permitting.

In the meantime and to all: There's lots o' folks plenty more gifted here than me. By chance, does anyone have a ready-made example of multi-level filtering? (see attached at #8 for an example of filtering by Lucas)

Mark

[GTO]

Greetings and Salutations Teodormircea :hi:

Hopefully this is 'close' to what you were looking to learn/do. I wasn't absolutely sure as to whether you were looking to create only one sheet per 'filtering', or two new sheets, if there is a secondary filter happening. I elected to have it only create one, as with five primary combo boxes (down from ten for example purposes), you can 'mix-and-match' the filtering so-to-speak.

You will note that the code looks a bit different, as even in this rudimentary effort, I needed to change course a wee bit. I did mark up the code some, hopefully well enough to show what's going on.

Hope this helps ya,

Mark

[teodormircea]

It is great,thanks for your help, is an other way, but is working.
Question: it is normal to still have a filter in the result sheet? I think it will be simple to have a result values of the filter in the new sheet.I will look at the code to understand .
Thanks a lot for your time
:cloud9:

[teodormircea]

I sow the code.I sow that the columns are configured in advance.The sheet is only an example, that's mean that i can apply this macro to other sheets with different columns name.Also,the number of columns can be more then.