PDA

View Full Version : Autofilter based on content in cells



Jo83
03-23-2014, 02:22 PM
Hello.

I hope someone is able to help me.

I have a UserForm with 2 textBoxes where it is possible enter name of person or a city.

I am trying to make a code with an Autofilter that based on entered information will filter my sheet:

- If name of person is entered, it will search for the initials in column Q, and show only rows with such names.
- If name of city is entered, it will search for the name in column L, and show only rows with such names.

That will happen after someone presses "OK" button on the Userform.

Anyone is able to help??

Best
Jo

D_Marcel
03-24-2014, 06:08 AM
Hello Jo, welcome to VBAX!

Please, let me know if this helps you:

Private Sub CommandButton1_Click()


Dim RNG As Range
Dim SH As Worksheet


Set SH = Sheets("MySheet")
Set RNG = SH.Range(Cells(1, 1), Cells(Cells(Rows.Count, 1).End(xlUp).Row, 2))


With RNG
.AutoFilter
.AutoFilter Field:=1, Criteria1:=TextBox1.Value
End With


End Sub

In RNG, you can set the area that will be filter according to your sheet, in this case L and Q columns.
With RNG, you'll filter according to the value of the TextBox.

Have a nice week,

Douglas

D_Marcel
03-24-2014, 06:14 AM
To better understanding, I'm sharing a sample.

Jo83
03-24-2014, 06:25 AM
Hello Dauglas!

Thank you so much. Looks great.

I have just one more criteria that came up today. When someone inserts personīs initials then the filter displays cells that include ONLY these initials. But is possible to have a code that display cells that contain ALSO these initials.

E.g. once cell may contain "LGD; DKE; SER". and if someone enters the initials of LGD, then the filter should also display this raw.

Is it possible to achive?

thank you again
Joanna

D_Marcel
03-24-2014, 07:01 AM
Sincerely I don't know if we can use a "Like" operator in the syntax of the AutoFilter method, so I thought in this, Joanna:

Private Sub CommandButton1_Click()

Dim RNG As Range
Dim SH As Worksheet


Set SH = Sheets("MySheet")
Set RNG = SH.Range(Cells(1, 1), Cells(Cells(Rows.Count, 1).End(xlUp).Row, 2))


With RNG
Set Target = .Find(TextBox1.Value)
If Not Target Is Nothing Then
Location = Target.Address
With RNG
.AutoFilter
.AutoFilter Field:=1, Criteria1:=Range(Location).Value
End With
Else
MsgBox "Not found!!"
End If
End With


End Sub

Using the .Find method, we can search the content of the TextBox, get its address in the Worksheet and the get the value of this address to be used as criteria in the AutoFilter.

Let me know if it works for you.

Douglas

Jo83
03-24-2014, 10:41 AM
Hello.

I have inserted the new code in your Excel sheet and modified the example a little. Now your excel looks as follows:



Name


Douglas


Bryan


Eileen


Robert


Mark; Bryan



When i search for "Mark" then it returns the cell where both Mark and Bryan is included. However, when i search for "Bryan", then it returns only the cell when "Bryan" stands alone.
So it does not work totally well :-(

Joanna

D_Marcel
03-24-2014, 11:50 AM
Sorry, my fault. I did not understand exactly your information, thinking that you only would have one occurrence.

Well, in this case, guess we can use this:

Private Sub CommandButton1_Click()

Dim RNG As Range
Dim SH As Worksheet

Set SH = Sheets("MySheet")
Set RNG = SH.Range(Cells(1, 1), Cells(Cells(Rows.Count, 1).End(xlUp).Row, 2))

With RNG
Set Target = .Find(TextBox1.Value)
If Not Target Is Nothing Then
Location = Target.Address
With RNG
.AutoFilter
.AutoFilter Field:=1, Criteria1:="*" & TextBox1.Value & "*"
End With
Else
MsgBox "Not found!!"
End If
End With

End Sub

Now, for example, we have:

Name
Douglas ; Joanna
Bryan
Eileen
Robert ; Joanna
Mark

Typing 'Joanna' on the TextBox and pressing the button, we'll have:



Douglas ; Joanna


Robert ; Joanna

Give it a try.

Douglas