+ Reply to Thread
Results 1 to 4 of 4

Dynamic Variable Name during Runtime?

  1. #1
    Forum Contributor
    Join Date
    09-23-2007
    Location
    Melbourne, Australia
    Posts
    176

    Dynamic Variable Name during Runtime?

    Hi

    Does anyone know how to creat a dynamic variable name. I actually want to create a dynamic range variable though I've tried so many things now and its not looking good. Basically I don't know how many different variables I'll need each day so if I was to run a macro the idea is it would count the number of values or ranges in a worksheet and allocate a range variable to each one of those ranges. I had some ideas using a string and a count function but can't get it to work. Could anyone suggest anything here.

    Thanks

  2. #2
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591
    Hi

    I don't believe that you can build dynamic variables like this, but why don't you use an array to contain the ranges? You can then loop through the array to get to the range address. The array can be dynamic and not a fixed length, so it can grow as required.

    How about putting up an example of what you are trying to achieve.


    rylo

  3. #3
    Forum Contributor
    Join Date
    09-23-2007
    Location
    Melbourne, Australia
    Posts
    176

    Smile Thanks

    Hi rylo

    Thanks for that. I think that just may work. Just to give you an example,
    with reference to below column A contains the names and Column B contains serial numbers corresponding to each name

    I want to move through the names in column A and each time I get to bob I want to record the corresponding cell (range) in Column B into a range. So the first range in total would be called Bob, and the second range would be called Bill. The range Bob contains a whole range of serial numbers and so does the range Bill. There could be literally thousands of ranges and I won't know until I run the macro (which is why a dynamic variable name was thought of).

    In the end I will loop though another sheet containg serial numbers and add a 1 to bob whenever one of those serial numbers (from the Bob range) turns up in the column. This way I can get a tally. It will become a little more complicated than this but that is the general Idea.

    Thanks for getting back to me on this, I realy appreciate the help.

    1__A _______B___
    2 Bob.....4545664
    3 Bob.....4525554
    4 Bob.....2154556
    5 Bill......1122234
    6 Bil.......2154859
    Last edited by danny2000; 01-19-2008 at 02:48 AM.

  4. #4
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591
    Hi

    If you have a series of serial numbers, and you want to get a total by the "owner" (ie bob, bill etc), then there are much easier ways than trying to build ranges.

    How about you set up a small sample file of the whole things with (from what I can see) 2 sheets. The first sheet will have the serial number / name mapping, and the second sheet will contain the list of serial numbers.

    Give a sample output of what you would expect to see.

    rylo

+ 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1