AutoFilter Criteria in VBA

G

Guest

I am not able to set the criteria in a autofilter to be dynamic (i.e as the
user is selecting it.) in VBA. The code is as below:

Code:

Sub filt2()
'
' filt2 Macro
' Macro recorded 4/23/2007 by Abhijit
'

'
Dim engcode As Range, product As Range, quotetype As Range
Set engcode = ThisWorkbook.Worksheets("a").Range("a1")
Set product = ThisWorkbook.Worksheets("a").Range("a2")
Set quotetype = ThisWorkbook.Worksheets("a").Range("a3")
Sheets("Raw").Select
Selection.AutoFilter
Selection.AutoFilter
Selection.AutoFilter Field:=13, Criteria1:=engcode.Value
Selection.AutoFilter Field:=2, Criteria1:=product.Value
Selection.AutoFilter Field:=4, Criteria1:=quotetype.Value
Selection.AutoFilter Field:=8, Criteria1:="Jan"
Sheets("Raw").Select
Range("A1").Select
ActiveCell.FormulaR1C1 = "=SUBTOTAL(102,R[1]C:RC)"
Range("A1").Select
Selection.Copy
Sheets("a").Select
Range("F14").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.ClearContents
Sheets("Raw").Select
Selection.Copy
Sheets("a").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
End Sub

Any suggestions.

Please help
 
D

Don Guillett

try this idea. Notice the DOT ( . ) placement within the WITH statements.
Can be fired from anywhere in the workbook.
Sub filterit()
With Sheets("a")
engcode = .Range("a1")
'.etc
'.etc
'.etc
End With

With Sheets("raw")
With .Range("a6:d6") 'yourtoprowofrange
.AutoFilter
.AutoFilter Field:=1, Criteria1:=engcode
'.etc
'.etc

..Range("A1").FormulaR1C1 = "=SUBTOTAL(102,R[1]C:RC)"
Sheets("a").Range("a1").Value = .Range("a1")
' MsgBox "done"
.AutoFilter
End With
End With

End Sub
 
G

Guest

Hi Don,

Thanks so much for your response. I will definitely that. In the meanwhile,
I rewrote the code as below and it seems to be working for the time being.
Thanks for all the help!

Application.ScreenUpdating = False
Dim engcode As Range, product As Range, quotetype As Range, a As Range
Set engcode = ThisWorkbook.Worksheets("a").Range("e7")
Set product = ThisWorkbook.Worksheets("a").Range("e8")
Set quotetype = ThisWorkbook.Worksheets("a").Range("e9")
Set a = ThisWorkbook.Worksheets("Raw").Range("a2")
ThisWorkbook.Worksheets("Raw").AutoFilterMode = False
If engcode = "ALL" Then
a.AutoFilter Field:=1, Criteria1:=product
a.AutoFilter Field:=3, Criteria1:=quotetype
a.AutoFilter Field:=7, Criteria1:="Jan"
Worksheets("Raw").Range("A1").FormulaR1C1 =
"=SUBTOTAL(102,R[1]C[12]:R[10000]C[12])"
Worksheets("a").Range("f14").Value =
Worksheets("Raw").Range("A1").Value
Worksheets("Raw").Range("B1").FormulaR1C1 =
"=SUBTOTAL(109,R[1]C[7]:R[10000]C[7])"
Worksheets("a").Range("f15").Value =
Worksheets("Raw").Range("B1").Value
Sheets("a").Select
Else
a.AutoFilter Field:=12, Criteria1:=engcode
a.AutoFilter Field:=1, Criteria1:=product
a.AutoFilter Field:=3, Criteria1:=quotetype
a.AutoFilter Field:=7, Criteria1:="Jan"
Worksheets("Raw").Range("A1").FormulaR1C1 =
"=SUBTOTAL(102,R[1]C[12]:R[10000]C[12])"
Worksheets("a").Range("f14").Value = Worksheets("Raw").Range("A1").Value
Worksheets("Raw").Range("B1").FormulaR1C1 =
"=SUBTOTAL(109,R[1]C[7]:R[10000]C[7])"
Worksheets("a").Range("f15").Value =
Worksheets("Raw").Range("B1").Value
Sheets("a").Select
End If
End Sub
--
Regards,
Abhi


Don Guillett said:
try this idea. Notice the DOT ( . ) placement within the WITH statements.
Can be fired from anywhere in the workbook.
Sub filterit()
With Sheets("a")
engcode = .Range("a1")
'.etc
'.etc
'.etc
End With

With Sheets("raw")
With .Range("a6:d6") 'yourtoprowofrange
.AutoFilter
.AutoFilter Field:=1, Criteria1:=engcode
'.etc
'.etc

..Range("A1").FormulaR1C1 = "=SUBTOTAL(102,R[1]C:RC)"
Sheets("a").Range("a1").Value = .Range("a1")
' MsgBox "done"
.AutoFilter
End With
End With

End Sub


--
Don Guillett
SalesAid Software
(e-mail address removed)
Abhi said:
I am not able to set the criteria in a autofilter to be dynamic (i.e as the
user is selecting it.) in VBA. The code is as below:

Code:

Sub filt2()
'
' filt2 Macro
' Macro recorded 4/23/2007 by Abhijit
'

'
Dim engcode As Range, product As Range, quotetype As Range
Set engcode = ThisWorkbook.Worksheets("a").Range("a1")
Set product = ThisWorkbook.Worksheets("a").Range("a2")
Set quotetype = ThisWorkbook.Worksheets("a").Range("a3")
Sheets("Raw").Select
Selection.AutoFilter
Selection.AutoFilter
Selection.AutoFilter Field:=13, Criteria1:=engcode.Value
Selection.AutoFilter Field:=2, Criteria1:=product.Value
Selection.AutoFilter Field:=4, Criteria1:=quotetype.Value
Selection.AutoFilter Field:=8, Criteria1:="Jan"
Sheets("Raw").Select
Range("A1").Select
ActiveCell.FormulaR1C1 = "=SUBTOTAL(102,R[1]C:RC)"
Range("A1").Select
Selection.Copy
Sheets("a").Select
Range("F14").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.ClearContents
Sheets("Raw").Select
Selection.Copy
Sheets("a").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
End Sub

Any suggestions.

Please help
Click to expand...
Click to expand...
 
D

Don Guillett

Glad to be of help. A cursory look suggests that your code could be further
simplified in regards to the IF.

If engcode <> "ALL" Then
a.AutoFilter Field:=12, Criteria1:=engcode
end if
a.AutoFilter Field:=1, Criteria1:=product
a.AutoFilter Field:=3, Criteria1:=quotetype
a.AutoFilter Field:=7, Criteria1:="Jan"




--
Don Guillett
SalesAid Software
(e-mail address removed)
Abhi said:
Hi Don,

Thanks so much for your response. I will definitely that. In the
meanwhile,
I rewrote the code as below and it seems to be working for the time being.
Thanks for all the help!

Application.ScreenUpdating = False
Dim engcode As Range, product As Range, quotetype As Range, a As Range
Set engcode = ThisWorkbook.Worksheets("a").Range("e7")
Set product = ThisWorkbook.Worksheets("a").Range("e8")
Set quotetype = ThisWorkbook.Worksheets("a").Range("e9")
Set a = ThisWorkbook.Worksheets("Raw").Range("a2")
ThisWorkbook.Worksheets("Raw").AutoFilterMode = False
If engcode = "ALL" Then
a.AutoFilter Field:=1, Criteria1:=product
a.AutoFilter Field:=3, Criteria1:=quotetype
a.AutoFilter Field:=7, Criteria1:="Jan"
Worksheets("Raw").Range("A1").FormulaR1C1 =
"=SUBTOTAL(102,R[1]C[12]:R[10000]C[12])"
Worksheets("a").Range("f14").Value =
Worksheets("Raw").Range("A1").Value
Worksheets("Raw").Range("B1").FormulaR1C1 =
"=SUBTOTAL(109,R[1]C[7]:R[10000]C[7])"
Worksheets("a").Range("f15").Value =
Worksheets("Raw").Range("B1").Value
Sheets("a").Select
Else
a.AutoFilter Field:=12, Criteria1:=engcode
a.AutoFilter Field:=1, Criteria1:=product
a.AutoFilter Field:=3, Criteria1:=quotetype
a.AutoFilter Field:=7, Criteria1:="Jan"
Worksheets("Raw").Range("A1").FormulaR1C1 =
"=SUBTOTAL(102,R[1]C[12]:R[10000]C[12])"
Worksheets("a").Range("f14").Value =
Worksheets("Raw").Range("A1").Value
Worksheets("Raw").Range("B1").FormulaR1C1 =
"=SUBTOTAL(109,R[1]C[7]:R[10000]C[7])"
Worksheets("a").Range("f15").Value =
Worksheets("Raw").Range("B1").Value
Sheets("a").Select
End If
End Sub
--
Regards,
Abhi


Don Guillett said:
try this idea. Notice the DOT ( . ) placement within the WITH
statements.
Can be fired from anywhere in the workbook.
Sub filterit()
With Sheets("a")
engcode = .Range("a1")
'.etc
'.etc
'.etc
End With

With Sheets("raw")
With .Range("a6:d6") 'yourtoprowofrange
.AutoFilter
.AutoFilter Field:=1, Criteria1:=engcode
'.etc
'.etc

..Range("A1").FormulaR1C1 = "=SUBTOTAL(102,R[1]C:RC)"
Sheets("a").Range("a1").Value = .Range("a1")
' MsgBox "done"
.AutoFilter
End With
End With

End Sub


--
Don Guillett
SalesAid Software
(e-mail address removed)
Abhi said:
I am not able to set the criteria in a autofilter to be dynamic (i.e as
the
user is selecting it.) in VBA. The code is as below:

Code:

Sub filt2()
'
' filt2 Macro
' Macro recorded 4/23/2007 by Abhijit
'

'
Dim engcode As Range, product As Range, quotetype As Range
Set engcode = ThisWorkbook.Worksheets("a").Range("a1")
Set product = ThisWorkbook.Worksheets("a").Range("a2")
Set quotetype = ThisWorkbook.Worksheets("a").Range("a3")
Sheets("Raw").Select
Selection.AutoFilter
Selection.AutoFilter
Selection.AutoFilter Field:=13, Criteria1:=engcode.Value
Selection.AutoFilter Field:=2, Criteria1:=product.Value
Selection.AutoFilter Field:=4, Criteria1:=quotetype.Value
Selection.AutoFilter Field:=8, Criteria1:="Jan"
Sheets("Raw").Select
Range("A1").Select
ActiveCell.FormulaR1C1 = "=SUBTOTAL(102,R[1]C:RC)"
Range("A1").Select
Selection.Copy
Sheets("a").Select
Range("F14").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.ClearContents
Sheets("Raw").Select
Selection.Copy
Sheets("a").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
End Sub

Any suggestions.

Please help
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Hi Don,

That was a little gem. It just did not occur to me while I was coding.

Thanks a ton!!
--
Regards,
Abhi


Don Guillett said:
Glad to be of help. A cursory look suggests that your code could be further
simplified in regards to the IF.

If engcode <> "ALL" Then
a.AutoFilter Field:=12, Criteria1:=engcode
end if
a.AutoFilter Field:=1, Criteria1:=product
a.AutoFilter Field:=3, Criteria1:=quotetype
a.AutoFilter Field:=7, Criteria1:="Jan"




--
Don Guillett
SalesAid Software
(e-mail address removed)
Abhi said:
Hi Don,

Thanks so much for your response. I will definitely that. In the
meanwhile,
I rewrote the code as below and it seems to be working for the time being.
Thanks for all the help!

Application.ScreenUpdating = False
Dim engcode As Range, product As Range, quotetype As Range, a As Range
Set engcode = ThisWorkbook.Worksheets("a").Range("e7")
Set product = ThisWorkbook.Worksheets("a").Range("e8")
Set quotetype = ThisWorkbook.Worksheets("a").Range("e9")
Set a = ThisWorkbook.Worksheets("Raw").Range("a2")
ThisWorkbook.Worksheets("Raw").AutoFilterMode = False
If engcode = "ALL" Then
a.AutoFilter Field:=1, Criteria1:=product
a.AutoFilter Field:=3, Criteria1:=quotetype
a.AutoFilter Field:=7, Criteria1:="Jan"
Worksheets("Raw").Range("A1").FormulaR1C1 =
"=SUBTOTAL(102,R[1]C[12]:R[10000]C[12])"
Worksheets("a").Range("f14").Value =
Worksheets("Raw").Range("A1").Value
Worksheets("Raw").Range("B1").FormulaR1C1 =
"=SUBTOTAL(109,R[1]C[7]:R[10000]C[7])"
Worksheets("a").Range("f15").Value =
Worksheets("Raw").Range("B1").Value
Sheets("a").Select
Else
a.AutoFilter Field:=12, Criteria1:=engcode
a.AutoFilter Field:=1, Criteria1:=product
a.AutoFilter Field:=3, Criteria1:=quotetype
a.AutoFilter Field:=7, Criteria1:="Jan"
Worksheets("Raw").Range("A1").FormulaR1C1 =
"=SUBTOTAL(102,R[1]C[12]:R[10000]C[12])"
Worksheets("a").Range("f14").Value =
Worksheets("Raw").Range("A1").Value
Worksheets("Raw").Range("B1").FormulaR1C1 =
"=SUBTOTAL(109,R[1]C[7]:R[10000]C[7])"
Worksheets("a").Range("f15").Value =
Worksheets("Raw").Range("B1").Value
Sheets("a").Select
End If
End Sub
--
Regards,
Abhi


Don Guillett said:
try this idea. Notice the DOT ( . ) placement within the WITH
statements.
Can be fired from anywhere in the workbook.
Sub filterit()
With Sheets("a")
engcode = .Range("a1")
'.etc
'.etc
'.etc
End With

With Sheets("raw")
With .Range("a6:d6") 'yourtoprowofrange
.AutoFilter
.AutoFilter Field:=1, Criteria1:=engcode
'.etc
'.etc

..Range("A1").FormulaR1C1 = "=SUBTOTAL(102,R[1]C:RC)"
Sheets("a").Range("a1").Value = .Range("a1")
' MsgBox "done"
.AutoFilter
End With
End With

End Sub


--
Don Guillett
SalesAid Software
(e-mail address removed)
I am not able to set the criteria in a autofilter to be dynamic (i.e as
the
user is selecting it.) in VBA. The code is as below:

Code:

Sub filt2()
'
' filt2 Macro
' Macro recorded 4/23/2007 by Abhijit
'

'
Dim engcode As Range, product As Range, quotetype As Range
Set engcode = ThisWorkbook.Worksheets("a").Range("a1")
Set product = ThisWorkbook.Worksheets("a").Range("a2")
Set quotetype = ThisWorkbook.Worksheets("a").Range("a3")
Sheets("Raw").Select
Selection.AutoFilter
Selection.AutoFilter
Selection.AutoFilter Field:=13, Criteria1:=engcode.Value
Selection.AutoFilter Field:=2, Criteria1:=product.Value
Selection.AutoFilter Field:=4, Criteria1:=quotetype.Value
Selection.AutoFilter Field:=8, Criteria1:="Jan"
Sheets("Raw").Select
Range("A1").Select
ActiveCell.FormulaR1C1 = "=SUBTOTAL(102,R[1]C:RC)"
Range("A1").Select
Selection.Copy
Sheets("a").Select
Range("F14").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.ClearContents
Sheets("Raw").Select
Selection.Copy
Sheets("a").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
End Sub

Any suggestions.

Please help
Click to expand...
Click to expand...
Click to expand...
 
D

Don Guillett

Glad to help eliminate redundancy.

--
Don Guillett
SalesAid Software
(e-mail address removed)
Abhi said:
Hi Don,

That was a little gem. It just did not occur to me while I was coding.

Thanks a ton!!
--
Regards,
Abhi


Don Guillett said:
Glad to be of help. A cursory look suggests that your code could be
further
simplified in regards to the IF.

If engcode <> "ALL" Then
a.AutoFilter Field:=12, Criteria1:=engcode
end if
a.AutoFilter Field:=1, Criteria1:=product
a.AutoFilter Field:=3, Criteria1:=quotetype
a.AutoFilter Field:=7, Criteria1:="Jan"




--
Don Guillett
SalesAid Software
(e-mail address removed)
Abhi said:
Hi Don,

Thanks so much for your response. I will definitely that. In the
meanwhile,
I rewrote the code as below and it seems to be working for the time
being.
Thanks for all the help!

Application.ScreenUpdating = False
Dim engcode As Range, product As Range, quotetype As Range, a As
Range
Set engcode = ThisWorkbook.Worksheets("a").Range("e7")
Set product = ThisWorkbook.Worksheets("a").Range("e8")
Set quotetype = ThisWorkbook.Worksheets("a").Range("e9")
Set a = ThisWorkbook.Worksheets("Raw").Range("a2")
ThisWorkbook.Worksheets("Raw").AutoFilterMode = False
If engcode = "ALL" Then
a.AutoFilter Field:=1, Criteria1:=product
a.AutoFilter Field:=3, Criteria1:=quotetype
a.AutoFilter Field:=7, Criteria1:="Jan"
Worksheets("Raw").Range("A1").FormulaR1C1 =
"=SUBTOTAL(102,R[1]C[12]:R[10000]C[12])"
Worksheets("a").Range("f14").Value =
Worksheets("Raw").Range("A1").Value
Worksheets("Raw").Range("B1").FormulaR1C1 =
"=SUBTOTAL(109,R[1]C[7]:R[10000]C[7])"
Worksheets("a").Range("f15").Value =
Worksheets("Raw").Range("B1").Value
Sheets("a").Select
Else
a.AutoFilter Field:=12, Criteria1:=engcode
a.AutoFilter Field:=1, Criteria1:=product
a.AutoFilter Field:=3, Criteria1:=quotetype
a.AutoFilter Field:=7, Criteria1:="Jan"
Worksheets("Raw").Range("A1").FormulaR1C1 =
"=SUBTOTAL(102,R[1]C[12]:R[10000]C[12])"
Worksheets("a").Range("f14").Value =
Worksheets("Raw").Range("A1").Value
Worksheets("Raw").Range("B1").FormulaR1C1 =
"=SUBTOTAL(109,R[1]C[7]:R[10000]C[7])"
Worksheets("a").Range("f15").Value =
Worksheets("Raw").Range("B1").Value
Sheets("a").Select
End If
End Sub
--
Regards,
Abhi


:

try this idea. Notice the DOT ( . ) placement within the WITH
statements.
Can be fired from anywhere in the workbook.
Sub filterit()
With Sheets("a")
engcode = .Range("a1")
'.etc
'.etc
'.etc
End With

With Sheets("raw")
With .Range("a6:d6") 'yourtoprowofrange
.AutoFilter
.AutoFilter Field:=1, Criteria1:=engcode
'.etc
'.etc

..Range("A1").FormulaR1C1 = "=SUBTOTAL(102,R[1]C:RC)"
Sheets("a").Range("a1").Value = .Range("a1")
' MsgBox "done"
.AutoFilter
End With
End With

End Sub


--
Don Guillett
SalesAid Software
(e-mail address removed)
I am not able to set the criteria in a autofilter to be dynamic (i.e
as
the
user is selecting it.) in VBA. The code is as below:

Code:

Sub filt2()
'
' filt2 Macro
' Macro recorded 4/23/2007 by Abhijit
'

'
Dim engcode As Range, product As Range, quotetype As Range
Set engcode = ThisWorkbook.Worksheets("a").Range("a1")
Set product = ThisWorkbook.Worksheets("a").Range("a2")
Set quotetype = ThisWorkbook.Worksheets("a").Range("a3")
Sheets("Raw").Select
Selection.AutoFilter
Selection.AutoFilter
Selection.AutoFilter Field:=13, Criteria1:=engcode.Value
Selection.AutoFilter Field:=2, Criteria1:=product.Value
Selection.AutoFilter Field:=4, Criteria1:=quotetype.Value
Selection.AutoFilter Field:=8, Criteria1:="Jan"
Sheets("Raw").Select
Range("A1").Select
ActiveCell.FormulaR1C1 = "=SUBTOTAL(102,R[1]C:RC)"
Range("A1").Select
Selection.Copy
Sheets("a").Select
Range("F14").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.ClearContents
Sheets("Raw").Select
Selection.Copy
Sheets("a").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
End Sub

Any suggestions.

Please help
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Help me4 2
Selection.AutoFilter Issue 1
AutoFilter Criteria using named range 4
Pasting Variable cell info into a macro 1
Autofilter / Sort in VBA doesn't work 2
Prevent getting all rows if Autofilter criteria doesn't exist 13
Multiple Filter Criteria in Excel VBA 4
Copying nothing/everything when an autofilter returns no results? XL2003 1

Top