Assign value to a name in VBA

**SpringLily** · 09-21-2010, 04:28 AM

Dear All,
I used below to assign a value to a name in VBA but, VBA said there is an error 1004 as" application defined or object defined error".

ThisWorkbook.Names("Rvalue").refertorange.Value = MyValue

Any suggestions? Where can I find some document about using Names in VBA?

Thanks.

Regards,
SpringLily

**davesexcel** · 09-21-2010, 05:00 AM

What is MyValue ?

Check this out
http://www.cpearson.com/excel/DefinedNames.aspx

**SpringLily** · 09-21-2010, 05:10 AM

myValue is defined as double and is predefined value. see below

myValue=0.35

Thanks.

**Andy Pope** · 09-21-2010, 05:13 AM

Macro recorder would have helped point you in the right direction

    Thisworkbook.Names("RValue").RefersToR1C1 = "=" & MyValue

**SpringLily** · 09-21-2010, 05:22 AM

Sorry, Andy. Myvalue is not a predefined name. It is a variable defined in the VBA sub(). I tried the formula. it seems do not work. Thanks anyway. Any other suggestions?

**davesexcel** · 09-21-2010, 05:23 AM

Try this,

    MyValue = 0.35

    ActiveWorkbook.Names.Add Name:="Rvalue", RefersToR1C1:=MyValue

**SpringLily** · 09-21-2010, 05:25 AM

hi, davesexcel
The name Rvalue has been defined already. Any other suggestions?

**snb** · 09-21-2010, 05:30 AM

Consider this code as an example:

Sub snb()
  newvalue = 3.5
  Range("A1:C10").Name = "newname_snb"
  Range("newname_snb").Value = newvalue
End Sub

**Andy Pope** · 09-21-2010, 05:34 AM

Not sure what the issue is when you are trying it but for me this code sets the existing named range Rvalue to the value contained in the vba variable MyValue.

Sub Macro1()
'
' Macro1 Macro
'
    Dim MyValue As Double
    
    MyValue = 0.35
    ThisWorkbook.Names("RValue").RefersToR1C1 = "=" & MyValue
    
End Sub

**SpringLily** · 09-21-2010, 05:54 AM

Hi,there
I tried three methods as below:

A:  Range("Rvalue") = myRvalue
B:  ThisWorkbook.Names("Rvalue").Value = myRvalue
C:  ThisWorkbook.Names("Rvalue").RefersToR1C1 = "=" & myRvalue

It seems only A works and it changes the value in the cell where Rvalue referes to. The other two change the defination of the "Rvalue" to 0.35. Any suggestions to improve the code?
Thank.

**SpringLily** · 09-22-2010, 04:52 AM

Hi, there
I solved the problem by creating a array which contains the third table in VBA. Thanks everyone for your contribution. Regards, SpringLily

Assign value to a name in VBA

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Assign value to a name in VBA

Re: Assign value to a name in VBA

Re: Assign value to a name in VBA

Re: Assign value to a name in VBA

Re: Assign value to a name in VBA

Re: Assign value to a name in VBA

Re: Assign value to a name in VBA

Re: Assign value to a name in VBA

Re: Assign value to a name in VBA

Re: Assign value to a name in VBA

Re: Assign value to a name in VBA

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions