Variable Name Range Reference (VBA)

[excells]

Hi,
I'm writing code to name ranges and the actual name varies. Im using the following expression but its not working. Below "name" is a variable. Can somebody help me with this?

ActiveWorkbook.Names.Add name:="cstr(name)", RefersTo:=Selection




Thanks for your help in advance.

Juan

 

[JoeMo]

What exactly is "not working"? What error message do you get? Since "name" is a VBA object and a property you might want to name your variable something other than "name".

 

[Jonmo1]

Also,

cstr(yourvariable) should NOT be in quotation marks.

 

[excells]

Hi JonMo1,
thanks for prompt response.
I get Run-time error '1004':
The name you entered is not valid


The full code is:

Sub Name_ranges()
x = ActiveCell.Row
Dim column As Integer
Dim name As String

column = 2


ActiveCell.Offset(0, 1).Select
Application.CutCopyMode = False
name = Selection.Copy
ActiveCell.Offset(0, -1).Select
Application.Run "'140206_JUAN_MACRO_MODULES.xlsm'!Select_Group_cells"
Application.CutCopyMode = False
'ActiveWorkbook.Names.Add name:="name", RefersTo:=Selection
' the above works but "name" is taken as a constant and the range is called
'name, as opposed to being called the value of "name",
ActiveWorkbook.Names.Add name:="cstr(name)", RefersTo:=Selection
'The above give me Run-time error'1004'

'End Sub


End Sub

 

[excells]

Hi Jonmo1,
I took the quotation marks and still get the Run-time error '1004.

Thanks for helping.

Juan

 

[JoeMo]

Jonmo1 picked up the likely problem - see his post, but I still recommend you name the variable something other than "name".

 

[excells]

Hi Joemo,
I used the following
ActiveWorkbook.Names.Add name:=CStr(VarName), RefersTo:=Selection

still get the same Run Time error 1004.

any ideas?
cheers

Juan

 

[JoeMo]

Hi Joemo,
I used the following
ActiveWorkbook.Names.Add name:=CStr(VarName), RefersTo:=Selection

still get the same Run Time error 1004.

any ideas?
cheers

Juan

What is the value of varName when you get the error?

 

[excells]

Hi JoeMo;
I hovered over the variable and found that the value was TRUE. This is a bit strange to me because the variable should be equal to another string picked by the program on a previous step.

What do you think?

Regards,
Juan

 

[JoeMo]

Hi JoeMo;
I hovered over the variable and found that the value was TRUE. This is a bit strange to me because the variable should be equal to another string picked by the program on a previous step.

What do you think?

Regards,
Juan

I don't find anything in your earlier posts showing where you set the value of that variable. Can you post the entire code and indicate which line causes the error?