Dynamic Ranges (2000 SR1 )

Topic

New Reply

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:

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

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,

Reply | Quote

Replies

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:

LAddress = "=Scenarios!R" & CStr(ListRow) & "C1:R"
LAddress = LAddress & CStr(ListRow + NameCount - 1) & "C1"

ActiveWorkbook.Names.Add Name:=List.Name.Name, RefersToR1C1:=LAddress

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…

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

Edited by gwhitfield on 27-Jul-01 13:19.

For anyone that this might help or might be interested, this is what I finally did. Pretty rudimentary, but, hey, it works for me.

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

Also, I see the demo bar up there which is a really 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!

Too late… Geoff

Reply | Quote

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.

Reply | Quote

[indent]

---

but I have tried putting “_”, ” _”,” _ “,_ in as the line continuation

---

[/indent]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.

Reply | Quote

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

Reply | Quote