Filtering Data Based On Different Criteria
 

Thank you for all who've helped me learn VBA through this group - you
guys are the best. I have yet another question and hope someone can
help. I know how to Autofilter in VBA, but have reached a point where
I don't know how to filter data based on criteria.

I have a list of data with 8 fields...the fieldnames are in cells
A1:H1, and the list of data starts in A2 and goes down a few 100
rows.
In cells J2:J9, I have numbers. J2 represents column A; J3 represents
column B, J4 is for column C, etc. Now, what I need to do is filter
the list based based on what number is in the J column. To make it
easier, I'll give an example.


The code should look at the number in J2. If it is greater than 0
(there are no negative numbers), then I need column A to be filtered
to show values 0. I know what I have to do once this filter is
achieved - in other words, once i achieve this filter, I copy the
data
in this column and paste it elsewhere - i know how to do that. After
I
do that, I want to unfilter and have the code go to check the value
in
J3. If that is greater than 0, then I want column B filtered. Again,
I
will copy that data over to another place. Next, column B will be
unfiltered and then the code will go to J4. If J4 = 0, then I'll go
to
J5. If J5 0, then I want column D filtered, etc. etc. This will end
once we get to J9, and see if the last column needs to be filtered or
not (J90 gets column H filtered.)


Here's a summary of which of the cells in column J correspond to the
columns:


J2 corresponds with column A
J3 corresponds with column B
J4 corresponds with column C
J5 corresponds with column D
J6 corresponds with column E
J7 corresponds with column F
J8 corresponds with column G
J9 corresponds with column H


I hope i was able to explain this so you can understand. Please let
me
know if you have any questions. Thanks in advance!

Filtering Data Based On Different Criteria
 

This should be real close to what you want. Watch for duplicate variable
names with your existing code.

Sub Marine()
Dim c As Range, myrng As Range
Dim i As Integer

Set myrng = Range("J2:J9")
For Each c In myrng
If c.Value 0 Then
ActiveSheet.AutoFilterMode = False
i = c.Row - 1
Columns(i).AutoFilter Field:=1, Criteria1:="0", Operator:=xlAnd
'do your copy/paste here
ActiveSheet.AutoFilterMode = False
End If
Next
End Sub



Mike F
wrote in message
...
Thank you for all who've helped me learn VBA through this group - you
guys are the best. I have yet another question and hope someone can
help. I know how to Autofilter in VBA, but have reached a point where
I don't know how to filter data based on criteria.

I have a list of data with 8 fields...the fieldnames are in cells
A1:H1, and the list of data starts in A2 and goes down a few 100
rows.
In cells J2:J9, I have numbers. J2 represents column A; J3 represents
column B, J4 is for column C, etc. Now, what I need to do is filter
the list based based on what number is in the J column. To make it
easier, I'll give an example.


The code should look at the number in J2. If it is greater than 0
(there are no negative numbers), then I need column A to be filtered
to show values 0. I know what I have to do once this filter is
achieved - in other words, once i achieve this filter, I copy the
data
in this column and paste it elsewhere - i know how to do that. After
I
do that, I want to unfilter and have the code go to check the value
in
J3. If that is greater than 0, then I want column B filtered. Again,
I
will copy that data over to another place. Next, column B will be
unfiltered and then the code will go to J4. If J4 = 0, then I'll go
to
J5. If J5 0, then I want column D filtered, etc. etc. This will end
once we get to J9, and see if the last column needs to be filtered or
not (J90 gets column H filtered.)


Here's a summary of which of the cells in column J correspond to the
columns:


J2 corresponds with column A
J3 corresponds with column B
J4 corresponds with column C
J5 corresponds with column D
J6 corresponds with column E
J7 corresponds with column F
J8 corresponds with column G
J9 corresponds with column H


I hope i was able to explain this so you can understand. Please let
me
know if you have any questions. Thanks in advance!

Filtering Data Based On Different Criteria
 

On Feb 10, 9:42*am, "Mike Fogleman"
wrote:
This should be real close to what you want. Watch for duplicate variable
names with your existing code.

Sub Marine()
Dim c As Range, myrng As Range
Dim i As Integer

Set myrng = Range("J2:J9")
* * For Each c In myrng
* * * * If c.Value 0 Then
* * * * * * ActiveSheet.AutoFilterMode = False
* * * * * * i = c.Row - 1
* * * * * * Columns(i).AutoFilter Field:=1, Criteria1:="0", Operator:=xlAnd
* * * * * * 'do your copy/paste here
* * * * * * ActiveSheet.AutoFilterMode = False
* * * * End If
* * Next
End Sub

Mike wrote in message

...



Thank you for all who've helped me learn VBA through this group - you
guys are the best. I have yet another question and hope someone can
help. I know how to Autofilter in VBA, but have reached a point where
I don't know how to filter data based on criteria.

I have a list of data with 8 fields...the fieldnames are in cells
A1:H1, and the list of data starts in A2 and goes down a few 100
rows.
In cells J2:J9, I have numbers. J2 represents column A; J3 represents
column B, J4 is for column C, etc. Now, what I need to do is filter
the list based based on what number is in the J column. To make it
easier, I'll give an example.

The code should look at the number in J2. If it is greater than 0
(there are no negative numbers), then I need column A to be filtered
to show values 0. I know what I have to do once this filter is
achieved - in other words, once i achieve this filter, I copy the
data
in this column and paste it elsewhere - i know how to do that. After
I
do that, I want to unfilter and have the code go to check the value
in
J3. If that is greater than 0, then I want column B filtered. Again,
I
will copy that data over to another place. Next, column B will be
unfiltered and then the code will go to J4. If J4 = 0, then I'll go
to
J5. If J5 0, then I want column D filtered, etc. etc. This will end
once we get to J9, and see if the last column needs to be filtered or
not (J90 gets column H filtered.)

Here's a summary of which of the cells in column J correspond to the
columns:

J2 corresponds with column A
J3 corresponds with column B
J4 corresponds with column C
J5 corresponds with column D
J6 corresponds with column E
J7 corresponds with column F
J8 corresponds with column G
J9 corresponds with column H

I hope i was able to explain this so you can understand. Please let
me
know if you have any questions. Thanks in advance!- Hide quoted text -

- Show quoted text -

MIke,

Thanks for the response but the programming line of

Columns(i).AutoFilter Field:=1, Criteria1:="0", Operator:=xlAnd

yielded a run time error of '1004' - Autofilter method of Range class
failed.

Not sure what to do from here. Thanks!

Filtering Data Based On Different Criteria
 

It works properly on my test sheet!!

Mike F
wrote in message
...
On Feb 10, 9:42 am, "Mike Fogleman"
wrote:
This should be real close to what you want. Watch for duplicate variable
names with your existing code.

Sub Marine()
Dim c As Range, myrng As Range
Dim i As Integer

Set myrng = Range("J2:J9")
For Each c In myrng
If c.Value 0 Then
ActiveSheet.AutoFilterMode = False
i = c.Row - 1
Columns(i).AutoFilter Field:=1, Criteria1:="0", Operator:=xlAnd
'do your copy/paste here
ActiveSheet.AutoFilterMode = False
End If
Next
End Sub

Mike wrote in message

...



Thank you for all who've helped me learn VBA through this group - you
guys are the best. I have yet another question and hope someone can
help. I know how to Autofilter in VBA, but have reached a point where
I don't know how to filter data based on criteria.

I have a list of data with 8 fields...the fieldnames are in cells
A1:H1, and the list of data starts in A2 and goes down a few 100
rows.
In cells J2:J9, I have numbers. J2 represents column A; J3 represents
column B, J4 is for column C, etc. Now, what I need to do is filter
the list based based on what number is in the J column. To make it
easier, I'll give an example.

The code should look at the number in J2. If it is greater than 0
(there are no negative numbers), then I need column A to be filtered
to show values 0. I know what I have to do once this filter is
achieved - in other words, once i achieve this filter, I copy the
data
in this column and paste it elsewhere - i know how to do that. After
I
do that, I want to unfilter and have the code go to check the value
in
J3. If that is greater than 0, then I want column B filtered. Again,
I
will copy that data over to another place. Next, column B will be
unfiltered and then the code will go to J4. If J4 = 0, then I'll go
to
J5. If J5 0, then I want column D filtered, etc. etc. This will end
once we get to J9, and see if the last column needs to be filtered or
not (J90 gets column H filtered.)

Here's a summary of which of the cells in column J correspond to the
columns:

J2 corresponds with column A
J3 corresponds with column B
J4 corresponds with column C
J5 corresponds with column D
J6 corresponds with column E
J7 corresponds with column F
J8 corresponds with column G
J9 corresponds with column H

I hope i was able to explain this so you can understand. Please let
me
know if you have any questions. Thanks in advance!- Hide quoted text -

- Show quoted text -

MIke,

Thanks for the response but the programming line of

Columns(i).AutoFilter Field:=1, Criteria1:="0", Operator:=xlAnd

yielded a run time error of '1004' - Autofilter method of Range class
failed.

Not sure what to do from here. Thanks!