Excel macro filter Like keyword

[Jeff via OfficeKB.com]

I am trying to program a filter so that it searches a keyword among a string
of characters in my first column
I used a combo to feed a linked cell. I have the following code:

Private Sub ComboBox1_Change()

SourceRange.AutoFilter Field:=1, Criteria1:="Like & " * "& A1 & " * ""

End Sub

I have a type mismatch error.

Any help would be great!

My criterion (linked) cell is A1

Thanks

 

[Dave Peterson]

Is A1 on the same worksheet as the sourcerange?

with SourceRange
.AutoFilter Field:=1, Criteria1:="*" & .parent.range("A1").value & "*"
End with

The parent of sourcerange is the worksheet that owns it.

 

[Jeff via OfficeKB.com]

Thanks a lot for the help
I am still getting an "object required" error 424
All the data, combo box and source range in on the same worksheet.
In cell A1, I have the keyword cell (which is the linked cell for my combo
box)
My headers start on A2 to AA2 and have autofilter set up, the data itself
starts cell A3...
I would like that when I select the correct value from the combobox, it
filters my data by searching for a matching keyword in column 1.
I hope this is more clear.

Thanks for the help.

Is A1 on the same worksheet as the sourcerange?

with SourceRange
.AutoFilter Field:=1, Criteria1:="*" & .parent.range("A1").value & "*"
End with

The parent of sourcerange is the worksheet that owns it.

I am trying to program a filter so that it searches a keyword among a string
of characters in my first column

[quoted text clipped - 17 lines]

Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.aspx/excel-programming/201003/1

 

[Dave Peterson]

What's SourceRange?

It looked like you defined it somewhere else and didn't share it with us.

It may be time to share all the relevant code.

Thanks a lot for the help
I am still getting an "object required" error 424
All the data, combo box and source range in on the same worksheet.
In cell A1, I have the keyword cell (which is the linked cell for my combo
box)
My headers start on A2 to AA2 and have autofilter set up, the data itself
starts cell A3...
I would like that when I select the correct value from the combobox, it
filters my data by searching for a matching keyword in column 1.
I hope this is more clear.

Thanks for the help.

Is A1 on the same worksheet as the sourcerange?

with SourceRange
.AutoFilter Field:=1, Criteria1:="*" & .parent.range("A1").value & "*"
End with

The parent of sourcerange is the worksheet that owns it.

I am trying to program a filter so that it searches a keyword among a string
of characters in my first column

[quoted text clipped - 17 lines]

Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.aspx/excel-programming/201003/1

 

[Dave Peterson]

ps.

If you defined a worksheet name "sourcerange" (insert|name define in xl2003
menus), then maybe...

with me.range("SourceRange")
.AutoFilter Field:=1, Criteria1:="*" & .parent.range("A1").value & "*"
End with

Thanks a lot for the help
I am still getting an "object required" error 424
All the data, combo box and source range in on the same worksheet.
In cell A1, I have the keyword cell (which is the linked cell for my combo
box)
My headers start on A2 to AA2 and have autofilter set up, the data itself
starts cell A3...
I would like that when I select the correct value from the combobox, it
filters my data by searching for a matching keyword in column 1.
I hope this is more clear.

Thanks for the help.

Is A1 on the same worksheet as the sourcerange?

with SourceRange
.AutoFilter Field:=1, Criteria1:="*" & .parent.range("A1").value & "*"
End with

The parent of sourcerange is the worksheet that owns it.

I am trying to program a filter so that it searches a keyword among a string
of characters in my first column

[quoted text clipped - 17 lines]

Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.aspx/excel-programming/201003/1

 

[jfsauzeat129 via OfficeKB.com]

SourceRange must be a leftover from some code I grabbed somewhere. I left it
in because of my misunderstanding of VBA. (Ithought it was some kind of build
it function)

I tried another approach (still not quite working). I recorded a macro while
doing a custom filtering and used a select case to filter the different
options.
Here is my code..


Private Sub ComboBox1_Change()

Dim ProcessType As String
ProcessType = Range("A1").Value

Select Case ProcessType

Case Is = "BL"
Selection.AutoFilter Field:=1, Criteria1:="=*Blended*", Operator:=xlAnd

Case Is = "DA"
Selection.AutoFilter Field:=1, Criteria1:="=*DA*", Operator:=xlAnd

Case Is = "TRD"
Selection.AutoFilter Field:=1, Criteria1:="=*Traditional*", Operator:=xlAnd
End Select

End Sub

Thanks a lot for the help
I am still getting an "object required" error 424
All the data, combo box and source range in on the same worksheet.
In cell A1, I have the keyword cell (which is the linked cell for my combo
box)
My headers start on A2 to AA2 and have autofilter set up, the data itself
starts cell A3...
I would like that when I select the correct value from the combobox, it
filters my data by searching for a matching keyword in column 1.
I hope this is more clear.

Thanks for the help.

Is A1 on the same worksheet as the sourcerange?

[quoted text clipped - 9 lines]

 

[Jeff via OfficeKB.com]

SourceRange must be a leftover from some code I grabbed somewhere. I left it
in because of my misunderstanding of VBA. (Ithought it was some kind of build
it function)

I tried another approach (still not quite working). I recorded a macro while
doing a custom filtering and used a select case to filter the different
options.
Here is my code..


Private Sub ComboBox1_Change()

Dim ProcessType As String
ProcessType = Range("A1").Value

Select Case ProcessType

Case Is = "BL"
Selection.AutoFilter Field:=1, Criteria1:="=*Blended*", Operator:=xlAnd

Case Is = "DA"
Selection.AutoFilter Field:=1, Criteria1:="=*DA*", Operator:=xlAnd

Case Is = "TRD"
Selection.AutoFilter Field:=1, Criteria1:="=*Traditional*", Operator:=xlAnd
End Select

End Sub

Thanks a lot for the help
I am still getting an "object required" error 424
All the data, combo box and source range in on the same worksheet.
In cell A1, I have the keyword cell (which is the linked cell for my combo
box)
My headers start on A2 to AA2 and have autofilter set up, the data itself
starts cell A3...
I would like that when I select the correct value from the combobox, it
filters my data by searching for a matching keyword in column 1.
I hope this is more clear.

Thanks for the help.

Is A1 on the same worksheet as the sourcerange?

[quoted text clipped - 9 lines]

 

[jfsauzeat129 via OfficeKB.com]

Ok,
By ignoring the error as per the code below, it works....


Private Sub ComboBox1_Change()
On Error Resume Next

Dim ProcessType As String
ProcessType = Range("A1").Value

Select Case ProcessType

Case Is = "BL"
Selection.AutoFilter Field:=1, Criteria1:="=*BL*", Operator:=xlAnd

Case Is = "DA"
Selection.AutoFilter Field:=1, Criteria1:="=*DA*", Operator:=xlAnd

Case Is = "TRD"
Selection.AutoFilter Field:=1, Criteria1:="=*TRD*", Operator:=xlAnd
End Select

End Sub

SourceRange must be a leftover from some code I grabbed somewhere. I left it
in because of my misunderstanding of VBA. (Ithought it was some kind of build
it function)

I tried another approach (still not quite working). I recorded a macro while
doing a custom filtering and used a select case to filter the different
options.
Here is my code..

Private Sub ComboBox1_Change()

Dim ProcessType As String
ProcessType = Range("A1").Value

Select Case ProcessType

Case Is = "BL"
Selection.AutoFilter Field:=1, Criteria1:="=*Blended*", Operator:=xlAnd

Case Is = "DA"
Selection.AutoFilter Field:=1, Criteria1:="=*DA*", Operator:=xlAnd

Case Is = "TRD"
Selection.AutoFilter Field:=1, Criteria1:="=*Traditional*", Operator:=xlAnd
End Select

End Sub

Thanks a lot for the help
I am still getting an "object required" error 424

[quoted text clipped - 14 lines]

 

[Dave Peterson]

How about if you're only checking to see if the value in the combobox is
included in the text:


Option Explicit
Private Sub ComboBox1_Change()

Dim ProcessType As String

ProcessType = me.Range("A1").Value

if processtype = "" then
exit sub
end if

selection.autofilter field:=1, criteria:="*" & processtype & "*"

End sub

Personally, I wouldn't use Selection. What happens if your selection isn't in
the correct range?

I'd be more explicit:

Option Explicit
Private Sub ComboBox1_Change()

Dim ProcessType As String
dim myRng as range
Dim LastRow as long
Dim LastCol as long

ProcessType = me.Range("A1").Value

if processtype = "" then
exit sub
end if

with me
lastrow = .cells(.rows.count,"A").end(xlup).row
lastcol = .cells(2, .columns.count).end(xltoleft).column
set myrng = .range("a2",.cells(lastrow,lastcol))

'show all the data
If .FilterMode Then
.ShowAllData
End If

myrng.autofilter field:=1, criteria:="*" & processtype & "*"

End sub

Ok,
By ignoring the error as per the code below, it works....

Private Sub ComboBox1_Change()
On Error Resume Next

Dim ProcessType As String
ProcessType = Range("A1").Value

Select Case ProcessType

Case Is = "BL"
Selection.AutoFilter Field:=1, Criteria1:="=*BL*", Operator:=xlAnd

Case Is = "DA"
Selection.AutoFilter Field:=1, Criteria1:="=*DA*", Operator:=xlAnd

Case Is = "TRD"
Selection.AutoFilter Field:=1, Criteria1:="=*TRD*", Operator:=xlAnd
End Select

End Sub

SourceRange must be a leftover from some code I grabbed somewhere. I left it
in because of my misunderstanding of VBA. (Ithought it was some kind of build
it function)

I tried another approach (still not quite working). I recorded a macro while
doing a custom filtering and used a select case to filter the different
options.
Here is my code..

Private Sub ComboBox1_Change()

Dim ProcessType As String
ProcessType = Range("A1").Value

Select Case ProcessType

Case Is = "BL"
Selection.AutoFilter Field:=1, Criteria1:="=*Blended*", Operator:=xlAnd

Case Is = "DA"
Selection.AutoFilter Field:=1, Criteria1:="=*DA*", Operator:=xlAnd

Case Is = "TRD"
Selection.AutoFilter Field:=1, Criteria1:="=*Traditional*", Operator:=xlAnd
End Select

End Sub

Thanks a lot for the help
I am still getting an "object required" error 424

[quoted text clipped - 14 lines]

Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.aspx/excel-programming/201003/1

 

[Jeff via OfficeKB.com]

Dave,

Thanks a lot for your help!
Be sure that I will try your suggestions.

How about if you're only checking to see if the value in the combobox is
included in the text:

Option Explicit
Private Sub ComboBox1_Change()

Dim ProcessType As String

ProcessType = me.Range("A1").Value

if processtype = "" then
exit sub
end if

selection.autofilter field:=1, criteria:="*" & processtype & "*"

End sub

Personally, I wouldn't use Selection. What happens if your selection isn't in
the correct range?

I'd be more explicit:

Option Explicit
Private Sub ComboBox1_Change()

Dim ProcessType As String
dim myRng as range
Dim LastRow as long
Dim LastCol as long

ProcessType = me.Range("A1").Value

if processtype = "" then
exit sub
end if

with me
lastrow = .cells(.rows.count,"A").end(xlup).row
lastcol = .cells(2, .columns.count).end(xltoleft).column
set myrng = .range("a2",.cells(lastrow,lastcol))

'show all the data
If .FilterMode Then
.ShowAllData
End If

myrng.autofilter field:=1, criteria:="*" & processtype & "*"

End sub

Ok,
By ignoring the error as per the code below, it works....

[quoted text clipped - 55 lines]