PDA

View Full Version : Solved: Autofilter: using * in criteria for zip codes



nameuser321
04-11-2012, 04:53 PM
This is the function that I'm trying to figure out. Can't use the criteria part of this sub because i can't use (*) when looking at a number. trying to get make visible even partial zip codes that meet this criteria. Just blanks everything out at this point. My Question is is there a way to treat the zip code like a string without entering some character in the cell ("z_77043"), which i kind of just don't want to do, or is there a better way to make visible partial zip codes?


sub test()
dim aRange as range
Set aRange = ActiveSheet.Range("A1")

AutoFilterMakeVisible aRange, "ZIP", "=*77*"
end sub

Sub AutoFilterMakeVisible(dRef As Range, rangeName As String, aCriteria As String)
Dim interRange As Range

Set interRange = dRef.Parent.Names(rangeName).RefersToRange
interRange.AutoFilter interRange.Column, aCriteria
End Sub

nameuser321
04-11-2012, 05:08 PM
Another part to this question would be autofilter on partial dates, like making visible only dates in 2012 from dates such as (2/3/12), if i could just convert that date into a string, then have my autofilter criteria portion "*/12" i could easily do this. The problem is that i just can't get inside the autofilter sub.....

Bob Phillips
04-12-2012, 12:42 AM
Maybe you could insert a helper column and add a formula to that such as

=ISNUMBER(FIND(77,I2)

copied down and filter for true n the helper column.

nameuser321
04-12-2012, 07:45 AM
Great suggestion xld. Last night I also realized that I could can turn that number into a string. By setting format to text. This I had originally tried, but for some reason you have to go into the formula bar and then press enter for it to take. You get an error message but then you can run a search with the * sign. Thanks for your help again!