-
Aug 18th, 2014, 07:15 PM
#1
Thread Starter
Lively Member
[RESOLVED] Excel VBA: Autofilter with 3 or more criteria and wildcards
I have looked through my books, searched the web and spent the last 5 hours swearing , and I'm still no further along with this problem then when I started.
What I need to do.
1. Using Autofilter, filter a sheet where the filter consists of three names (for now) that can be in any position in the cell.
* This worked when I was filtering on two names.
Now, however the client needs to expand to three (with the expectation that there will be more names added to the filter in the future) and the macro no longer works.
2. Once filtered the visible rows will then be copied to another worksheet. (This still works)
Any suggestions?
The code that is not working is:
Code:
Sheets("StartPoint").Select
Range("A1").Select
With ActiveSheet
.AutoFilterMode = False
.UsedRange
'Determine last row
lLastrow = .Cells.SpecialCells(xlCellTypeLastCell).Row
'Set Rng to the A column data rows
Set Rng = Range("A1", Cells(lLastrow, "K"))
'Filter the B column to show only the data to be copied
Rng.AutoFilter field:=2, Criteria1:=Array("=*Harsch*", "=*Cottner*", "=*Markham*"), Operator:=xlFilterValues
Rng.AutoFilter field:=7, Criteria1:="POS", Operator:=xlFilterValues
'Copy the visible cells
Rng.SpecialCells(xlCellTypeVisible).EntireRow.Copy
End With
Sheets("NewPoint").Select
You all have helped me in the past, here's hoping you can help me with this one.
TIA
datapard
If you have to do it more than once...
Automate it!
-
Aug 19th, 2014, 12:05 PM
#2
Re: Excel VBA: Autofilter with 3 or more criteria and wildcards
What do you mean by this:
the filter consists of three names (for now) that can be in any position in the cell.
Is it the two filter fields that are causing the problem, or the three names in the criteria array?
-
Aug 19th, 2014, 03:11 PM
#3
Thread Starter
Lively Member
Re: Excel VBA: Autofilter with 3 or more criteria and wildcards
The names in the criteria array. The second filter, filters on the results of the first. The only change to the macro was going to the criteria array for the multiple names with the wildcards. That's what's not working.
If you have to do it more than once...
Automate it!
-
Aug 19th, 2014, 05:31 PM
#4
Re: Excel VBA: Autofilter with 3 or more criteria and wildcards
Got this:
Code:
Sub Testo()Dim ar As Variant
Dim i As Integer
ar = Array("=FG1F*", "=FG2F*", "=FG1E0??")
For i = 0 To UBound(ar)
Range("A1:B359").AutoFilter 1, ar(i)
Next
End Sub
from here:
http://www.ozgrid.com/forum/showthread.php?t=172620
If the number goes higher than 3, the loop should still handle it.
-
Aug 20th, 2014, 04:33 AM
#5
Thread Starter
Lively Member
Re: Excel VBA: Autofilter with 3 or more criteria and wildcards
Originally Posted by vbfbryce
Got this:
Code:
Sub Testo()Dim ar As Variant
Dim i As Integer
ar = Array("=FG1F*", "=FG2F*", "=FG1E0??")
For i = 0 To UBound(ar)
Range("A1:B359").AutoFilter 1, ar(i)
Next
End Sub
from here:
http://www.ozgrid.com/forum/showthread.php?t=172620
If the number goes higher than 3, the loop should still handle it.
Not sure why, as it looks like it should work, but when I incorporated the code into the macro and tried to run it I get "Run-Time Error '1004' Method 'Range of Object' failed.
This is the changed code:
Code:
Range("A1").Select
Dim ar As Variant
Dim a As Integer
With ActiveSheet
.AutoFilterMode = False
.UsedRange
'Determine last row
lLastrow = .Cells.SpecialCells(xlCellTypeLastCell).Row
'Set Rng to the A column data rows
Set Rng = Range("A1", Cells(lLastrow, "K"))
ar = Array("=*Harsch*", "=*Cottner*", "=*Markham*")
For a = 0 To UBound(ar)
Range(Rng).AutoFilter 1, ar(a) 'THIS IS WHERE IT IS FAILING
Next
'Filter the B column to show only the data to be copied
Rng.AutoFilter field:=2, Criteria1:=Array("=*Harsch*", "=*Cottner*", "=*Markham*"), Operator:=xlFilterValues
Rng.AutoFilter field:=7, Criteria1:="POS", Operator:=xlFilterValues
'Copy the visible cells
Rng.SpecialCells(xlCellTypeVisible).EntireRow.Copy
' Rng.AutoFilter
' .UsedRange 'Reset the last cell
End With
Can anyone see what I've done wrong here? I desperately need to get this to work. I'm at a complete halt on this project until I can get past this point.
Thank you all for any help you can give me.
+++Additional note+++
I just realized that I had only tagged this for MS Office 2003. It needs to be able to run in both 2003 and 2010, which it was doing until I changed this section of the macro.
datapard
Last edited by datapard; Aug 20th, 2014 at 04:42 AM.
Reason: Additional information that was missed.
If you have to do it more than once...
Automate it!
-
Aug 20th, 2014, 07:26 AM
#6
Re: Excel VBA: Autofilter with 3 or more criteria and wildcards
which line is highlighted when you get that error?
EDIT: Just noticed you already showed that, sorry!
-
Aug 20th, 2014, 07:36 AM
#7
Re: Excel VBA: Autofilter with 3 or more criteria and wildcards
Try dimming "rng" as Range, seemed to fix it for me.
-
Aug 20th, 2014, 10:57 AM
#8
Thread Starter
Lively Member
Re: Excel VBA: Autofilter with 3 or more criteria and wildcards
Originally Posted by vbfbryce
Try dimming "rng" as Range, seemed to fix it for me.
Just tried this, unfortunately, it still fails at the same point with the same error message.
If you have to do it more than once...
Automate it!
-
Aug 20th, 2014, 11:00 AM
#9
Re: Excel VBA: Autofilter with 3 or more criteria and wildcards
Which version of Excel you currently trying it in? Can you zip and attach the actual workbook, with code?
-
Aug 20th, 2014, 04:07 PM
#10
Thread Starter
Lively Member
Re: Excel VBA: Autofilter with 3 or more criteria and wildcards
Originally Posted by vbfbryce
Which version of Excel you currently trying it in? Can you zip and attach the actual workbook, with code?
Currently I'm working in Excel 2010. I had started this project in Excel 2003. Unfortunately I cannot attach the actual workbook as I am working under an NDA. I can show the code as long as I change the names being used in the criteria.
If you have to do it more than once...
Automate it!
-
Aug 21st, 2014, 12:56 PM
#11
Re: Excel VBA: Autofilter with 3 or more criteria and wildcards
Looks like you can't have more than two criteria (at least with wildcards), from what I can tell. Here is an ugly workaround to filter with 3 or more. It first adds a formula in column L (my data only went to column K), then filters based on that column, then clears it. It does leave the filter symbol in row 1 in column L, which doesn't look good...
Code:
Sub aFilter()
Dim ws As Worksheet
Dim lr As Long
Dim j As Long
Dim crit(2) As String
Dim strIf As String
Set ws = ActiveSheet
lr = ws.Range("a" & Rows.Count).End(xlUp).Row
crit(0) = "*ob*"
crit(1) = "*am*"
crit(2) = "*vi*"
With ws
.AutoFilterMode = False
.Range("l1").Value = "Temp"
For j = 2 To lr
strIf = "=if(or(isnumber(search(" & Chr(34) & crit(0) & Chr(34) & ",a" & j & ")),isnumber(search(" & Chr(34) & crit(1) & Chr(34) _
& ",a" & j & ")),isnumber(search(" & Chr(34) & crit(2) & Chr(34) & ",a" & j & "))),999,0)"
.Range("l" & j).Formula = strIf
Next j
End With
ws.Range("a1:l" & lr).AutoFilter field:=12, Criteria1:="999", Operator:=xlFilterValues
ws.Range("l1").EntireColumn.ClearContents
Set ws = Nothing
End Sub
-
Aug 21st, 2014, 09:01 PM
#12
Thread Starter
Lively Member
Re: Excel VBA: Autofilter with 3 or more criteria and wildcards
Originally Posted by vbfbryce
Looks like you can't have more than two criteria (at least with wildcards), from what I can tell. Here is an ugly workaround to filter with 3 or more. It first adds a formula in column L (my data only went to column K), then filters based on that column, then clears it. It does leave the filter symbol in row 1 in column L, which doesn't look good...
I had pretty much come to that conclusion myself. You can have as many criteria in an autofilter criteria array as you need...as long as none of them include a wildcard. So I used a most inelegant and inefficient (at least to me) method to give me what I needed for my project. I will be looking closer at your solution to the problem and see if it might work better.
In any case, that you for trying to help.
datawolf
If you have to do it more than once...
Automate it!
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|