Help with code assistance

[Mekinnik]

I am tring to populate a column of cells when they have focus and all I can
seem to do is populate just one and the kicker is that it only will populate
to the second cell in the reference range

I have a validation list on sheet1, e2 validated with data from
sheet2,a1-a50 and using the code for an advanced filter search I found
online, I can get it to work but only on sheet2,e3 and no others in the
column.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row = 2 And Target.Column = 5 Then
'calculate criteria cell in case calculation mode is manual
Worksheets("Lists").Range("F2").Calculate
Worksheets("Lists").Range("AbrevList").AdvancedFilter
Action:=xlFilterCopy, _
CriteriaRange:=Sheets("Lists").Range("F1:F2"), _
CopyToRange:=Range("E3"), Unique:=False
End If
End Sub



HELP PLEASE...

 

[JLGWhiz]

Your code appears to be OK, so based on the tests that I ran, the
problem might be either how you set up your criteria, or the column that you
designate to be filtered, or both. The criteria should consist of the column
header on one row of the criteria range and a data element that is or will be
contained in that column at the time the macro fires. The filter range is
the range that you want data returned from. Since I do not know what you
have in F1 and F2, I can not tell which column you are setting the filter
criteria for, Nor do I know if Range("AbrevList") is the range you want the
filtered data returned from, although I assume it is. If the AbrevList is
also your criteria column, it won't work.

This probably don't help much but, without seeing your sheet, it is the best
I can do.

 

[Mekinnik]

Correction to my post:
I can only get it to work on sheet1,e3

I think what I need to do is change the following line to include a column
range not a cell, but I'm not sure how to do it?

CopyToRange:=Range("E3"), Unique:=False

I thinking something to the effect of:
CopyToRange:=Range.(THE CELL WITH FOCUS), Unique:=False

 

[JLGWhiz]

No, that is not the problem. I got it to return several values down the
column using your original statement. The problem is either in your criteria
set up, or your range that your are specifying to return the data from.
Again, if you are using the same range for your criteria that you are trying
to return data from, it won't work.

Your criteria has to be in one column and the data you want to return needs
to be in an adjacent column. Then it will return every cell value in the
adjacent column that corresponds to a criteria cell in the other column.

Unfortunately, the VBA help does not provide a lot of explanation on this.