Using Variable in Worksheets (2000 SR1)

Topic

New Reply

I’m trying to figure out a way to use variables in the Worksheets( code below, rather than having to hard-code the sheet name. The list of sheets to be used is found on another spreadsheet.

The code I have so far is:

Dim Store As Range
Dim Stores As Range
Dim Product As Range
Dim Products As Range

Dim StartRow As Integer
Dim AllocFile As String

AllocFile = “ByStore Allocation Calculations.xls”

Set Stores = Range(“=OFFSET(SetUp!$A$2,0,0,COUNTA(SetUp!$A:$A)-1)”)
Set Products = Range(“=OFFSET(SetUp!$C$2,0,0,COUNTA(SetUp!$C:$C)-1)”)

For Each Store In Stores
StartRow = 28
For Each Product In Products
Worksheets(“3000”).Range(“H” & StartRow).Formula = “=VLOOKUP($C$1,'[” & AllocFile & “]” & Product & “‘!$A$33:$Q$218,17,FALSE)”

StartRow = StartRow + 1
Next
Next

Rather than Worksheets(“3000”)…, I’d like to replace the “3000” with the variable Store.

Replacing “3000” with Store or “” & Store & “” or other combinations haven’t worked for me so far…is there a way to do this?

Thanks for any help you could offer…

Reply | Quote

Replies

Edited by gwhitfield on 16-Jul-01 07:05. Long line split

From your explanation, I am not completely clear on exactly what you are trying to do. The variable store is a range, and that is not something that can be used as an index into to collection Worksheets.

It the cell referenced by Store contains a the name or index number of a worksheet, then you should be able to do the following:

Worksheets(Store.Value).Range("H" & StartRow).Formula = _
   "=VLOOKUP($C$1,'[" & AllocFile & "]" & Product & "'!$A$33:$Q$218,17,FALSE)"

Reply | Quote

Thanks, Legere. This almost worked perfectly – once I changed it to

Worksheets(“” & Store.Value & “”).Range…

it ran like a charm! You’ve helped me save my team about 15 hours of manual data entry with this timely tip!

Thank you muchly!

Reply | Quote