Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Inputting criteria into a macro/vb code
I don't know if anyone can help. I need to create some code that operates
autofilter, I don't have a problem doing this: Sub TEST() Sheets("Store Data").Select Selection.AutoFilter Field:=8, Criteria1:="10", Operator:=xlTop10Items Selection.AutoFilter Field:=20, Criteria1:="30", Operator:=xlAnd Selection.AutoFilter Field:=21, Criteria1:="50", Operator:=xlAnd End Sub However, the criteria and operator will change everytime, the user will be putting the criteria and operator into cells on another sheet and I need the code to pick up this data from the cells. Plus, there will sometimes be only 1, 2 or 3 critieria, ie, not all three selections will be made everytime. Is this at all possible? Thanks in advance. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Inputting criteria into a macro/vb code
Maybe...
Sub TEST() Sheets("Store Data").Select with worksheets("sheet99") with .range("a1") if isempty(.value) then 'skip it else Selection.AutoFilter Field:=8, Criteria1:=.value, _ Operator:=xlTop10Items end if End with with .range("a2") if isempty(.value) then 'skip it else Selection.AutoFilter Field:=20, Criteria1:="" & .value end if end with with .range("a3") if isempty(.value) then 'skip it else Selection.AutoFilter Field:=21, Criteria1:="" & .value end if end with end with End Sub Maybe you can pick out something worth saving. Marie Bayes wrote: I don't know if anyone can help. I need to create some code that operates autofilter, I don't have a problem doing this: Sub TEST() Sheets("Store Data").Select Selection.AutoFilter Field:=8, Criteria1:="10", Operator:=xlTop10Items Selection.AutoFilter Field:=20, Criteria1:="30", Operator:=xlAnd Selection.AutoFilter Field:=21, Criteria1:="50", Operator:=xlAnd End Sub However, the criteria and operator will change everytime, the user will be putting the criteria and operator into cells on another sheet and I need the code to pick up this data from the cells. Plus, there will sometimes be only 1, 2 or 3 critieria, ie, not all three selections will be made everytime. Is this at all possible? Thanks in advance. -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Inputting criteria into a macro/vb code
Hi Dave
I'm not sure this is what I'm looking for, sorry if i'm being dumb, but the criteria and operator for each of the 3 autofilters will change or be empty, so the users input the criteria and operator that they require for the autofilter into a cell in sheet A, this then needs to be input into the code to automatically filter the data in "Store Data". So, using your code, if the data in "a1" is empty, that's great, but if it's not there'll be criteria and an operator in cells a1 and a2. Do you have any ideas if this can be done? Thanks in advance. "Dave Peterson" wrote: Maybe... Sub TEST() Sheets("Store Data").Select with worksheets("sheet99") with .range("a1") if isempty(.value) then 'skip it else Selection.AutoFilter Field:=8, Criteria1:=.value, _ Operator:=xlTop10Items end if End with with .range("a2") if isempty(.value) then 'skip it else Selection.AutoFilter Field:=20, Criteria1:="" & .value end if end with with .range("a3") if isempty(.value) then 'skip it else Selection.AutoFilter Field:=21, Criteria1:="" & .value end if end with end with End Sub Maybe you can pick out something worth saving. Marie Bayes wrote: I don't know if anyone can help. I need to create some code that operates autofilter, I don't have a problem doing this: Sub TEST() Sheets("Store Data").Select Selection.AutoFilter Field:=8, Criteria1:="10", Operator:=xlTop10Items Selection.AutoFilter Field:=20, Criteria1:="30", Operator:=xlAnd Selection.AutoFilter Field:=21, Criteria1:="50", Operator:=xlAnd End Sub However, the criteria and operator will change everytime, the user will be putting the criteria and operator into cells on another sheet and I need the code to pick up this data from the cells. Plus, there will sometimes be only 1, 2 or 3 critieria, ie, not all three selections will be made everytime. Is this at all possible? Thanks in advance. -- Dave Peterson |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Inputting criteria into a macro/vb code
The only operator that I saw that was necessary was in the first filter. The
others weren't needed. But you could use the same kind of code to point at any cell in any worksheet. If you mean the comparison operator ( may be = or < or <= or =), you could concatenate that, too: with .range("a2") if isempty(.value) then 'skip it else Selection.AutoFilter Field:=20, Criteria1:="" & .value end if end with Becomes: if isempty(range("a2").value) then 'skip it else Selection.AutoFilter Field:=20, _ Criteria1:=.range("A99").value & .range("a2").value 'change the addresses to what you need end if end with Marie Bayes wrote: Hi Dave I'm not sure this is what I'm looking for, sorry if i'm being dumb, but the criteria and operator for each of the 3 autofilters will change or be empty, so the users input the criteria and operator that they require for the autofilter into a cell in sheet A, this then needs to be input into the code to automatically filter the data in "Store Data". So, using your code, if the data in "a1" is empty, that's great, but if it's not there'll be criteria and an operator in cells a1 and a2. Do you have any ideas if this can be done? Thanks in advance. "Dave Peterson" wrote: Maybe... Sub TEST() Sheets("Store Data").Select with worksheets("sheet99") with .range("a1") if isempty(.value) then 'skip it else Selection.AutoFilter Field:=8, Criteria1:=.value, _ Operator:=xlTop10Items end if End with with .range("a2") if isempty(.value) then 'skip it else Selection.AutoFilter Field:=20, Criteria1:="" & .value end if end with with .range("a3") if isempty(.value) then 'skip it else Selection.AutoFilter Field:=21, Criteria1:="" & .value end if end with end with End Sub Maybe you can pick out something worth saving. Marie Bayes wrote: I don't know if anyone can help. I need to create some code that operates autofilter, I don't have a problem doing this: Sub TEST() Sheets("Store Data").Select Selection.AutoFilter Field:=8, Criteria1:="10", Operator:=xlTop10Items Selection.AutoFilter Field:=20, Criteria1:="30", Operator:=xlAnd Selection.AutoFilter Field:=21, Criteria1:="50", Operator:=xlAnd End Sub However, the criteria and operator will change everytime, the user will be putting the criteria and operator into cells on another sheet and I need the code to pick up this data from the cells. Plus, there will sometimes be only 1, 2 or 3 critieria, ie, not all three selections will be made everytime. Is this at all possible? Thanks in advance. -- Dave Peterson -- Dave Peterson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Inputting criteria into a macro/vb code
Thanks Dave
This works a treat, I had to change it slightly (take the . from before range): Selection.AutoFilter Field:=7, Criteria1:=(Range("Sheet3!F12").Value) & (Range("Sheet3!g12").Value) Thanks v much "Dave Peterson" wrote: The only operator that I saw that was necessary was in the first filter. The others weren't needed. But you could use the same kind of code to point at any cell in any worksheet. If you mean the comparison operator ( may be = or < or <= or =), you could concatenate that, too: with .range("a2") if isempty(.value) then 'skip it else Selection.AutoFilter Field:=20, Criteria1:="" & .value end if end with Becomes: if isempty(range("a2").value) then 'skip it else Selection.AutoFilter Field:=20, _ Criteria1:=.range("A99").value & .range("a2").value 'change the addresses to what you need end if end with Marie Bayes wrote: Hi Dave I'm not sure this is what I'm looking for, sorry if i'm being dumb, but the criteria and operator for each of the 3 autofilters will change or be empty, so the users input the criteria and operator that they require for the autofilter into a cell in sheet A, this then needs to be input into the code to automatically filter the data in "Store Data". So, using your code, if the data in "a1" is empty, that's great, but if it's not there'll be criteria and an operator in cells a1 and a2. Do you have any ideas if this can be done? Thanks in advance. "Dave Peterson" wrote: Maybe... Sub TEST() Sheets("Store Data").Select with worksheets("sheet99") with .range("a1") if isempty(.value) then 'skip it else Selection.AutoFilter Field:=8, Criteria1:=.value, _ Operator:=xlTop10Items end if End with with .range("a2") if isempty(.value) then 'skip it else Selection.AutoFilter Field:=20, Criteria1:="" & .value end if end with with .range("a3") if isempty(.value) then 'skip it else Selection.AutoFilter Field:=21, Criteria1:="" & .value end if end with end with End Sub Maybe you can pick out something worth saving. Marie Bayes wrote: I don't know if anyone can help. I need to create some code that operates autofilter, I don't have a problem doing this: Sub TEST() Sheets("Store Data").Select Selection.AutoFilter Field:=8, Criteria1:="10", Operator:=xlTop10Items Selection.AutoFilter Field:=20, Criteria1:="30", Operator:=xlAnd Selection.AutoFilter Field:=21, Criteria1:="50", Operator:=xlAnd End Sub However, the criteria and operator will change everytime, the user will be putting the criteria and operator into cells on another sheet and I need the code to pick up this data from the cells. Plus, there will sometimes be only 1, 2 or 3 critieria, ie, not all three selections will be made everytime. Is this at all possible? Thanks in advance. -- Dave Peterson -- Dave Peterson |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Inputting criteria into a macro/vb code
I bet you dropped that top: With Worksheets("Sheet99")
I didn't know the name of the sheet that held the info (or what cells), so I just used Sheet99 and A1, ... You could have used: with worksheets("sheet3") '... selection.autofilter field:=7, _ criterial1:=.range("F12").value, & .range("G12").value I find this syntax: worksheets("sheet3").range("F12").value more robust than range("sheet3!F12").value The bottom version may not work in all situations (like behind a worksheet module). Marie Bayes wrote: Thanks Dave This works a treat, I had to change it slightly (take the . from before range): Selection.AutoFilter Field:=7, Criteria1:=(Range("Sheet3!F12").Value) & (Range("Sheet3!g12").Value) Thanks v much "Dave Peterson" wrote: The only operator that I saw that was necessary was in the first filter. The others weren't needed. But you could use the same kind of code to point at any cell in any worksheet. If you mean the comparison operator ( may be = or < or <= or =), you could concatenate that, too: with .range("a2") if isempty(.value) then 'skip it else Selection.AutoFilter Field:=20, Criteria1:="" & .value end if end with Becomes: if isempty(range("a2").value) then 'skip it else Selection.AutoFilter Field:=20, _ Criteria1:=.range("A99").value & .range("a2").value 'change the addresses to what you need end if end with Marie Bayes wrote: Hi Dave I'm not sure this is what I'm looking for, sorry if i'm being dumb, but the criteria and operator for each of the 3 autofilters will change or be empty, so the users input the criteria and operator that they require for the autofilter into a cell in sheet A, this then needs to be input into the code to automatically filter the data in "Store Data". So, using your code, if the data in "a1" is empty, that's great, but if it's not there'll be criteria and an operator in cells a1 and a2. Do you have any ideas if this can be done? Thanks in advance. "Dave Peterson" wrote: Maybe... Sub TEST() Sheets("Store Data").Select with worksheets("sheet99") with .range("a1") if isempty(.value) then 'skip it else Selection.AutoFilter Field:=8, Criteria1:=.value, _ Operator:=xlTop10Items end if End with with .range("a2") if isempty(.value) then 'skip it else Selection.AutoFilter Field:=20, Criteria1:="" & .value end if end with with .range("a3") if isempty(.value) then 'skip it else Selection.AutoFilter Field:=21, Criteria1:="" & .value end if end with end with End Sub Maybe you can pick out something worth saving. Marie Bayes wrote: I don't know if anyone can help. I need to create some code that operates autofilter, I don't have a problem doing this: Sub TEST() Sheets("Store Data").Select Selection.AutoFilter Field:=8, Criteria1:="10", Operator:=xlTop10Items Selection.AutoFilter Field:=20, Criteria1:="30", Operator:=xlAnd Selection.AutoFilter Field:=21, Criteria1:="50", Operator:=xlAnd End Sub However, the criteria and operator will change everytime, the user will be putting the criteria and operator into cells on another sheet and I need the code to pick up this data from the cells. Plus, there will sometimes be only 1, 2 or 3 critieria, ie, not all three selections will be made everytime. Is this at all possible? Thanks in advance. -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Inputting numbers using a macro. | Excel Discussion (Misc queries) | |||
Deleting code from a macro (by a macro) | Excel Discussion (Misc queries) | |||
Code help, delete rows based on column criteria | Excel Discussion (Misc queries) | |||
inputting data | Excel Discussion (Misc queries) | |||
Problem with criteria when using it from VBA Code | Excel Worksheet Functions |