RockandGrohl
Well-known Member
- Joined
- Aug 1, 2018
- Messages
- 788
- Office Version
- 2010
- Platform
- Windows
Hi all
Have the following code:
PU1 through 10 are placenames and can be like "Hereford" "Worcester" "Droitwich" "Pershore" etc.
It's looking in a workbook called AD under column I which contains a string of placenames, like this: "Felixstowe, Ipswich, Stowmarket, Bury St Edmunds, Newmarket, Cambridge, St Neots, Sandy, Biggleswade, Letchworth, Stevenage"
What I'm trying to do is assign valid placenames earlier on in the macro to variables PU1 through to PU10
However, my problem is that if a newspaper only has three/10 valid placenames, what variable name do I give PU4-PU10? If I do "", then the filter will look for [ "*" "" "*" ] which will just return any value. Alternatively, if I name the PU4-PU10 "Blank" then it strips out all results.
Basically the names of what I am filtering will change regularly, in addition to the quantity of what I am filtering. Sometimes I'll just be looking for one value to filter on, sometimes 8, and the names will always change.
Thanks!
The only thing I can think of is 10x IF statements which check if the PU is blank or not, if it's not then it adds another valid criteria to the filter, otherwise it skips.
Just to add some extra context, if column I has a list of strings, and one string contains one of the PU's and another string contains another of the PU's, I want them both to show up, so it's very much an "OR" kind of search.
Have the following code:
Code:
LastrowAD = Cells(Rows.Count, "A").End(xlUp).Row
ActiveSheet.Range("$A$1:$W$" & LastrowAD).AutoFilter Field:=5, Criteria1:=">=" & CLng(DateValue(tdate)) ' Tour date
ActiveSheet.Range("$A$1:$W$" & LastrowAD).AutoFilter Field:=12, Criteria1:="=" ' Ad Week blank
ActiveSheet.Range("$A$1:$W$" & LastrowAD).AutoFilter Field:=2, Criteria1:="Active" ' Status Active
ActiveSheet.Range("$A$1:$W$" & LastrowAD).AutoFilter Field:=9, Criteria1:=Array("*" & PU1 & "*", "*" & PU2 & "*", "*" & PU3 & "*"), Operator:=xlOr, Operator:=xlFilterValues
PU1 through 10 are placenames and can be like "Hereford" "Worcester" "Droitwich" "Pershore" etc.
It's looking in a workbook called AD under column I which contains a string of placenames, like this: "Felixstowe, Ipswich, Stowmarket, Bury St Edmunds, Newmarket, Cambridge, St Neots, Sandy, Biggleswade, Letchworth, Stevenage"
What I'm trying to do is assign valid placenames earlier on in the macro to variables PU1 through to PU10
However, my problem is that if a newspaper only has three/10 valid placenames, what variable name do I give PU4-PU10? If I do "", then the filter will look for [ "*" "" "*" ] which will just return any value. Alternatively, if I name the PU4-PU10 "Blank" then it strips out all results.
Basically the names of what I am filtering will change regularly, in addition to the quantity of what I am filtering. Sometimes I'll just be looking for one value to filter on, sometimes 8, and the names will always change.
Thanks!
The only thing I can think of is 10x IF statements which check if the PU is blank or not, if it's not then it adds another valid criteria to the filter, otherwise it skips.
Just to add some extra context, if column I has a list of strings, and one string contains one of the PU's and another string contains another of the PU's, I want them both to show up, so it's very much an "OR" kind of search.
Last edited: