VLOOKUP For Filtered Data

[Macro Fool]

Hello All!

I am hoping that someone can help me out with this as I have spent a fair amount of time searching the Internet for an answer and what I find I cannot get to work exactly as I need it to. I have just enough Excel Macro Knowledge to be dangerous and I only request help when I really need it (which is why I don’t have many posts here). If you are looking at my code and think it’s not the most efficient, it’s because I work with what I know.

So, with that out of the way, I was wondering if someone would be able to provide a solution for this (it may be fairly easy and I am just missing it).

VLookup Help.jpg

What I need to do is put a Filter on Column B “Supplier Code” for “Supplier1” & “Supplier3”. I need to use the Filtered Data from Column A to C for a VLOOKUP in another Spreadsheet. So, I was thinking that I needed to create a Named Range (and maybe I don’t, this is what I don’t know) in order to use the Filtered Data in the VLOOKUP. This is what I am thinking the VLOOKUP would look like in the actual Spreadsheet (not in the VBA Code: =VLOOKUP(E2,Supplier_Number_Dealer_Cross.xls!VLookSel,3,FALSE)”

What seems to be happening is that the VLOOKUP is looking at ALL of the Data in Columns A to C, not just the Filtered Data. When I “Step Into” and I get to the “Range(rnVisibleVL1(1, 3), rnVisibleVL(1, 1)).Select” & “Range(Selection, Selection.End(xlDown)).Select”, I can see it selecting the Correct Information, but then when I execute the VLOOKUP, it returns Information that was not “Visible”.

Below is how I have it coded right now:

Please Login or Register  to view this content.

Please let me know if you have any questions or need anything else from me. Thank you all very much for your time and help. I really do appreciate it.

[PCI]

"Please let me know if you have any questions or need anything else from me"
Yes please attach an Excel file
You could see for a formula using SUMPRODUCT and SUBTOTAL ...

[Macro Fool]

I can’t attached the actual files, so I did a quick example of what I am looking to solve. I have attached an Excel Spreadsheet with the Macro (“SUPXRef”).

In the Screenshot below, you can see the following:

Supplier Cust# for Supplier1 is 12345 = Our Cust# 001976
Supplier Cust# for Supplier1 is 98765 = Our Cust# 002467
Supplier Cust# for Supplier3 is 43210 = Our Cust# 007891

Source Data.jpg

Please note that the Supplier2 also has Supplier Cust# 12345, 98765, and 43210, but they point to different Our Cust#s.

Supplier2.jpg

When I run the Macro, I get what is below (without the “should be”s):

After Macro.jpg

Please let me know if you need anything else. Thank you very much, again, for all of your time and help.

[Macro Fool]

Okay, I looked through the "Similar Threads" below and I couldn't find anything that exactly fixed my Issue. I also did a search on "Vlookup On Filtered Data" and most of the responses were not how to use it in a Macro.

I believe the Spreadsheet I attached is a perfect example of what is happening and may be helpful to someone in helping me with a Solution.

Thank you all very much, again, for all of your time and help. This will be a huge help as I need to use this in about 12 different spreadsheets.

[PCI]

With a formula
Try next one and confirm using Control + Shift + Enter
=VLOOKUP(G1,IF(SUBTOTAL(3,OFFSET(A2:A10,ROW(A2:A15)-MIN(ROW(A2:A15)),0,1)),A2:C15),3,0)

[Macro Fool]

With a formula
Try next one and confirm using Control + Shift + Enter
=VLOOKUP(G1,IF(SUBTOTAL(3,OFFSET(A2:A10,ROW(A2:A15)-MIN(ROW(A2:A15)),0,1)),A2:C15),3,0)

Hello PCI,

Thank you very much for your Solution.

However, are you sure the Solution you presented is for me? I have nothing in "G1" and I am not trying to Subtotal anything. Please let me know if I am missing something in your Solution.

I am looking to get a VLOOKUP Formula (the VBA Code) that will use only the Visible Data after a Filter has been applied in the "Table_array".

[PCI]

I apologise I should have attach your file
See here and comment