Creating a Defined range name where the name is a variable

Opopanax

New Member
Joined
Jul 16, 2007
Messages
9
Hey all. I'm running a script where I want to create a defined name that varies based on my counter. I thought below will work, but when I get to the "ActiveWorkbook.Names.add Name: = " I get errors when attempting to use the variable TeamName. I thought the "" & TeamName, would work but doesnt.

The code:

While Counter < NumEmpTeam + 1

'Count number of employees on active EmpTeam Counter
NumEmp = Application.WorksheetFunction.CountA(Sheets("Employee Teams").Range(Cells(2, vCol), Cells(100, vCol))) + 1

Set EmpNames = Range(Cells(2, vCol), Cells(NumEmp, vCol))
TeamName = Cells(1, vCol).Value

ActiveWorkbook.Names.Add Name:="" & TeamName , RefersToR1C1:="='Employee Teams'!R2C" & vCol & ":R" & NumEmp & "C" & vCol

Counter = Counter + 1
vCol = vCo`l + 1

Wend

I've also tried substituting the activeworkbooknames for this:

EmpNames.Name = "" & TeamName

or some combination but it doesn't work.

Anyone have any ideas?

Thanks in advance
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
And the errors are?

Are you sure the TeamName is valid for a named range?

PS There's no need for the "" & as far as I can see.
 
Upvote 0
And the errors are?

Are you sure the TeamName is valid for a named range?

PS There's no need for the "" & as far as I can see.


TeamName is valid for a named range. If I do this:

Activeworkbook.names.add name:="TeamName", RefersTo...

It defines the RefersTo Range as "TeamName"

What I need it to do however, is define the RefersTo Range based on the value of Cells(1,vCol). So the Variable TeamName is Dim TeamName as String

and TeamName = Cells(1,vCol).Value

I would think the following:

Activeworkbook.names.add name:= TeamName, RefersTo...

would work, but I always get the following error.

"Run-Time error '1004': That name is not valid."

The variable TeamName has a string value of "Test Team" which is correct. So what I want it to do is have the result
Activeworkbook.names.add name:= "Test Team", RefersTo

and then at my next counter put the next TeamName.
 
Upvote 0
Well there you are, the error message is spot on.:)

Team Name, with a space, isn't a valid name for a range.:)
 
Upvote 0
Well there you are, the error message is spot on.:)

Team Name, with a space, isn't a valid name for a range.:)

Oops! Thank you. I feel quite stupid now. So the code was correct - just not the format of my source data.

Thanks!
 
Upvote 0

Forum statistics

Threads
1,218,560
Messages
6,143,200
Members
450,469
Latest member
brent3162

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top