Macro 2010 to find words using an specific cel value

Hi, I need to write a macro that filters another sheet using that text written by the user.

If I put Rational, the macro should be able to understand that word and filter the other sheet (ALL) with that word using contains. That filed can be any word and not just "Rational".

Please your help. Thank you.

When you say 'filter', what do you mean? Do you mean locate, remove, sort or something else?

I mean, I need to use a cell that may have any word and use that information to make a filter in another tab (use the autofilter in another tab within the same excel file)

Does this help?

Please Login or Register  to view this content.

Uses A1 as the input cell. Filters on Column A of the other sheet.

Hi John,

I appreciate your help. It's working. I changed to the fields that I have in my excel file. Now the the problem that I have is that I need to include this macro inside another one (Search). Not sure if there is a way to autorun a macro inside the other.

K22 - input cell
Other Sheet - ALL (which the column E is the one that I need to use the autofilter with contains for the input cell K22)


Sub Search()
'
'Search filtering by region/role level/band/organization and role desc
'

Sheets("Capa").Select

RE = Sheets("Capa").Range("K10")
'Region

RL = Sheets("Capa").Range("K13")
'RoleLevel

BA = Sheets("Capa").Range("K16")
'Band

OG = Sheets("Capa").Range("K19")
'Organization

RD = Sheets("Capa").Range("K22")
'Role Desc

Select Case RE

Case Is = "NA": Sheets("ALL").Cells.AutoFilter Field:=14, Criteria1:= _
"NA"
Case Is = "LA": Sheets("ALL").Cells.AutoFilter Field:=14, Criteria1:= _
"LA"

Case Else
Sheets("ALL").Cells.AutoFilter Field:=14

End Select

Select Case RL

Case Is = "REP": Sheets("ALL").Cells.AutoFilter Field:=6, Criteria1:= _
"REP"
Case Is = "FLM": Sheets("ALL").Cells.AutoFilter Field:=6, Criteria1:= _
"FLM"
Case Is = "SLM & BUE": Sheets("ALL").Cells.AutoFilter Field:=6, Criteria1:= _
"SLM & BUE"

Case Else
Sheets("ALL").Cells.AutoFilter Field:=6

End Select

Select Case BA

Case Is = "6": Sheets("ALL").Cells.AutoFilter Field:=10, Criteria1:= _
"6"
Case Is = "7": Sheets("ALL").Cells.AutoFilter Field:=10, Criteria1:= _
"7"
Case Is = "8": Sheets("ALL").Cells.AutoFilter Field:=10, Criteria1:= _
"8"
Case Is = "9": Sheets("ALL").Cells.AutoFilter Field:=10, Criteria1:= _
"9"
Case Is = "10": Sheets("RD WISD").Cells.AutoFilter Field:=10, Criteria1:= _
"10"

Case Else
Sheets("ALL").Cells.AutoFilter Field:=10

End Select

Select Case OG

Case Is = "BP": Sheets("ALL").Cells.AutoFilter Field:=1, Criteria1:= _
"BP"
Case Is = "Enterprise": Sheets("ALL").Cells.AutoFilter Field:=1, Criteria1:= _
"Enterprise"
Case Is = "GBS": Sheets("ALL").Cells.AutoFilter Field:=1, Criteria1:= _
"GBS"
Case Is = "GPS": Sheets("ALL").Cells.AutoFilter Field:=1, Criteria1:= _
"GPS"
Case Is = "GTS": Sheets("ALL").Cells.AutoFilter Field:=1, Criteria1:= _
"GTS"
Case Is = "IGF": Sheets("ALL").Cells.AutoFilter Field:=1, Criteria1:= _
"IGF"
Case Is = "Industry": Sheets("ALL").Cells.AutoFilter Field:=1, Criteria1:= _
"Industry"
Case Is = "Inside Sales": Sheets("ALL").Cells.AutoFilter Field:=1, Criteria1:= _
"Inside Sales"
Case Is = "Intellectual Property": Sheets("ALL").Cells.AutoFilter Field:=1, Criteria1:= _
"Intellectual Property"
Case Is = "Mid Market": Sheets("ALL").Cells.AutoFilter Field:=1, Criteria1:= _
"Mid Market"
Case Is = "S&D Cross Support": Sheets("ALL").Cells.AutoFilter Field:=1, Criteria1:= _
"S&D Cross Support"
Case Is = "S&D Tech Sales": Sheets("ALL").Cells.AutoFilter Field:=1, Criteria1:= _
"S&D Tech Sales"
Case Is = "SCI": Sheets("ALL").Cells.AutoFilter Field:=1, Criteria1:= _
"SCI"
Case Is = "SGP": Sheets("ALL").Cells.AutoFilter Field:=1, Criteria1:= _
"SGP"
Case Is = "Software": Sheets("ALL").Cells.AutoFilter Field:=1, Criteria1:= _
"Software"
Case Is = "STG": Sheets("ALL").Cells.AutoFilter Field:=1, Criteria1:= _
"STG"
Case Else
Sheets("ALL").Cells.AutoFilter Field:=1

End Select

Sheets("ALL").Select

Select Case RD

Dim x As String
x = ActiveSheet.Range("K22").Value
Sheets("ALL").Range("E5:E" & Sheets("ALL").Range("E" & Rows.Count).End(3)(1).Row).AutoFilter 5, "=*" & x & "*"

End Select

Sheets("ALL").Select

End Sub


I've got this following message error

VBA error.JPG

This is the limits looks like, user should be able to select one option for the 4 initial limits and write a word for the last one. After that, just hit Search to check the data for the other sheet.

Excel Limits.JPG

Fabmatos.

You need to comply with Forum Rule No 3. when posting code. 3. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the [#] button at the top of the post window (if you are editing an existing post, press Go Advanced to see the [#] button).

Your last Select Case statement has no case?

Please Login or Register  to view this content.

This may be causing the error.

Hi John,

Thanks for the explanation. Yes, after reviewing I was able to get it done. Thank you so much for the help.

You're welcome. Glad to help out and thanks for the feedback.