Convert a range into range name in each worksheet? :: MrExcel Message Board


 FAQFAQ
   SearchSearch   MemberlistMemberlist   UsergroupsUsergroups   FavoritesFavorites   StatisticsStatistics 
 RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 
Online StoreOnline Store

MrExcel Message Board Forum Index -> Excel Questions

Convert a range into range name in each worksheet?
Post new topic   Reply to topic
Last Thread | Next Thread  >   Printable version
  Author    Thread

Jake_Wrestler
Board Regular


Joined: 03 Sep 2003
Posts: 95
Location: Los Angeles
Flag: Usa

Status: Offline

 Reply with quote  

Convert a range into range name in each worksheet?

I often find myself often going into Insert -> Name -> Define and tediously changing my range names and parameters there. It is awfully time consuming.

Really I am just looking for a method to boost my effieciency.

In a perfect world I imagine a macro where I have two columns, one for the name of the range (i.e., ThompsonJune2001, ThompsonJune2002, MilfordJune2001, MilfordJune2002, etc. You can see how this could be time consuming if I have 4 years of monthly data from 5 or more stores [4 X 12 X 5 = 240 range names!!!]) and the other where I can specify the actual corresponding range (i.e., '6-2001'!$A$1:$AA$48, '6-2002'!$A$1:$AA$48, etc.).

From these two columns of input, the macro would create these named ranges on the fly. Is this macro possible??

If you think of an improvement if you can see what I am doing or any other suggestions for other ways that will help to boost my efficiency in creating name ranges like this.

thanks icon_pray.gif

Post Fri Sep 19, 2003 6:12 pm 
 View user's profile Send private message Yahoo Messenger

earlyd
Board Master


Joined: 11 Dec 2002
Posts: 955

Flag: Usa

Status: Offline

 Reply with quote  

Re: Convert a range into range name in each worksheet?

Have you tried using the Name Box rather than the menus - at least when initially creating them? Sorry if this is obvious - just wondering.

Post Fri Sep 19, 2003 6:14 pm 
 View user's profile Send private message

Jake_Wrestler
Board Regular


Joined: 03 Sep 2003
Posts: 95
Location: Los Angeles
Flag: Usa

Status: Offline

 Reply with quote  

Re: Convert a range into range name in each worksheet?

The name box is another way I try (in order to mix it up a bit) but doing that 240 plus times is still murder on the carpal tunnel... :(

Post Fri Sep 19, 2003 6:45 pm 
 View user's profile Send private message Yahoo Messenger

Richie(UK)
MrExcel MVP


Joined: 18 May 2002
Posts: 2464
Location: Worcester, England
Flag: Uk

Status: Offline

 Reply with quote  

Re: Convert a range into range name in each worksheet?

Hi Jake,

This has only been tested to a limited extent but it should serve as a starting point for doing what you wish.

code:
Sub NamesTest()
    Dim rngNames As Range, rngCell As Range
   
    With Sheet1
        Set rngNames = .Range("A1:A" & .Cells(Rows.Count, "A").End(xlUp).Row)
        'the range containing the names
        For Each rngCell In rngNames
            With rngCell
                If NameExists(.Value) Then ThisWorkbook.Names(.Value).Delete
                'if a name already exists then delete it
                ThisWorkbook.Names.Add Name:=.Value, RefersTo:="=" & .Offset(0, 1).Value
            End With
        Next rngCell
    End With
   
End Sub

Function NameExists(strName As String) As Boolean
    Dim nmTest As Name
    On Error Resume Next
    Set nmTest = ThisWorkbook.Names(strName)
    On Error GoTo 0
    If Not nmTest Is Nothing Then NameExists = True
End Function


HTH
_________________
««« ¤ Richie ¤ »»»

Post Fri Sep 19, 2003 8:40 pm 
 View user's profile Send private message

Jake_Wrestler
Board Regular


Joined: 03 Sep 2003
Posts: 95
Location: Los Angeles
Flag: Usa

Status: Offline

 Reply with quote  

Re: Convert a range into range name in each worksheet?

THanks Ritchie, unfortunately I get the following error:

Compile error. Variable not defined.

It is referring to "Sheet1" on the third line...

I am assuming to use the macro I select the range with the names in it then run it, is this correct?

Post Fri Sep 19, 2003 11:19 pm 
 View user's profile Send private message Yahoo Messenger

Richie(UK)
MrExcel MVP


Joined: 18 May 2002
Posts: 2464
Location: Worcester, England
Flag: Uk

Status: Offline

 Reply with quote  

Re: Convert a range into range name in each worksheet?

Hi Jake,

The "Sheet1" part is the sheet CodeName for the first sheet in a new workbook (which is what I used for testing). It looks as if you have deleted this particular sheet at some stage but that doesn't matter, it was just an example - change it to the CodeName of the sheet containing the names and 'refers to' details in your workbook or replace it with Worksheets("WhateverName"), where "WhateverName" is the name of your chosen sheet .

No need to select the range, the code will pick up the names from column A of the specified sheet.
_________________
««« ¤ Richie ¤ »»»

Post Sat Sep 20, 2003 2:37 pm 
 View user's profile Send private message

Jake_Wrestler
Board Regular


Joined: 03 Sep 2003
Posts: 95
Location: Los Angeles
Flag: Usa

Status: Offline

 Reply with quote  

Re: Convert a range into range name in each worksheet?

Hi Richie,

After I created some named ranges in sheet1 and place their names in column A, I get the following error when I run it:

application-defined or object defined error

It is referring to this line:
ThisWorkbook.Names.Add Name:=.Value, RefersTo:="=" & .Offset(0, 1).Value

Maybe I am going about it wrong. I creatd the following named ranges

one =Sheet1!$D$3:$E$9

two =Sheet1!$C$7:$D$15

three =Sheet1!$C$9:$F$9

four =Sheet1!$D$5:$D$24

in sheet1 and put the names (one, two, three, four) in column A then run the macros, is this right? I am assuming it should take those named ranges and populate all the sheets with them, right?

Post Mon Sep 22, 2003 3:51 pm 
 View user's profile Send private message Yahoo Messenger

Richie(UK)
MrExcel MVP


Joined: 18 May 2002
Posts: 2464
Location: Worcester, England
Flag: Uk

Status: Offline

 Reply with quote  

Re: Convert a range into range name in each worksheet?

Hi Jake,

Come on, look at these things as a challenge and try to figure out why they don't work icon_wink.gif

Specifically, if you look at the line that the error arises on:

code:
ThisWorkbook.Names.Add Name:=.Value, RefersTo:="=" & .Offset(0, 1).Value

what does it do?

It adds a name to the names collection of ThisWorkbook.
The name is taken from the value of the cell in column A.
The refers to is equal to "=" plus the value in the cell next to the one in column A.

So, if you are adding references with "=" at the beginning in column B then the code is effectively 'seeing double', try ditching one of the "=" from the start, like this:

Microsoft Excel - deleteme.xls___Running: xl97 : OS = Windows 98
(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
=

A
B
C
D
1
one Sheet1!$D$3:$E$9   
2
two Sheet1!$C$7:$D$15   
3
three Sheet1!$C$9:$F$9   
4
four Sheet1!$D$5:$D$24   
Sheet1 

[HtmlMaker 2.32] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.


or like this:
code:
ThisWorkbook.Names.Add Name:=.Value, RefersTo:=.Offset(0, 1).Value

HTH
_________________
««« ¤ Richie ¤ »»»

Post Mon Sep 22, 2003 7:26 pm 
 View user's profile Send private message

Jake_Wrestler
Board Regular


Joined: 03 Sep 2003
Posts: 95
Location: Los Angeles
Flag: Usa

Status: Offline

 Reply with quote  

Re: Convert a range into range name in each worksheet?

Cool! Thanks for the lucid explanation Richie icon_biggrin.gif

Post Mon Sep 22, 2003 8:02 pm 
 View user's profile Send private message Yahoo Messenger

Jake_Wrestler
Board Regular


Joined: 03 Sep 2003
Posts: 95
Location: Los Angeles
Flag: Usa

Status: Offline

 Reply with quote  

Re: Convert a range into range name in each worksheet?

Richie,

I am still getting the same error and I have been fiddling with it for a few hours. icon_banghead.gif Any ideas? I set up the data like you suggested:

Microsoft Excel - dasak.xls___Running: xl2002 XP : OS = Windows XP
(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
=

A
B
C
D
1
PL0102PL0102!$A$1:$G$305  
2
PL0202PL0202!$A$1:$G$305  
3
PL0302PL0302!$A$1:$G$305  
4
PL0402PL0402!$A$1:$G$305  
5
PL0502PL0502!$A$1:$G$305  
6
PL0602PL0602!$A$1:$G$305  
7
PL0702PL0702!$A$1:$G$305  
8
PL0802PL0802!$A$1:$G$305  
9
PL0902PL0902!$A$1:$G$305  
10
PL1002PL1002!$A$1:$G$305  
11
PL1102PL1102!$A$1:$G$305  
Sheet1 

[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.



Thanks Richie

Post Thu Sep 25, 2003 12:14 am 
 View user's profile Send private message Yahoo Messenger

Tana Lee
Board Master


Joined: 26 Jul 2003
Posts: 282
Location: Hawaii
Flag: Usa

Status: Offline

 Reply with quote  

Re: Convert a range into range name in each worksheet?

Aloha...

As usual I don't have access to Excel at the moment, but I'm thinking of something in Names where you can define the range by column, row. or column and row...

Anybody know where it is right off? I'm thinking Insert --> Names --> Create. A box should pop up asking what you want to use for the name.

Would this be of any help to you?

Tana-Lee

Post Thu Sep 25, 2003 1:23 am 
 View user's profile Send private message

Jake_Wrestler
Board Regular


Joined: 03 Sep 2003
Posts: 95
Location: Los Angeles
Flag: Usa

Status: Offline

 Reply with quote  

Re: Convert a range into range name in each worksheet?

Tana,
Yes, insert --> name --> define unfortunately it isn't all that fast if I have, say, 140 named ranges to define. This macro that Richie has helped me with would simply take the name from one column and the actual range from another column and create the named range. Unfortunately, I keep getting an error everytime I run it that reads:

Run time error '1004':
Application defined or object defined error

When I debug, the debugger points to this line in the macro:

ThisWorkbook.Names.Add Name:=.Value, RefersTo:="=" & .Offset(0, 1).Value

I can't seem to figure it out icon_frown.gif

Post Thu Sep 25, 2003 3:31 pm 
 View user's profile Send private message Yahoo Messenger

Jake_Wrestler
Board Regular


Joined: 03 Sep 2003
Posts: 95
Location: Los Angeles
Flag: Usa

Status: Offline

 Reply with quote  

Re: Convert a range into range name in each worksheet?

Anyone icon_pray.gif have any ideas how I can take two columns (one with names and the next with the actual ranges) and create named ranges with a macro? Otherwise I will be doing the long process of typing it 76 times... icon_frown.gif thank you for any insights! (my carpal tunnel thanks you too)

Post Thu Sep 25, 2003 6:42 pm 
 View user's profile Send private message Yahoo Messenger

Tana Lee
Board Master


Joined: 26 Jul 2003
Posts: 282
Location: Hawaii
Flag: Usa

Status: Offline

 Reply with quote  

Re: Convert a range into range name in each worksheet?

Aloha Part II,

I still don't have Excel nearby, but I wasn't referring to Name Define.

Try Insert-->Name-->CREATE.

I think this will help you out. Really I do. icon_biggrin.gif

Tana-Lee

Post Fri Sep 26, 2003 2:20 am 
 View user's profile Send private message

dolphin
Board Master


Joined: 19 Nov 2002
Posts: 135

Flag: Blank

Status: Offline

 Reply with quote  

Re: Convert a range into range name in each worksheet?

Aaron Blood’s site has what you are talking about

Check out
Name_mgr_zip

http://www.xl-logic.com/pages/vba.html

Post Fri Sep 26, 2003 2:35 am 
 View user's profile Send private message

Jake_Wrestler
Board Regular


Joined: 03 Sep 2003
Posts: 95
Location: Los Angeles
Flag: Usa

Status: Offline

 Reply with quote  

Re: Convert a range into range name in each worksheet?

Aloha Tana, you are dead on. Thanks a zillion!!!

Post Fri Sep 26, 2003 3:26 pm 
 View user's profile Send private message Yahoo Messenger
  Display posts from previous:      

MrExcel Message Board Forum Index -> Excel Questions


Forum Jump:
Jump to:  

Post new topic   Reply to topic
Page 1 of 1



Add To Favorites

 


Forum Rules:
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum

Powered by phpBB: 2.0.4 © 2001 phpBB Group

Need help posting your first question? Read how to post

Need extra help ? Couldn't get the answer you needed ? Get a free quote from our Consulting Team

Download Colo's HTML Maker utility for displaying your Excel Worksheet on the board.

Download VB HTML Maker to post your code on the board


Check out our new index to 485 Excel Articles.


Return to MrExcel Consulting

All contents Copyright 1998-2004 by MrExcel.com
If you believe information posted here is from your copyrighted source, notify us per the Terms of Use
Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.