Results 1 to 9 of 9
  1. #1
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Dallas plus 20 miles or so, Texas, USA
    Posts
    876
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Dynamic Ranges (2000 SR1 )

    Hi all,

    How do I change a range name definition at run-time?

    For example, in Sheet 1, I have a range named "Goober" which is defined as $A$20:$A$34. If the user clears or changes A34 to blank, how do I redefine the range name "Goober" to equal $A$20:$A$33?

    This is what I have started on:

    <pre>Public Sub Worksheet_Change(ByVal Target As Range)
    Dim yopapa As Integer
    Dim yomama As Integer
    For Each cell In Range("a20:a34")
    If cell.Value <> "" Then yopapa = yopapa = yopapa + 1
    Next
    yomama = 20 + yopapa
    ThisWorkbook.Names.Add Name:="Goober", RefersToR1C1:="='Sheet1'!R20C1:R'yomama'C1"
    End Sub
    </pre>


    I keep getting an error on the 'Thisworkbook.Names.Add....' line. Almost like the syntax is not right or something. Any ideas for my rudimentary code?

    Thanks,

  2. #2
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    268
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Dynamic Ranges (2000 SR1 )

    Mike - I think the problem may be that the ThisWorkbook.Names.Add syntax expects to see a string, and you have supplied it with a mixed string and integer value.

    I used the following code to do much the same thing:
    <pre>LAddress = "=Scenarios!R" & CStr(ListRow) & "C1:R"
    LAddress = LAddress & CStr(ListRow + NameCount - 1) & "C1"

    ActiveWorkbook.Names.Add Name:=List.Name.Name, RefersToR1C1:=LAddress
    </pre>


    where "scenarios" is the name of the tab where I am inserting the new name. It was easier to assemble the string value of the range to be supplied to the activeworkbook.names.add function in "LAddress" than to write all that stuff out into a single line (or continue it on several lines.

    The weird (to my eye) name of "list.name.name" is grabbing the existing name of a defined range (supplied to the routine as a variable named "List" dimmed as an Object) and then applying it (or re-applying it) to the range defined by the ListRow. The idea is to re-set a range used as a source for a drop-down list when the user adds additional choices.

    Drop me a note if you would like a copy of the file whe I have this set up - I did it as a demo, so there is no proprietary information in it...

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

    Re: Dynamic Ranges (2000 SR1 )

    <pre> ActiveWorkbook.Names("Goober").RefersTo = "=Sheet1!$A$20:$A$33"
    </pre>

    Legare Coleman

  4. #4
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Dallas plus 20 miles or so, Texas, USA
    Posts
    876
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dynamic Ranges (2000 SR1 )

    Hi Dean,

    That is exactly what I am trying to do also. If you don't mind, I would very much appreciate a copy of the file, or at least the part which uses your posted code.

    Thanks,
    Mike

  5. #5
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    268
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Dynamic Ranges (2000 SR1 )

    Here you go...

    As I said, this was demo for a colleague (I don't like to admit that I work in the power business when you look at what is in this s/sheet!) - but there is nothing proprietary in there.

    The idea is that you can run (and manage) different scenarios on the 'scenarios' tab - I don't like the MS "Scenario Manager" as I find it too inflexible.

    As shown in this example, you can have multiple scenarios - labour costs, fuel costs and escalation, selling prices, etc - all of which can be varied independently. To create a new scenario you just enter the parameters of interest to the right of an existing scenario on that tab, then click the update scenarios button - the macro has been provided with the locations of each list of scenarios, each row of scenario names and the controls (drop down boxes) that reference each scenario list. Then when you go to the Summary tab the appropriate drop-down box will include the new scenario name as an alternative, and the appropriate values will be used on the calculation tab.

    If you need a scenario to apply to other logical groupings (say financing costs) you have to modify the 'mainline' routine CallUName and have it call UpdateNames one more time, and provide those parameters to UpdateNames (and name the appropriate ranges and dropdown box on the s/sheets).

    It sounds a lot harder than it is! Drop me a note if you would like help with it


    Cheers,

    Dean
    Attached Files Attached Files

  6. #6
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Dallas plus 20 miles or so, Texas, USA
    Posts
    876
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dynamic Ranges (2000 SR1 )

    <P ID="edit"><FONT SIZE=-1>Edited by gwhitfield on 27-Jul-01 13:19.</FONT></P>For anyone that this might help or might be interested, this is what I finally did. Pretty rudimentary, but, hey, it works for me. <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15>

    <pre>Private Sub Worksheet_Activate()
    Application.EnableCancelKey = xlDisabled
    Dim cell As Range
    Dim VL As Range
    b = 195
    Set VL = ThisWorkbook.Sheets("Journal").Range("b195:b209")
    For Each cell In VL
    If cell.Value = "" Then GoTo 10
    b = b + 1
    Next
    End
    10 ThisWorkbook.Unprotect password:=PW
    ThisWorkbook.Names.Add Name:="Vlist",_
    RefersToR1C1:="=Journal!r195c2:r" & b - 1 & "c2"
    ThisWorkbook.Protect password:=PW
    End Sub
    </pre>


    Also, I see the demo bar up there which is a really <img src=/S/groovin.gif border=0 alt=groovin width=21 height=21> idea, but I have tried putting "_", " _"," _ ",_ in as the line continuation but the darn thing won't work. If someone will tell me how to put the darn thing in maybe Geoff won't come edit my post! <img src=/S/rtfm.gif border=0 alt=rtfm width=24 height=23>

    Too late... Geoff <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15>

  7. #7
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dynamic Ranges (2000 SR1 )

    Sorry, I couldn't resist that opportunity...

    How did you try putting in the continuation character? You should be able to do just as I did- weren't you able to?

    Eileen has just implemented some changes to do with "pre" tags. They've been a bugbear for a little while now- the worst situation in the past is, when viewing wide posts in flat mode, every post in the thread was also widened.

    The changes are:
    .As you have seen, you are warned if there's any lines in Pre tags wider than 80
    .in flat mode, only the wide post is widened- all the other posts display OK.
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  8. #8
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Dynamic Ranges (2000 SR1 )

    <hr>but I have tried putting "_", " _"," _ ",_ in as the line continuation<hr>
    You have to put in a carriage return as well. The continuation character is just so that any code that gets copied and pasted doesn't throw up on broken lines, it doesn't really have anything to do with the [ pre] tags themselves.
    Charlotte

  9. #9
    Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia
    Posts
    85
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dynamic Ranges (2000 SR1 )

    Rather than doing this in code, you do have the option (at least you do in 97, I have not caught up to 2000 yet) of defining the range name with a dynamic formula (am I right in thinking that we've had threads on this before?).

    eg in the range name definition you have a formula something like
    =offset($a$20,0,0,counta($a$20:$a$100),1)
    which returns a range that is as big as the number of filled cells in a20:a100. This works fine as long as you have contiguous data.

    Jon

Posting Permissions

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