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
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Sort by date Sort by votes
The easiest option would be to put the lookup table on a sheet in the workbook containing the macro, then you could just refer to that sheet.
 
Upvote 0
Fluff said:
The easiest option would be to put the lookup table on a sheet in the workbook containing the macro, then you could just refer to that sheet.
Click to expand...
Exactly, but how do I make the VBA code remember what this data is. It is generally static and does not change. So then Im not really automating much if I have to still manually add the reference table to the worksheet every time, and just make the VBA reference that.

Another option is to just save a file somewhere else and make the VBA refer to that file?
 
Upvote 0
Where is the macro located?
 
Upvote 0
Yup, put the lookup table on a sheet in your Personal.xlsb file & then you can just refer to that, rather than copy/pasting it to a new sheet for every report.
 
Upvote 0
And then I do the steps to open that file and vlook, as if it were any other? For example. I went through the steps to reference an external file. And I believe I got that to work just fine, it looks like this.

VBA Code: 
    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:\DEPARTMENT\NAME\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)

So the "workbooks.open filename" command would then just be navigating to the personal workbook file extension? Or is there a better way? Also, to close it, is the command simply "workbooks.close filename"?

Or actually, do I not have to give it an open command, since its already technically open, just hidden?
 
Last edited:
Upvote 0
You're personal workbook will already be open, otherwise you would not be able to run the code to begin with. ;)

Do you want to keep the formula in the cells, or do you just one the value?
 
Upvote 0
Fluff said:
You're personal workbook will already be open, otherwise you would not be able to run the code to begin with. ;)

Do you want to keep the formula in the cells, or do you just one the value?
Click to expand...
DUH! that makes sense when someone else says it haha.

I suppose just the value is suitable since I wont be adding to either the lookup table or my destination data after I run this code. Its a one time shot.

Also, when I was messing with the previous method, I ran into a snag because as youll see Im dealing with the file that my system spits out as "sales by style" so sometimes, if I were to have multiple copies open, it would say "sales by style (1)" etc, so its hard to build that into the code. I know there are commands like switching back to the "original workbook" or something to that effect, versus defining a file name. Not sure if the personal workbook route you suggested requires that consideration?
 
Upvote 0
In that case you can use something like this.
Rich (BB code): 
Sub Guitarmageddon()
   Dim Cl As Range
   Dim Dic As Object
   
   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 the value in red to match the sheet name that holds the lookup table
 
Upvote 0
You must log in or register to reply here.

Similar threads

F
  • Question
using vlookup function in VBA
Replies
1
Views
87
Rijnsent
Rijnsent
C
  • Solved
If VLOOKUP is @NA check Table 2
Replies
9
Views
433
Joe4
Joe4
C
  • Question
How to get my macro to stop at the last recognized line
Replies
6
Views
197
Coco1
C
F
  • Question
Filtering/Copying a table via VBA.
Replies
2
Views
100
frobeniusabnorm
F
B
  • Question
Issues using "Textsplit" formula in VBA
Replies
3
Views
627
MARK858
MARK858

Forum statistics

Threads
1,214,606
Messages
6,120,490
Members
448,967
Latest member
visheshkotha

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