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

 

[excells]

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?

Hi Joe,
Thanks for your assistance. The following is the code:

Sub Name_ranges_complete()


 'Starts at the currently selected cell. USER IS TO MAKE SURE TOP CELL OF THE RANGE IS SELECTED BEFORE RUNNING IT
 'Varname stores the name of the range
    x = ActiveCell.Row
    Dim column As Integer
    Dim VarName As Variant
    
    column = 2
    'The items to be named as a range are in column B
    ActiveCell.Offset(0, 1).Select
    'The name of the range is in the cell next to the first item of the range in column C


    Application.CutCopyMode = False
    VarName = Selection.Copy
    ActiveCell.Offset(0, -1).Select
    'After the name of the range has been selected and copied it goes back to the first item of the range
    'The range will be comprised of 108 items on the current column B and so they get selected
     myrow = ActiveCell.Row
     mycol = ActiveCell.column
     numrows = 107
     lastrow = myrow + numrows
     Range(Cells(myrow, mycol), Cells(lastrow, mycol)).Select
        
    
    Application.CutCopyMode = False
    'on the following line I get Run-time error '1004:  The name that you entered is not valid...
    'however the name in question is simply flashingA which does not seem to have any invalid characters
    'spaces and it hasnot been used to name any other objects
    
    ActiveWorkbook.Names.Add name:=CStr(VarName), RefersTo:=Selection
     
   'If I tried the following I would get the same Run-time error '1004
   'Selection.name = VarName
   


End Sub

To run it have content on cells B1 to B108 (the items to have as part of the range) and have the name to be used on cell C1.
The Run-time error 1004 appears on line: "ActiveWorkbook.Names.Add name:=CStr(VarName), RefersTo:=Selection"

Have a great day

Regards,
Juan

 

[JoeMo]

Without looking at your code in detail, a guess would be that this line:


VarName = Selection.Copy
is the problem. Maybe that should be:
VarName = Selection.Value</pre>

 

[excells]

Without looking at your code in detail, a guess would be that this line:


VarName = Selection.Copy
is the problem. Maybe that should be:
VarName = Selection.Value

Hi JoeMo,
Thanks for the hint. I made the change to:

VarName = Selection.vaule

but I got Run-time error '438': Object doesnt support this property or method.
When I debug the error is right on that line of code. the value of VarName when I hover on it is 0.

Any idea about what to try next?

Cheers

 

[JoeMo]

Hi JoeMo,
Thanks for the hint. I made the change to:

VarName = Selection.[COLOR=#ff0000][B]vaule[/B][/COLOR]

but I got Run-time error '438': Object doesnt support this property or method.
When I debug the error is right on that line of code. the value of VarName when I hover on it is 0.

Any idea about what to try next?

Cheers

Unless that's a typo in your post only, I would try changing "vaule" to "Value"

 

[excells]

Unless that's a typo in your post only, I would try changing "vaule" to "Value"

oops.... sorry
I fixed it and now it works. Brilliant!
Thanks so much JoeMo.

ps. I dont want to waste your time, but would you be able to tell me in few words why this was a error? I mean: is Selection.Copy the wrong expression to be used in all cases? why the run-time error was targeting: (ActiveWorkbook.Names.Add name:=CStr(VarName), RefersTo:=Selection) instead of pointing it to VarName = Selection.Copy

All the very best and thanks a lot.

Juan

 

[JoeMo]

oops.... sorry
I fixed it and now it works. Brilliant!
Thanks so much JoeMo.

ps. I dont want to waste your time, but would you be able to tell me in few words why this was a error? I mean: is Selection.Copy the wrong expression to be used in all cases? why the run-time error was targeting: (ActiveWorkbook.Names.Add name:=CStr(VarName), RefersTo:=Selection) instead of pointing it to VarName = Selection.Copy
All the very best and thanks a lot.

Juan

You are welcome. Selection.copy copies the selected range to the clipboard and is not the correct expression to use if you want to assign a range to a variable.

 

[excells]

You are welcome. Selection.copy copies the selected range to the clipboard and is not the correct expression to use if you want to assign a range to a variable.

Thanks so much JoeMo. It makes perfect sense. Have a lovely day.

Regards,
Juan