looping through two (2) named ranges to pair up each cell in 2 stacked columns

[sveen00]

I have a two named ranges: DateRng and StoreRng. I need to create two columns that stack all the days in DateRng with each store in StoreRng.

For Example: let's say DateRng contains the 30 dates in a month - and StoreRng contains 30 store numbers (ie: "Fac-0001", "Fac-0005", "Fac-2321", etc). I would like to end up with all 30 dates listed in Column A with the first store in Column B, then stack the dates again under that with the next store, etc for the rest of the stores. Essentially, I'd have 900 rows in two columns.

I hope this makes sense. I'll paste the current code I have (which works) but it takes over 4 mins to run a list of 84 dates and 26 stores. Now, 4 mins is not crazy bad, but I'm doing this on 7 more tabs - and then tackling another sheet with 84 days and 314 stores (7 more times).

Someone please school me in the most efficient way to do this, as my hacked loop code can't be the best way.

Thanks,
Steve

Sub LoopDateStore()

Dim StoreRng As Range   'list of stores
Dim DateRng As Range  'list of LY Dates
Dim LYDate As String    'current date in loop
Dim StoreNum As String  'current store in loop

Dim h As Long  'total number of stores
Dim i As Long  'store loop counter
Dim j As Long  'total number of days
Dim k As Long  'date loop counter
Dim Rownum As Long  ' row placeholder

'record start time
Worksheets("Setup").Activate
Cells(11, 12).Value = Now()


'Proj ID Basis Tab
Set StoreRng = Worksheets("Setup").Range("StoreList").Columns(1)
Set DateRng = Worksheets("Setup").Range("LYDates").Columns(1)
h = StoreRng.Rows.Count
j = DateRng.Rows.Count

Rownum = 8
Worksheets("Proj ID Basis").Activate

For i = 1 To h
    StoreNum = StoreRng.Cells(i, 1)
    For k = 1 To j
        LYDate = DateRng.Cells(k, 1)
        Cells(k + Rownum, 4).Value = StoreNum
        Cells(k + Rownum, 2).Value = LYDate
    Next k
Rownum = Rownum + j
Next i

'record End time
Worksheets("Setup").Activate
Cells(12, 12).Value = Now()

End Sub

 

[sveen00]

I wanted to post one other layer to this - the column I am pasting the store numbers in is part of a Smartview Named Range for an Essbase retrieval. Now, I'm not connected when I run this, but maybe pasting (changing) something in a Smartview named range causes some delay?? Wish I knew the answer to that, maybe one of you are familiar with Smartview? - Steve