Faster Way To Insert Defined Names??

[cqdigital]

I'm doing a project where I need to insert hundreds of defined names
into Excel. I have the names and the info that goes into the "refers
to" box listed in a table. At the moment I'm laboriously copying and
pasting them into the "Define Name" dialog box one at a time.

Is there a faster way to do this? Is there some functionality in
Excel (or a third party tool) that allows many defined names to be
dumped into Excel at once? This would save me lots of hours.

 

[Rick Rothstein \(MVP - VB\)]

Since you already have everything in cells, you can use a macro. Your name's
ranges in the cell should looks like these examples...

B7
D1:F1
C35,F1:G4
etc.

Change the worksheet name and the assignments to the Const statements as
appropriate for your data...

Sub AssignNames()
Dim X As Long
Const StartRowForTable As Long = 1
Const EndRowForTable As Long = 3
Const ColumnForNames As Long = 1
Const ColumnForRanges As Long = 2
With ThisWorkbook.Worksheets("Sheet5")
For X = StartRowForTable To EndRowForTable
ThisWorkbook.Names.Add .Cells(X, ColumnForNames).Value, _
.Range(.Cells(X, ColumnForRanges).Value)
Next
End With
End Sub

Rick

 

[cqdigital]

Thanks for the reply Rick. I'm using the defined names to provide
scrolling chart functionality. My names and ranges are a bit more
complex than simple cell ranges and look like the following:

SPARE2_RWCFR_ScrollVal ScrollVal!$C$26

SPARE2_RWCFR_ZoomVal ScrollVal!$D$26

SPARE2_RWCFR_ChartX OFFSET('Data Input'!$C
$298,0,SPARE2_RWCFR_ScrollVal-1,1,SPARE2_RWCFR_ZoomVal)

SPARE2_RWCFR_12MA OFFSET(SPARE2_RWCFR_ChartX,33,0,,)

etc

I'm assuming that the code you have posted should still work despite
this. I've changed the code to:

Sub AssignNames()
Dim X As Long
Const StartRowForTable As Long = 1
Const EndRowForTable As Long = 3
Const ColumnForNames As Long = 1
Const ColumnForRanges As Long = 2
With ThisWorkbook.Worksheets("Sheet3")
For X = StartRowForTable To EndRowForTable
ThisWorkbook.Names.Add .Cells(A,
ColumnForNames).Value, .Range(.Cells(B, ColumnForRanges).Value)
Next
End With
End Sub

inserting my Worksheet name and "A" and "B" to define the columns on
the sheet that contain the names and references. The code is failing
with a runtime error 1004 on the last line. Have I done the right
thing by placing the names of the column headers (A and B) in the
places where you had the X or should I be doing something different?

 

[cqdigital]

Thanks Rick.

 

[Rick Rothstein \(MVP - VB\)]

Thanks Rick.

Since you have posted this "thank you" message an hour-and-a-half after you
posted a message saying the code was failing... can I assume you worked
everything out and your question has been resolved?

Rick

 

[Bob Phillips]

Jan Karel Pieterse has a utility designed to improve working with defined
names.
It is a free add-in, and you can get it at
http://www.jkp-ads.com/OfficeMarketPlaceNM-EN.asp

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[cqdigital]

Since you have posted this "thank you" message an hour-and-a-half after you
posted a message saying the code was failing... can I assume you worked
everything out and your question has been resolved?

Rick

Yes, as you know I was not following your initial instructions
correctly. Hence I removed the long winded post that resulted and
went back to the drawing board. I'm still finding however that
the code fails on the last line with a runtime error 1004.