Filtering Data Based On Different Criteria

[haas786]

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 (J9>0 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!

 

[Mike Fogleman]

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

 

[haas786]

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











- 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!

 

[Mike Fogleman]

It works properly on my test sheet!!

Mike F

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











- 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!