Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Jan 2001
    Location
    Toronto, ON, Cayman Brac, Canada
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Using Variable in Worksheets (2000 SR1)

    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...

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using Variable in Worksheets (2000 SR1)

    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:

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

    Legare Coleman

  3. #3
    New Lounger
    Join Date
    Jan 2001
    Location
    Toronto, ON, Cayman Brac, Canada
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using Variable in Worksheets (2000 SR1)

    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!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •