Home |
Search |
Today's Posts |
#1
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 |
#2
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 |
#3
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. |
#4
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 |
#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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Inputting criteria into a macro/vb code
Yes I did. I couldn't get it to work at all with the 'with' in, but that was
probably just me being a bit dumb. Now it gets more interesting as I now have to find a way to have the 3 options, ie, the user has the choice to fill in 3 criteria (F12, F13, F14) and can fill in any one, two or all three of the criteria, this is what I have so far (don't laugh at my clumsy coding): If IsEmpty(Range("F12").Value) Then With Worksheets("Store Data") Sheets("Store Data").Select If .FilterMode Then .ShowAllData End If End With Else Sheets("Store Data").Select If Sheets("Store data").FilterMode Then Sheets("Store data").ShowAllData Selection.AutoFilter Field:=7, Criteria1:=(Range("Sheet3!F12").Value) & (Range("Sheet3!g12").Value) Else Selection.AutoFilter Field:=7, Criteria1:=(Range("Sheet3!F12").Value) & (Range("Sheet3!g12").Value) End If End If End Sub Any ideas??? "Dave Peterson" wrote: 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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Inputting criteria into a macro/vb code
That's what I tried in my first response:
Sub TEST() 'Show all the data on "Store Data" with Sheets("Store Data") .Select if .filtermode then .showalldata end if end with with worksheets("sheet3") with .range("f12") if isempty(.value) then 'skip it else Selection.AutoFilter Field:=7, _ Criteria1:=.value & .offset(0,1).value end if end with with .range("f13") if isempty(.value) then 'skip it else Selection.AutoFilter Field:=8888, _ Criteria1:=.value & .offset(0,1).value end if end with with .range("f14") if isempty(.value) then 'skip it else Selection.AutoFilter Field:=9999, _ Criteria1:=.value & .offset(0,1).value end if end with end with End Sub I don't know what fields F13 and F14 would control. I used 8888 and 9999. That's probably not correct! ..offset(0,1) is one column to the right of the cell refered to in the With statement. F12&G12, F13&G13, and so on. Marie Bayes wrote: Yes I did. I couldn't get it to work at all with the 'with' in, but that was probably just me being a bit dumb. Now it gets more interesting as I now have to find a way to have the 3 options, ie, the user has the choice to fill in 3 criteria (F12, F13, F14) and can fill in any one, two or all three of the criteria, this is what I have so far (don't laugh at my clumsy coding): If IsEmpty(Range("F12").Value) Then With Worksheets("Store Data") Sheets("Store Data").Select If .FilterMode Then .ShowAllData End If End With Else Sheets("Store Data").Select If Sheets("Store data").FilterMode Then Sheets("Store data").ShowAllData Selection.AutoFilter Field:=7, Criteria1:=(Range("Sheet3!F12").Value) & (Range("Sheet3!g12").Value) Else Selection.AutoFilter Field:=7, Criteria1:=(Range("Sheet3!F12").Value) & (Range("Sheet3!g12").Value) End If End If End Sub Any ideas??? "Dave Peterson" wrote: 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 -- Dave Peterson |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Inputting criteria into a macro/vb code
Dave, you're an absolute genius, thanks once again for your invaluable aid.
Marie "Dave Peterson" wrote: That's what I tried in my first response: Sub TEST() 'Show all the data on "Store Data" with Sheets("Store Data") .Select if .filtermode then .showalldata end if end with with worksheets("sheet3") with .range("f12") if isempty(.value) then 'skip it else Selection.AutoFilter Field:=7, _ Criteria1:=.value & .offset(0,1).value end if end with with .range("f13") if isempty(.value) then 'skip it else Selection.AutoFilter Field:=8888, _ Criteria1:=.value & .offset(0,1).value end if end with with .range("f14") if isempty(.value) then 'skip it else Selection.AutoFilter Field:=9999, _ Criteria1:=.value & .offset(0,1).value end if end with end with End Sub I don't know what fields F13 and F14 would control. I used 8888 and 9999. That's probably not correct! ..offset(0,1) is one column to the right of the cell refered to in the With statement. F12&G12, F13&G13, and so on. Marie Bayes wrote: Yes I did. I couldn't get it to work at all with the 'with' in, but that was probably just me being a bit dumb. Now it gets more interesting as I now have to find a way to have the 3 options, ie, the user has the choice to fill in 3 criteria (F12, F13, F14) and can fill in any one, two or all three of the criteria, this is what I have so far (don't laugh at my clumsy coding): If IsEmpty(Range("F12").Value) Then With Worksheets("Store Data") Sheets("Store Data").Select If .FilterMode Then .ShowAllData End If End With Else Sheets("Store Data").Select If Sheets("Store data").FilterMode Then Sheets("Store data").ShowAllData Selection.AutoFilter Field:=7, Criteria1:=(Range("Sheet3!F12").Value) & (Range("Sheet3!g12").Value) Else Selection.AutoFilter Field:=7, Criteria1:=(Range("Sheet3!F12").Value) & (Range("Sheet3!g12").Value) End If End If End Sub Any ideas??? "Dave Peterson" wrote: 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 -- Dave Peterson |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Inputting criteria into a macro/vb code
Glad it worked for you.
Marie Bayes wrote: Dave, you're an absolute genius, thanks once again for your invaluable aid. Marie "Dave Peterson" wrote: That's what I tried in my first response: Sub TEST() 'Show all the data on "Store Data" with Sheets("Store Data") .Select if .filtermode then .showalldata end if end with with worksheets("sheet3") with .range("f12") if isempty(.value) then 'skip it else Selection.AutoFilter Field:=7, _ Criteria1:=.value & .offset(0,1).value end if end with with .range("f13") if isempty(.value) then 'skip it else Selection.AutoFilter Field:=8888, _ Criteria1:=.value & .offset(0,1).value end if end with with .range("f14") if isempty(.value) then 'skip it else Selection.AutoFilter Field:=9999, _ Criteria1:=.value & .offset(0,1).value end if end with end with End Sub I don't know what fields F13 and F14 would control. I used 8888 and 9999. That's probably not correct! ..offset(0,1) is one column to the right of the cell refered to in the With statement. F12&G12, F13&G13, and so on. Marie Bayes wrote: Yes I did. I couldn't get it to work at all with the 'with' in, but that was probably just me being a bit dumb. Now it gets more interesting as I now have to find a way to have the 3 options, ie, the user has the choice to fill in 3 criteria (F12, F13, F14) and can fill in any one, two or all three of the criteria, this is what I have so far (don't laugh at my clumsy coding): If IsEmpty(Range("F12").Value) Then With Worksheets("Store Data") Sheets("Store Data").Select If .FilterMode Then .ShowAllData End If End With Else Sheets("Store Data").Select If Sheets("Store data").FilterMode Then Sheets("Store data").ShowAllData Selection.AutoFilter Field:=7, Criteria1:=(Range("Sheet3!F12").Value) & (Range("Sheet3!g12").Value) Else Selection.AutoFilter Field:=7, Criteria1:=(Range("Sheet3!F12").Value) & (Range("Sheet3!g12").Value) End If End If End Sub Any ideas??? "Dave Peterson" wrote: 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 -- 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 |