+ Reply to Thread
Results 1 to 6 of 6

referring to a range by variable

  1. #1
    Forum Contributor
    Join Date
    06-27-2006
    Posts
    310

    referring to a range by variable

    I have a named ranges on a sheet....team_1, team_2 , team_3... etc

    How can I refer to them in code using a variable?

    something like his

    dim x as integer
    dim myrange as range

    For x = 1 To 30

    Set myrange = range(team_x)
    'do something to myrange

    Next x

  2. #2
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,196
    Quote Originally Posted by SuitedAces
    I have a named ranges on a sheet....team_1, team_2 , team_3... etc

    How can I refer to them in code using a variable?

    something like his

    dim x as integer
    dim myrange as range

    For x = 1 To 30

    Set myrange = range(team_x)
    'do something to myrange

    Next x
    Hi,

    Please Login or Register  to view this content.
    seems to do this.

    hth
    ---
    Si fractum non sit, noli id reficere.

  3. #3
    Forum Contributor
    Join Date
    06-27-2006
    Posts
    310
    Thank you for responding.

    I ran the test code and at line .....Set myrange = Range(myname)

    I get an error :

    Method 'Range of Object'_ Worksheet failed

  4. #4
    Forum Contributor
    Join Date
    06-27-2006
    Posts
    310
    Brian

    I got past that error by change the line
    myname = "team" & x to myname = "team_" & x

    the code works great , thank you

    I just don't understand it completely .
    I have never declared variables in this way

    With the line :
    Dim c, myrange
    how is c interpreted as each cell ?

  5. #5
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,196
    Quote Originally Posted by SuitedAces
    Brian

    I got past that error by change the line
    myname = "team" & x to myname = "team_" & x

    the code works great , thank you

    I just don't understand it completely .
    I have never declared variables in this way

    With the line :
    Dim c, myrange
    how is c interpreted as each cell ?
    Hi,

    you had the error because you didn't have Team 1 2 & 3 etc as Named range. The variable name is made in a string, and used from there, same as the ("A1:A" & lastrow), or a follow on of the Indirect( ) formula, all allow address names to be 'created'.

    the Dim I used was lazy and should not be used, I normally Dim x as string, y as string then on other lines the integers etc, but also run 'Option Explicit', so when I'm lazy I just 'Dim' and allow the system to assign type.

    But, so long as you had it working, that's good.
    Thanks for the response.
    ---

  6. #6
    Forum Contributor
    Join Date
    06-27-2006
    Posts
    310
    Thank you for the help

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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