Applying VLOOKUP to AutoFiltered column

[j4ttlife]

Hi All,


PROBLEM:
When I AutoFilter my data to the favourite colour as 'Blue' (check before and after images), the first cell with data changes from A2 to A4 as A2 is filtered out. I run a VLOOKUP which works fine however, my problem is that the data I receive may change daily.

For example, in this example the first Row with the favourite colour Blue is A4, but tomorrow it might be A3 or A23 etc.


QUESTION:
Is it possible to dynamically automate this process so that my macro finds the correct cell to start the macro from itself instead of me having to specify what cell to begin the VLOOKUP from (in this instance I have selected Range A4 in the code).

Here is my code:

Dim Sheet1 as Worksheet
Set Sheet1 = Sheets("Sheet1")
Dim formul As String


Range("A:C").AutoFilter Field:=3, Criteria1:="Blue"

Range("A4").Select '//I would prefer to avoid specifying what cell to start from as the data changes daily
formul = "=VLOOKUP(RC[2],Sheet2!R1C1:R5C2,2,0)" '////Pulls back the comment from the second sheet in the image below
Range("A4:A" & Cells(Rows.count, 1).End(xlUp).Row) = [formul]

If this query isn't clear then let me know and I will try to explain in further detail.

second sheet







no filter

after filter

 

[ThanP]

Hi,

This is what I understood, maybe I am wrong.
Sheet1 : Comment, Name, Favorite color
Sheet2: Color, Comment

With Sheet1,

1. You want to filter the "Favorite color" to blue
2. With only the filtered rows, you want to insert a vlookup in the column A cell for the rows, (with Sheet2 table)

Thanks

 

[j4ttlife]

Hi,

This is what I understood, maybe I am wrong.
Sheet1 : Comment, Name, Favorite color
Sheet2: Color, Comment

With Sheet1,

1. You want to filter the "Favorite color" to blue
2. With only the filtered rows, you want to insert a vlookup in the column A cell for the rows, (with Sheet2 table)

Thanks

Hi,

Yes I insert an AutoFilter, in this case favourite colour to blue.

Then I add a VLOOKUP in the first non header row on the AutoFiltered sheet (in this case range A4)

My question was regarding my data changing next week, for example what if the first row in my data next time where the favourite colour is Blue is range A9.

Is there a way to change the VLOOKUP macro so that it is able to detect the first row of AutoFiltered data and start the VLOOKUP there until the last row (in this example A4 and A7).

 

[RoryA]

I think you want:

Range("A2:A" & Cells(Rows.count, 1).End(xlUp).Row).SpecialCells(xlcelltypevisible).formular1c1 = formul