use a lookup table within my vba code?

G

Guitarmageddon

Board Regular
Joined
Dec 22, 2014
Messages
159
So I have a simple vba to reformat a report I use constantly. Its pretty simple, and the report Im reformatting only has one tab.

I want to add onto that code, and basically insert a column which is a vlookup from a store number, to its parent warehouse.This means Im inserting a tab, pasting the lookup data, and just doing a vlook.

However, Im confused on how I can make that vlookup reference table "live" within my vba. Can anyone assist? I hope this makes sense.

So here is what Ive got. This is me inserting the column, and renaming it "parent rdc", followed by me inserting the tab "rdc lookup" that I presumably needed to paste the lookup data for the vba to use. Then, when I want to do the vlookup, the first chunk of commented steps is me pulling the vlookup data from some other sheet. This is basically where I want to make that vlookup data reside within the VBA code. Is that possible?

The second comment is me trying to remember what the dynamic autofill vba was, versus a defined range (since my report length is always different.

VBA Code: 
    Columns("N:N").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("N8").Select
    ActiveCell.FormulaR1C1 = "PARENT RDC"
    Range("O11").Select
    Columns("N:N").ColumnWidth = 8
    Sheets.Add After:=ActiveSheet
    Sheets("Sheet1").Select
    Sheets("Sheet1").Name = "RDC LOOKUP"
    
    'Windows("Daily AR Orders 2019.xlsx").Activate
    'Range("A1:C1").Select
    'Range(Selection, Selection.End(xlDown)).Select
    'Selection.Copy
    'Windows("Sales by Style Report.xlsx").Activate
    'Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
        'SkipBlanks:=False, Transpose:=False
    
    ActiveSheet.Paste
    Sheets("Page1").Select
    Range("N9").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],'RDC LOOKUP'!C1:C3,3,0)"
    'THE NEXT STEPS NEED TO GET CHANGED TO THE AUTOFILL TO END OF DATA
    Range("N9").Select
    Selection.AutoFill Destination:=Range("N9:N279")
    Range("N9:N279").Select

The data that I am trying to make vba use as lookup values is here.
SiteCMDPARENT RDC
01100HHM60001
02100QUM60001
02309QUM60001
04100CPM60001
05100CLM60001
09101SDM70001
10101PNM70001
11100MRM70001
13100TWM70001
14100YUM70001
18100SCM60001
05102CLM60001
05300CLM60001
09100SDM70001
15100KBM70001
16100IWM70001
02212QUM60001
05201CLM60001
05202CLM60001
05207CLM60001
05233CLM60001
05315CLM60001
10252PNM70001
02202QUM60001
02211QUM60001
03100ELM60001
05206CLM60001
08100ALM60001
10113PNM70001
10230PNM70001
10251PNM70001
10321PNM70001
10343PNM70001
13307TWM70001
15101KBM70001
18101SCM60001
05301CLM60001
10320PNM70001
10332PNM70001
15305KBM70001
16001IWM70001
02214QUM60001
05305CLM60001
09700SDM70001
16300IWM70001
02301QUM60001
02305QUM60001
02500QUM60001
09500SDM70001
10001PNM70001
11300MRM70001
05211CLM60001
04200CPM60001
13379TWM70001
15303KBM70001
01200HHM60001
02304QUM60001
04300CPM60001
10314PNM70001
10352PNM70001
10353PNM70001
10362PNM70001
11200MRM70001
15301KBM70001
15400KBM70001
05309CLM60001
09300SDM70001
13305TWM70001
13314TWM70001
05310CLM60001
05311CLM60001
05314CLM60001
05316CLM60001
12100BAM70001
13306TWM70001
14400YUM70001
02205QUM60001
02302QUM60001
02306QUM60001
02307QUM60001
04400CPM60001
05209CLM60001
05302CLM60001
05303CLM60001
05304CLM60001
05306CLM60001
09301SDM70001
10303PNM70001
10304PNM70001
10322PNM70001
10323PNM70001
10324PNM70001
10325PNM70001
10326PNM70001
10331PNM70001
10333PNM70001
10341PNM70001
10342PNM70001
10344PNM70001
10413PNM70001
10451PNM70001
11301MRM70001
13303TWM70001
14301YUM70001
05001CLM60001
05500CLM60001
15001KBM70001
13380TWM70001
01500HHM60001
04500CPM60001
03400ELM60001
10229PNM70001
13101TWM70001
15200KBM70001
16107IWM70001
18301SCM60001
18703SCM60001
18302SCM60001
09103SDM70001
13001TWM70001
12300BAM70001
10118PNM70001
10500PNM70001
05200CLM60001
10364PNM70001
16200IWM70001
12301BAM70001
16301IWM70001
10003PNM70001
02802QUM60001
02801QUM60001
60001EC STOW60001
70001WC STOW70001
16101IWM70001

This is the report that I am attempting to vlook the data into. Column N will reference the existing "SITE" column M, and use the spoiler data I provided above. I have already executed the vlookup below in N as it should end up playing out. This is like a .5 second vlookup to do in normal excel, I just dont get how to make the VBA keep the reference table within itself. Let me know if I can clarify anything!
sbs.PNG
 
Sort by date Sort by votes

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
This replaces all the code you posted in your op
VBA Code: 
Sub Guitarmageddon()
   Dim Cl As Range
   Dim Dic As Object
   
   With ActiveWorkbook.Sheets("Page1")
      .Columns("N").Insert
      .Range("N8").Value = "PARENT RDC"
      .Columns("N").ColumnWidth = 8
   End With
   Set Dic = CreateObject("scripting.dictionary")
   With ThisWorkbook.Sheets("List")
      For Each Cl In .Range("A2", .Range("A" & Rows.Count).End(xlUp))
         Dic(Cl.Value) = Cl.Offset(, 2).Value
      Next Cl
   End With
   With ActiveWorkbook.Sheets("Page1")
      For Each Cl In .Range("N9:N" & .Range("M" & Rows.Count).End(xlUp).Row)
         Cl.Value = Dic(Cl.Offset(, -1).Value)
      Next Cl
   End With
End Sub
Change "List" to match the sheet name containing the lookup table.
 
Upvote 0
Fluff said:
This replaces all the code you posted in your op
VBA Code: 
Sub Guitarmageddon()
   Dim Cl As Range
   Dim Dic As Object
  
   With ActiveWorkbook.Sheets("Page1")
      .Columns("N").Insert
      .Range("N8").Value = "PARENT RDC"
      .Columns("N").ColumnWidth = 8
   End With
   Set Dic = CreateObject("scripting.dictionary")
   With ThisWorkbook.Sheets("List")
      For Each Cl In .Range("A2", .Range("A" & Rows.Count).End(xlUp))
         Dic(Cl.Value) = Cl.Offset(, 2).Value
      Next Cl
   End With
   With ActiveWorkbook.Sheets("Page1")
      For Each Cl In .Range("N9:N" & .Range("M" & Rows.Count).End(xlUp).Row)
         Cl.Value = Dic(Cl.Offset(, -1).Value)
      Next Cl
   End With
End Sub
Change "List" to match the sheet name containing the lookup table.
Click to expand...
Ok I think this is where Im getting hung up. When changing "list" to reference the lookup table, I can do that one of two ways.
a.)I store it on sheet one of my personal workbook. So do I have to use something like this to expose the personal?
VBA Code: 
Windows("PERSONAL.XLSB").Visible = True
b.)I reference my external file by using
VBA Code: 
    Workbooks.Open Filename:="S:\file\name\CMDLOOKUP.xlsx"
    Sheets("cmd look up").Select
and from there, switch back to the original workbook (not sure the VBA for this) and conduct the vlookup? Sorry, this is totally frying my brain right now.
 
Upvote 0
Just copy your lookup table to a sheet in your personal workbook, call that sheet List & use the code I provided.
 
Upvote 0
Fluff said:
Just copy your lookup table to a sheet in your personal workbook, call that sheet List & use the code I provided.
Click to expand...
Ok So I did that and now I just look between these two steps infinitely
VBA Code: 
         Dic(Cl.Value) = Cl.Offset(, 2).Value
      Next Cl
 
Upvote 0
To clarify, this is now my *entire* macro. I have commented the area that specifically refers to our conversation of conducting the lookup. Its at the bottom. The script gets as far as creating "PARENT RDC" then I see no more changes,
VBA Code: 
Sub SALESBYSTYLE_test()
'
' SALESBYSTYLE Macro
'
' Keyboard Shortcut: Ctrl+Shift+S
'
    Cells.Select
    Selection.UnMerge
    Rows("1:3").Select
    Range("A3").Activate
    Selection.Delete Shift:=xlUp
    Columns("A:A").ColumnWidth = 31
    Range("B9").Select
    Rows("8:8").RowHeight = 21
    Rows("8:8").Select
    With Selection
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    ActiveWindow.Zoom = 90
    Columns("A:A").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Rows("8:8").Select
    Selection.AutoFilter
 
    Range("a8").Select
    ActiveCell.FormulaR1C1 = "SITE-STYLE"
 
    Range("A9").Select
    ActiveCell.FormulaR1C1 = "=CONCATENATE(RC[12],RC[14])"

    Dim n As Long
    n = Range("B" & Rows.Count).End(xlUp).Row
    Range("A9").AutoFill Destination:=Range("A9:A" & n)
 
    Range("b8").Select
    Selection.Copy
    Range("A8").Select
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False

    Columns("B:B").ColumnWidth = 7
    Columns("C:C").ColumnWidth = 8
    Columns("D:D").ColumnWidth = 9.29
    Columns("E:E").ColumnWidth = 11.14
    Columns("F:F").ColumnWidth = 8.29
    Columns("G:G").ColumnWidth = 10.29
    Rows("8:8").RowHeight = 33.75
    Columns("H:H").ColumnWidth = 8.71
    Columns("I:I").ColumnWidth = 9.57
    Columns("K:K").ColumnWidth = 12.57

'what follows is the RDC lookup
   Dim Cl As Range
   Dim Dic As Object

   With ActiveWorkbook.Sheets("Page1")
      .Columns("N").Insert
      .Range("N8").Value = "PARENT RDC"
      .Columns("N").ColumnWidth = 8
   End With
   Set Dic = CreateObject("scripting.dictionary")
   With ThisWorkbook.Sheets("List")
      For Each Cl In .Range("A2", .Range("A" & Rows.Count).End(xlUp))
         Dic(Cl.Value) = Cl.Offset(, 2).Value
      Next Cl
   End With
   With ActiveWorkbook.Sheets("Page1")
      For Each Cl In .Range("N9:N" & .Range("M" & Rows.Count).End(xlUp).Row)
         Cl.Value = Dic(Cl.Offset(, -1).Value)
      Next Cl
   End With


End Sub
Im just really confused on what part of that code physically reaches into my personal book, sees that tab, selects that data, and conduct the lookup. It references my created "list" tab, but Im not sure how its supposed to find that in another workbook?
 
Upvote 0
Firstly, does it actually work?
If not please supply details/
 
Upvote 0
What if we tried a slightly different approach. I have this method, and I know it works. Trouble is, sometimes a (1) or (2) etc is added to the filename, so the code breaks when my original vba is referencing "sales by style" and sees "sales by style (1)"

How do we change the sales by style activate line to rather be "go find the original code workbook and activate that"?
VBA Code: 
'what follows is the RDC lookup
    Columns("N:N").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.ColumnWidth = 7.43
    Range("N8").Select
    ActiveCell.FormulaR1C1 = "PARENT RDC"
    Range("N9").Select
    Workbooks.Open Filename:="S:\REPLENISHMENT\COLIN\CMDLOOKUP.xlsx"
    Sheets("cmd look up").Select
        Windows("Sales by Style Report.xlsx").Activate
    Range("N9").Select
    ActiveCell.FormulaR1C1 = _
        "=VLOOKUP(RC[-1],'[cmdlookup.xlsx]cmd look up'!C1:C4,4,0)"
    Range("N9").Select
    Dim b As Long
    b = Range("B" & Rows.Count).End(xlUp).Row
    Range("N9").AutoFill Destination:=Range("N9:N" & b)
        'not sure on workbooks close function. use that?
 
Upvote 0
Wait stop the presses. It does work. So, it just references one column too far to the left. How do I make it look one column further to the right?
 
Upvote 0
I changed the 2 to a 3 .... works!

VBA Code: 
      For Each Cl In .Range("A2", .Range("A" & Rows.Count).End(xlUp))
         Dic(Cl.Value) = Cl.Offset(, 3).Value

By god hes done it, youre the man haha. Now I just need to understand better what you did.
 
Upvote 0
You must log in or register to reply here.

Similar threads

F
  • Question
using vlookup function in VBA
Replies
1
Views
136
Rijnsent
Rijnsent
C
  • Solved
If VLOOKUP is @NA check Table 2
Replies
9
Views
511
Joe4
Joe4
I
  • Question
VBA code to find closest sum
Replies
1
Views
177
eiloken
E
C
  • Solved
Please help me clean up my VBA script, I'm having trouble with redundancies
Replies
4
Views
128
CapRavOr
C
C
  • Question
How to get my macro to stop at the last recognized line
Replies
6
Views
294
Coco1
C

Forum statistics

Threads
1,216,066
Messages
6,128,571
Members
449,458
Latest member
gillmit

Share this page

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