Applying VLOOKUP to AutoFiltered column

j4ttlife

New Member
Joined
Feb 2, 2022
Messages
30
Platform
  1. Windows
  2. MacOS
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:

VBA 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.jpg


second sheet







no filter
no filters.jpg
after filter.jpg


after filter
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
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
 
Upvote 0
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).
 
Upvote 0
I think you want:

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

Forum statistics

Threads
1,223,911
Messages
6,175,337
Members
452,637
Latest member
Ezio2866

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top