Advanced Filter macro

R

Raybo

Board Regular
Joined
Mar 3, 2011
Messages
124
I recorded a macro that utilizes the "Advanced Filter" feature in Excel.

I have a criteria that has 5 possible values. They are: na, e, e/p, p, s.

The macro filters each of those 5 values, individually, then I have some formulas that count the number of entries for each of those 5 possible values. The problem I have is that if I filter the "e" value and there are no rows that contain an "e", I get the count for "e/p", like it's defaulting to the closest match.

Is there a way to prevent this from happening, the count for "e", if there are none, should be 0 (zero).

Here's a portion of the code:

Code: 
Application.ScreenUpdating = False
    Range("I2").Select
    ActiveCell.FormulaR1C1 = "e"'[COLOR="Red"]this is where the value gets changed[/COLOR]
    Range("J2").Select
    ActiveCell.FormulaR1C1 = "0"
    Range("I11").Select
    Range("A11:N94").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _
        "G1:J2"), CopyToRange:=Range("HA1:HN1"), Unique:=False
    Range("O1").Select'[COLOR="red"]this is where the count is returned[/COLOR]
    Selection.Copy
    Range("u1").Select'[COLOR="red"]this is where the count is pasted to for each of the 5 possible values and this is where I get the wrong values I mentioned[/COLOR]
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    
    Range("I2").Select
    ActiveCell.FormulaR1C1 = "e"
    Range("J2").Select
    ActiveCell.FormulaR1C1 = "1"
    Range("I11").Select
    Range("A11:N94").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _
        "G1:J2"), CopyToRange:=Range("HA1:HN1"), Unique:=False
    Range("O2").Select
    Selection.Copy
    Range("u2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    
    Range("I2").Select
    ActiveCell.FormulaR1C1 = "e"
    Range("J2").Select
    ActiveCell.FormulaR1C1 = "2"
    Range("I11").Select
    Range("A11:N94").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _
        "G1:J2"), CopyToRange:=Range("HA1:HN1"), Unique:=False
    Range("O3").Select
    Selection.Copy
    Range("u3").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    
    Range("I2").Select
    ActiveCell.FormulaR1C1 = "e"
    Range("J2").Select
    ActiveCell.FormulaR1C1 = "3"
    Range("I11").Select
    Range("A11:N94").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _
        "G1:J2"), CopyToRange:=Range("HA1:HN1"), Unique:=False
    Range("O4").Select
    Selection.Copy
    Range("u4").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    
    Range("I2").Select
    ActiveCell.FormulaR1C1 = "e"
    Range("J2").Select
    ActiveCell.FormulaR1C1 = "4"
    Range("I11").Select
    Range("A11:N94").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _
        "G1:J2"), CopyToRange:=Range("HA1:HN1"), Unique:=False
    Range("O5").Select
    Selection.Copy
    Range("u5").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    
    Range("I2").Select
    ActiveCell.FormulaR1C1 = "e"
    Range("J2").Select
    ActiveCell.FormulaR1C1 = "5"
    Range("I11").Select
    Range("A11:N94").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _
        "G1:J2"), CopyToRange:=Range("HA1:HN1"), Unique:=False
    Range("O6").Select
    Selection.Copy
    Range("u6").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    
    Range("I2").Select
    ActiveCell.FormulaR1C1 = "e"
    Range("J2").Select
    ActiveCell.FormulaR1C1 = "6"
    Range("I11").Select
    Range("A11:N94").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _
        "G1:J2"), CopyToRange:=Range("HA1:HN1"), Unique:=False
    Range("O7").Select
    Selection.Copy
    Range("u7").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    
    Range("I2").Select
    ActiveCell.FormulaR1C1 = "e"
    Range("J2").Select
    ActiveCell.FormulaR1C1 = "7"
    Range("I11").Select
    Range("A11:N94").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _
        "G1:J2"), CopyToRange:=Range("HA1:HN1"), Unique:=False
    Range("O8").Select
    Selection.Copy
    Range("u8").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    
    Range("I2").Select
    ActiveCell.FormulaR1C1 = "e"
    Range("J2").Select
    ActiveCell.FormulaR1C1 = "8"
    Range("I11").Select
    Range("A11:N94").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _
        "G1:J2"), CopyToRange:=Range("HA1:HN1"), Unique:=False
    Range("O9").Select
    Selection.Copy
    Range("u9").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub
Sub FilterRunStyleSheet_test()
Application.ScreenUpdating = False
    Range("I2").Select
    ActiveCell.FormulaR1C1 = "e/p"
    Range("J2").Select
    ActiveCell.FormulaR1C1 = "0"
    Range("I11").Select
    Range("A11:N94").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _
        "G1:J2"), CopyToRange:=Range("HA1:HN1"), Unique:=False
    Range("O1").Select
    Selection.Copy
    Range("x1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    
    Range("I2").Select
    ActiveCell.FormulaR1C1 = "e/p"
    Range("J2").Select
    ActiveCell.FormulaR1C1 = "1"
    Range("I11").Select
    Range("A11:N94").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _
        "G1:J2"), CopyToRange:=Range("HA1:HN1"), Unique:=False
    Range("O2").Select
    Selection.Copy
    Range("x2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    
    Range("I2").Select
    ActiveCell.FormulaR1C1 = "e/p"
    Range("J2").Select
    ActiveCell.FormulaR1C1 = "2"
    Range("I11").Select
    Range("A11:N94").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _
        "G1:J2"), CopyToRange:=Range("HA1:HN1"), Unique:=False
    Range("O3").Select
    Selection.Copy
    Range("x3").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    
    Range("I2").Select
    ActiveCell.FormulaR1C1 = "e/p"
    Range("J2").Select
    ActiveCell.FormulaR1C1 = "3"
    Range("I11").Select
    Range("A11:N94").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _
        "G1:J2"), CopyToRange:=Range("HA1:HN1"), Unique:=False
    Range("O4").Select
    Selection.Copy
    Range("x4").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    
    Range("I2").Select
    ActiveCell.FormulaR1C1 = "e/p"
    Range("J2").Select
    ActiveCell.FormulaR1C1 = "4"
    Range("I11").Select
    Range("A11:N94").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _
        "G1:J2"), CopyToRange:=Range("HA1:HN1"), Unique:=False
    Range("O5").Select
    Selection.Copy
    Range("x5").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    
    Range("I2").Select
    ActiveCell.FormulaR1C1 = "e/p"
    Range("J2").Select
    ActiveCell.FormulaR1C1 = "5"
    Range("I11").Select
    Range("A11:N94").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _
        "G1:J2"), CopyToRange:=Range("HA1:HN1"), Unique:=False
    Range("O6").Select
    Selection.Copy
    Range("x6").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    
    Range("I2").Select
    ActiveCell.FormulaR1C1 = "e/p"
    Range("J2").Select
    ActiveCell.FormulaR1C1 = "6"
    Range("I11").Select
    Range("A11:N94").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _
        "G1:J2"), CopyToRange:=Range("HA1:HN1"), Unique:=False
    Range("O7").Select
    Selection.Copy
    Range("x7").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    
    Range("I2").Select
    ActiveCell.FormulaR1C1 = "e/p"
    Range("J2").Select
    ActiveCell.FormulaR1C1 = "7"
    Range("I11").Select
    Range("A11:N94").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _
        "G1:J2"), CopyToRange:=Range("HA1:HN1"), Unique:=False
    Range("O8").Select
    Selection.Copy
    Range("x8").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    
    Range("I2").Select
    ActiveCell.FormulaR1C1 = "e/p"
    Range("J2").Select
    ActiveCell.FormulaR1C1 = "8"
    Range("I11").Select
    Range("A11:N94").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _
        "G1:J2"), CopyToRange:=Range("HA1:HN1"), Unique:=False
    Range("O9").Select
    Selection.Copy
    Range("x9").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
Application.ScreenUpdating = True

I realize that writing a macro that loops through all this stuff would be much better, but I can't write code worth a darn, and usually record something and then modify when necessary.
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
Sort by date Sort by votes
Advanced Filter automaticaly uses a wild card. To get "e" but exclude "e/p
Try
Code: 
Range("I2").Value = "'" & "=e"
 
Upvote 0
mikerickson said:
Advanced Filter automaticaly uses a wild card. To get "e" but exclude "e/p
Try
Code: 
Range("I2").Value = "'" & "=e"
Click to expand...

Thanks, I did some more surfing and found this solution also. It works also:

ActiveCell.FormulaR1C1 = "=""=e"""
 
Upvote 0
While we're looking at this macro of mine, I would appreciate someone re-writing the code so that it loops through each of the 5 possible values in column "I" ( na, e, e/p, p, s), and the 9 possible values in column "J" (0 through 8). I need it to first filter for "na" for each possible value in column "J", then proceed to the next possible value in column "I", which would be "e", and all 9 possible values in column "J", then proceed to "e/p", then "p", and finally "s".

Like I said, I know that recording all these steps is not the way to do this, but haven't the code writing skill needed to loop through it properly.
 
Upvote 0
It's been quite a while since my last post in this thread, asking for help looping through the code. As I stated, I recorded the macro, which is very inefficient. I would really like to steamline the code for faster processing.

Thanks in advance!
 
Upvote 0
You must log in or register to reply here.

Similar threads

Dazzawm
  • Solved
Change Range To Recorded Macro
Replies
3
Views
168
Alex Blakenburg
A
willow1985
  • Solved
VBA Run-time error 91 - Object variable or With block variable not set when trying to refresh data/Sleep
Replies
4
Views
376
willow1985
willow1985
S
  • Question Question
Dynamic Address by VBA
Replies
8
Views
205
MikeVol
MikeVol
K
  • Question Question
Increasing row before the macro runs again
Replies
1
Views
228
kbk
K
S
  • Question Question
Macro Formatting to all WS
Replies
4
Views
331
MARK858
MARK858

Forum statistics

Threads
1,224,551
Messages
6,179,480
Members
452,915
Latest member
hannnahheileen

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top