How do I change the value of a Named Range in Excel using Vba

[tatin]

I have this VBA Project in Excel (Public Function) and I want to set the value of a named range and it is not happening. The function (public) just ends and returns a #value error.

This is what I have tried..

Names("mStamp").Value = "love"

 

[SkipVought]

tatin,

Trun on your Macro recorder and go thru the steps on Naming a Range.

Then modify the generated code as needed.

Post back with your code if you need help

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[xlbo]

Skip - in this instance, I think they just want to refer to the range in code.

Tatin, if you look in VBA help for Named Range and go to the "Referring To Named Ranges" section, it would give you lots of different ways including:

Range("RangeName")
and
[RangeName]

which are the 2 most common that I use

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[SkipVought]

Yes, Geoff, seems you are correct. Sorry, tatin.

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[tatin]

Thanks for the help but it is not happening

Try using the code I shared in a Public Function and see what happens.

 

[SkipVought]

Please post ALL your function code.

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[sdraper]

instead of Names("mStamp").Value = "love"
try ActiveSheet.Range("mStamp").Value = "love"
or
Sheets("Sheet1").Range("mStamp").Value = "love"
substitue your sheet name for Sheet1 above.

Sam

 

[xlbo]

??????
The code you shared ...
do you mean:

Names("mStamp").Value = "love"

If so - what are you trying to do ?? The value property of a name refers to its FORMULA. If you wish to set the VALUE of a named range on a spreadsheet, see my earlier post and look in VBA help, otherwise, please try and explain what you are attempting to do

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question