| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel macro filter Like keyword
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 -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/201003/1 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Excel macro filter Like keyword
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" wrote: 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 -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/201003/1 -- Dave Peterson |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Excel macro filter Like keyword
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. Dave Peterson wrote: 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.a...mming/201003/1 -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/201003/1 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Excel macro filter Like keyword
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. "Jeff via OfficeKB.com" wrote: 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. Dave Peterson wrote: 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.a...mming/201003/1 -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/201003/1 -- Dave Peterson |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Excel macro filter Like keyword
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 "Jeff via OfficeKB.com" wrote: 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. Dave Peterson wrote: 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.a...mming/201003/1 -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/201003/1 -- Dave Peterson |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Excel macro filter Like keyword
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 Jeff wrote: 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] Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/201003/1 -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/201003/1 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Excel macro filter Like keyword
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 Jeff wrote: 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] Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/201003/1 -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/201003/1 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Excel macro filter Like keyword
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 Jeff wrote: 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.a...mming/201003/1 -- Message posted via http://www.officekb.com |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Excel macro filter Like keyword
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 "jfsauzeat129 via OfficeKB.com" wrote: 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 Jeff wrote: 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.a...mming/201003/1 -- Message posted via http://www.officekb.com -- Dave Peterson |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Excel macro filter Like keyword
Dave,
Thanks a lot for your help! Be sure that I will try your suggestions. Dave Peterson wrote: 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] -- Message posted via http://www.officekb.com -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/201003/1 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Macro that concatenates content from cells if given keyword is found | Excel Programming | |||
| Selecting a Keyword for Macro: | Excel Worksheet Functions | |||
| Macro - reads cells in a column .If keyword found moves cell conte | Excel Programming | |||
| Macro searches for keyword. If found , puts 0 in cell from next co | Excel Programming | |||
| Filter list in place if a cell has a keyword within a formula | Excel Programming | |||
Linear Mode
