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

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,357
Office Version
  1. 365
Platform
  1. Windows
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.
 

Opopanax

New Member
Joined
Jul 16, 2007
Messages
9
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.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,357
Office Version
  1. 365
Platform
  1. Windows
Well there you are, the error message is spot on.:)

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

Opopanax

New Member
Joined
Jul 16, 2007
Messages
9
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!
 

Forum statistics

Threads
1,181,404
Messages
5,929,750
Members
436,687
Latest member
Glass of Gin

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
Top