Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Faster Way To Insert Defined Names??
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Faster Way To Insert Defined Names??
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 C3:D5,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 wrote in message ... 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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Faster Way To Insert Defined Names??
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? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Faster Way To Insert Defined Names??
Thanks Rick.
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Faster Way To Insert Defined Names??
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Faster Way To Insert Defined Names??
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) wrote in message ... Thanks Rick. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Faster Way To Insert Defined Names??
On May 14, 6:21*pm, "Rick Rothstein \(MVP - VB\)"
wrote: 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 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Why use defined names? | Excel Discussion (Misc queries) | |||
defined names | Excel Worksheet Functions | |||
Defined names | Excel Worksheet Functions | |||
NAMES DEFINED | Excel Discussion (Misc queries) | |||
Can faster CPU+larger/faster RAM significantly speed up recalulati | Excel Discussion (Misc queries) |