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:
Workbooks.Open Filename:= _
"C:\Users\myusername\Desktop\Macro\Supplier_Number_Dealer_Cross.xls"
Range("A2").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.AutoFilter
ActiveSheet.Range("$A$2:$T$65500").AutoFilter Field:=2, Criteria1:= _
"=SUPPLIER1", Operator:=xlOr, Criteria2:="=SUPPLIER3"
Dim rnVisibleVL As Range
Dim rnVisibleVL1 As Range
Set rnVisibleVL = ActiveSheet.Rows("3:65500").SpecialCells(xlVisible)
Set rnVisibleVL1 = ActiveSheet.Rows("3:65500").SpecialCells(xlVisible)
Range(rnVisibleVL1(1, 3), rnVisibleVL(1, 1)).Select
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.UsedRange.Name = "VLookSel"
Workbooks(NEWWB).Activate
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-1],[Supplier_Number_Dealer_Cross]Original!VlookSel,3,FALSE)"
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.
Bookmarks