Archive of Mr Excel Message Board


Back to Excel VBA archive index
Back to archive home



Change value of Named Range in VB

Posted by Melanie Swarner on October 26, 2001 12:37 PM
How do I change the value of a named range in VB without selecting the cell? Because of future plans of the project, I need to use a named range and what it refers to could change each time program is run.

I have a list box with multiple selections. As I iterate through it, it changes the value of a cell if an item is selected. However, if I select the cell first, it erases all of my subsequent selections and I only get the results of the first selection.

It seems pretty simple. There's got to be a way to do it, I just don't know it.

Thanks!


Check out our Excel VBA Resources

Re: Change value of Named Range in VB

Posted by Barrie Davidson on October 26, 2001 12:42 PM

Use something like this

Range("test").Value = 5

where your named range is named "test" and you want that range's value to be 5 (in each cell of the range).

Does this help you?

BarrieBarrie Davidson


RefersToRange vs Range's value property....

Posted by Melanie Swarner on October 27, 2001 1:08 PM
Barry,

This worked fine. This was my original idea, but it didn't seem to work. I was try to use RefersToRange property of "Names" because I'd had problems with a named range before. When do I have to use RefersToRange to for a name and when don't I?

Thanks for your help! I'm having a lot of fun learning this language, but it's hard when you run into a stumbling block. This board has helped me a lot.

Melanie


Re: RefersToRange vs Range's value property....

Posted by Barrie Davidson on October 29, 2001 8:09 AM
Melanie, from VBA's Help:

"Returns the Range object referred to by a Name object. Read-only."

This could be useful, for example, if you wanted to get a named range's address in VBA. Something like:

Sub GetAddress()
Dim RangeAddress As String
RangeAddress = Names("Melanie").RefersTo
End Sub

Regards,
Barrie

Barrie Davidson




This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.