Using Named Ranges for VLookups

Every day,you import a file of sales data from a chain of retail stores.The file includes store number but not the store name.You obviously don't want to have to type store names every day, but you would like to have store names appear on all the reports that you run.

Normally,you enter a table of store numbers and names in an out-of-the way spot on a back worksheet.You can use VBA to help maintain the list of stores each day and then use the vlookup function to get store names from the list into our dataset.

The basic steps are as follows:

1. Import the data file.

2. Find all the unique store numbers in today's file.

3. See whether any of these store numbers are not in your current table of store names.

4. For any stores that are new, add them to the table and ask the user for a store name.

5. The Store Names table is now larger,so re-assign the named range used to describe the store table.

6. Use a VLOOKUP in the original dataset to add a store name to all records.This VLOOKUP references the named range of the newly expanded Store Names table.

The following code handles these six steps:

Sub ImportData()

' This routine imports sales.csv to the data sheet

1 Check to see whether any stores in column A are new

1 If any are new, then add them to the StoreList table

Dim WSD As Worksheet

Dim WSM As Worksheet

Dim WB As Workbook

Set WB = ThisWorkbook

' Data is stored on the Data worksheet

Set WSD = ThisWorkbook.Worksheets("Data")

1 StoreList is stored on a menu worksheet

Set WSM = ThisWorkbook.Worksheets("Menu")

' Open the file. This makes the csv file active Workbooks.Open Filename:="C:\Sales.csv"

' Copy the data to WSD and close

Range("A1").CurrentRegion.Copy Destination:=WSD.Range("A1") ActiveWorkbook.Close SaveChanges:=False

' Activate the data sheet. Find a list of unique stores from column A WSD.Activate

FinalRow = Cells(65536, 1).End(xlUp).Row

Range("A1").Resize(FinalRow, 1).AdvancedFilter Action:=xlFilterCopy, _ CopyToRange:=Range("Z1"), Unique:=True

1 For all the unique stores, see whether they are in the

0 0

Post a comment