Define a range with a variable name

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 Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

KenMillard

Board Regular
Joined
Apr 25, 2007
Messages
151
Are you trying to put an apostrophe before the team name?

As far as I know, the only special character allowed is the underscore character. Eliminate the apostrophe and any special characters and that should fix any problems with the name.

Also, you could probably use
Code:
RefersTo:= EmpNames.Address
to set the address of the named range.
 

Forum statistics

Threads
1,181,658
Messages
5,931,271
Members
436,785
Latest member
KingGideon

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