Problem creating a named range

[headly]

Code looks like this:

ActiveWorkbook.Names.Add Name:=varName, RefersTo:="Sheet!" & varStartCell


Creates a name, which I cannot use, because it is surrounded by quotation "
" symbols.
Note about Varname: is created after stripping out spaces from user entered
input; varStartCell is an absolute reference like $A$5

TY

 

[Dave Peterson]

Do you have a worksheet named: Sheet

And you dropped an equal sign in the refersto:= portion

RefersTo:="=Sheet!" & varStartCell

 

[Ron Rosenfeld]

Code looks like this:

ActiveWorkbook.Names.Add Name:=varName, RefersTo:="Sheet!" & varStartCell


Creates a name, which I cannot use, because it is surrounded by quotation "
" symbols.
Note about Varname: is created after stripping out spaces from user entered
input; varStartCell is an absolute reference like $A$5

TY

You're very close to the correct format. Note the equal sign AFTER the initial
quote.

ActiveWorkbook.Names.Add Name:=varName, RefersTo:="=Sheet!" & varStartCell
--ron

 

[Leith Ross]

Do you have a worksheet named: Sheet

And you dropped an equal sign in the refersto:= portion

RefersTo:="=Sheet!" & varStartCell

Hello headly,

If you need the active sheet's name added to the reference, you can
use this code..

ActiveWorkbook.Names.Add Name:=varName, RefersTo:="='" &
ActiveSheet.Name & "'!" & varStartCell

Sincerely,
Leith Ross

 

[Gary''s Student]

You are missing an equals sign:

Sub Macro1()
Dim varName As String
Dim varStartCell As String
varName = "alpha"
varStartCell = "$A$5"
ActiveWorkbook.Names.Add Name:=varName, RefersTo:="=Sheet!" & varStartCell
End Sub

 

[headly]

Actually, no, I get a name like ="sheet1!$A$5" but no idea where the "" came
from

 

[OssieMac]

Put a stop in your code just before naming the range like the following then
while it is stopped and before cancelling the process, rest your cursor over
the variables varName and then varStartCell and see what value VBA has
assigned to them.

varName = "MyNamedRange" 'Just as demo. No doubt your code is different
varStartCell = "$A$5"

Stop

ActiveWorkbook.Names.Add Name:=varName, RefersTo:="Sheet!" & varStartCell


If you can't fix then post a bit more of your code showing how varName and
varStartCell are populated and manipulated. ie stipped of blanks etc.

 

[Ron Rosenfeld]

Actually, no, I get a name like ="sheet1!$A$5" but no idea where the "" came
from

If you put in the "=" sign where we have all been telling you to, those "" will
go away.
--ron