MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

VBA code for capturing Cell.Value from Autofiltered worksheet?

  Asked By: Lloyd    Date: Feb 10    Category: MS Office    Views: 2989

I have created a database which represents an entire season's
professional baseball games. Each record (line) represents the
performance of one team in one game, and the records are arranged in
chronological order.

Here is a truncated example of what one record looks like:

May 15 955 NYY NLE J Vazquez 8 Summary Away DOG WIN

What I want to do is determine whether the subject team of this
record (NYY) won or lost each of the 3 games they played immediately
preceding this game. (Each record shows in the far right column
either WIN or LOSS.)

Since there are a varying number of other records separating the
performances by NYY, I use Autofilter to show just the NYY records:
'Offset(0,-9) gets me to NYY from where I was
ActiveCell.Offset(0, -9).Range("A1").Select
crit = Selection.Value
Selection.AutoFilter Field:=7, Criteria1:=crit

Now I'm looking at the records I want.

What I need is code to capture the WIN/LOSS values from the
3 records immediately above the record I started with,so that I can
store them in variables for later use.

I have tried several methods, but they give me the WIN/LOSS
values from the corresponding cells in the UNFILTERED database.



7 Answers Found

Answer #1    Answered By: Angelina Gardner     Answered On: Feb 10

Not necessarily what you're looking for since this is a non VBA
method, but I have uploaded a file in the Code samples folder of the
Files section of this group, called
'3 games  immediately preceding.xls'
which gives the WIN/LOSS for the previous three games played by the
team in column A, in three columns, on every row.
Ignore #NUM errors, formulae can be filled down/up, but not left/right.
No autofiltering is required. Not very elegant, but can be made so I'm

Answer #2    Answered By: Dalpat Student     Answered On: Feb 10

I am looking at the worksheet  function you provided. My problem is
that you are so far ahead of me that I am not able to translate your
formula into something that I understand and the techniques of which I
can use in my efforts down the road . . .

Since I have spent several days trying to solve the problem with VBA,
I really want to use that method rather than a worksheet function
which goes haywire when I change the layout (which happens
frequently). Also, to be honest, I have become somewhat obsessed with
the notion that there MUST be a way to use Autofilter in VBA to
accomplish my goal . . .am I mistaken?

If Autofilter absolutely cannot be used as I desire, I have already
written a VBA macro which accomplishes my goal in a very amateurish
fashion, moving up the "team" column one record  at a time until it
finds a "match", then recording the WIN/LOSS in a variable and moving
on up the line.

Thanks for the formula you provided, though--when I get some free time
I need to learn how to use some of these worksheet functions, and your
formula will be a great practice model!

Answer #3    Answered By: Priscilla Lewis     Answered On: Feb 10

I've added a macro to the file in the files area. It's called by
clicking Button 3 on the sheet after the active cell  is placed
anywhere on the row required. The macro is documented by comments in
the code.
Autofilter in VBA is a dog. The way you suggest is probably the best
and the macro I've written does pretty much what you have already done.

Answer #4    Answered By: Delbert Cooper     Answered On: Feb 10

Unfortunately I cannot view any of the files
at the present time--getting the well-loved "Firefox can't establish a
connection to the server at f1.grp.yahoofs.com." error message. I
assume that the problem will be fixed shortly; and then I will feast
my eyes upon how an expert handled it!

You really answered my question by letting me know that Autofilter
just doesn't lend itself well to VBA use. Seems a shame, but if
that's the way it is then I won't waste any more effort on it.

One last question on it, though . . .I notice when I go into the
autofilter mode, the row numbers (column to the left of "A") have
changed color and look inviting. Is there any way to use these
line/row numbers in a macro as a means of identifying the record  that
I want to reference?

Answer #5    Answered By: Myron James     Answered On: Feb 10

Now I am getting the "Document Not Found" message when I try to view
your code  in the files. No problem getting the other ones, just the
one you put in there. Did you remove it?

Answer #6    Answered By: Vidisha Pathak     Answered On: Feb 10

No I haven't removed it, maybe there's a problem with it having the
same name as the old one on your machine? Perhaps your cache or your
ISP's cache is still expecting the old one's attributes? Anyway, I've
changed its name by adding " b" to the end. Have another go.

Answer #7    Answered By: Barney Smith     Answered On: Feb 10

Got it now! I'm going to have to take awhile to figure out all the
devices you used--It's amazing that your version takes so little code
and mine takes about 24 inches of code  .