Filter Data based on 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 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!

 

[Jim Rech]

Typically people who post a list of requirements for a macro and imply
they'd like someone to write it for them don't get a lot of responses. A
far better strategy seems to be to write it yourself and bring particular
sticking points here. A good way to start that in this case is to record a
macro as you set an autofilter and then work on how to replace the criterion
you enter manually with the value in a cell. And you learn so much more
that way.

 

[haas786]

Typically people who post a list of requirements for a macro and imply
they'd like someone to write it for them don't get a lot of responses.  A
far better strategy seems to be to write it yourself and bring particular
sticking points here.  A good way to start that in this case is to record a
macro as you set an autofilter and then work on how to replace the criterion
you enter manually with the value in a cell.  And you learn so much more
that way.

--










- Show quoted text -

Jim,

To be quite honest with you, i wasn't asking someone to write the code
for me. I was asking for help in writing code - I am not an expert
programmer like yourself and this is not something i do for a living.
Anything i have learned has been through the macro recorder, and when
I can't get past something i perceive as a more advanced programming
concept, I turn to the very knowledgeable and kind folks in this
newsgroup for help. I have learned an immense amount from people here
and have (and will continue to do so) help others with their questions
if i have answers. I don't mean to start a back and forth verbal
diarrhea between us, so if you can't help, simply ignore me. I thank
you for your advice to record the macro, etc. Like I said, I have done
that and know how to get the autofilter to work and to filter with the
criteria I specify. However, I am stuck at specifying criteria based
on values in certain cells. I made assumptions that the programmers in
this group would know that I know how to do the Autofilter, and
recognized i was struggling with the criteria portion. I shouldn't
assume.

I will repost again, and if you can't help, please just ignore me.

Thank you.

BTW, I had done the

 

[Jim Rech]

I'm glad to see that you did try the macro recorder. There was nothing to
indicate that in your post. So what I would suggest for this or other
questions in the future, is to show what you've done and ask a specific
question. For example:

"I recorded this macro which enters a literal value for the criteria:

xxxx

I want instead to enter the value in cell H2. How can I do that?"

So much easier for someone to answer than an open appeal for advice.

Now, sorry if I pissed you off, but if I didn't answer you, you might have
gotten zero responses. Would that have been better? I wanted you to
understand why you might have gotten no help.

--
Jim

Typically people who post a list of requirements for a macro and imply
they'd like someone to write it for them don't get a lot of responses. A
far better strategy seems to be to write it yourself and bring particular
sticking points here. A good way to start that in this case is to record a
macro as you set an autofilter and then work on how to replace the
criterion
you enter manually with the value in a cell. And you learn so much more
that way.

--










- Show quoted text -

Jim,

To be quite honest with you, i wasn't asking someone to write the code
for me. I was asking for help in writing code - I am not an expert
programmer like yourself and this is not something i do for a living.
Anything i have learned has been through the macro recorder, and when
I can't get past something i perceive as a more advanced programming
concept, I turn to the very knowledgeable and kind folks in this
newsgroup for help. I have learned an immense amount from people here
and have (and will continue to do so) help others with their questions
if i have answers. I don't mean to start a back and forth verbal
diarrhea between us, so if you can't help, simply ignore me. I thank
you for your advice to record the macro, etc. Like I said, I have done
that and know how to get the autofilter to work and to filter with the
criteria I specify. However, I am stuck at specifying criteria based
on values in certain cells. I made assumptions that the programmers in
this group would know that I know how to do the Autofilter, and
recognized i was struggling with the criteria portion. I shouldn't
assume.

I will repost again, and if you can't help, please just ignore me.

Thank you.

BTW, I had done the