Inputting criteria into a macro/vb code

[Dave Peterson]

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.

 

[Dave Peterson]

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:

Becomes:

Criteria1:=.range("A99").value & .range("a2").value
'change the addresses to what you need

 

[Marie Bayes]

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.

 

[Marie Bayes]

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.

 

[Marie Bayes]

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]

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).

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

 

[Marie Bayes]

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]

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.

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???

 

[Marie Bayes]

Dave, you're an absolute genius, thanks once again for your invaluable aid.

Marie

 

[Dave Peterson]

Glad it worked for you.

Dave, you're an absolute genius, thanks once again for your invaluable aid.

Marie