Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help with automating adding Names ranges
Sub AAAA() For Each cell In Worksheets("Categories").Range("B1:L1") ThisWorkbook.Names.Add Name:=cell.Value, _ RefersTo:=cell.Offset(27, 0).Resize(29, 1) Next End Sub -- Regards, Tom Ogilvy "rpw" wrote in message ... Hi, I've managed to get to the point where the name is added to the Names list, but when I run the code all of the added names have the same range reference. If I step through the code, I see that a name is added (e.g. New_List). I check the Names/Define in Excel and the range is correct (e.g. B28:B56). I step through the next name and it is added correctly, but now both have the same range (e.g.C28:C56). If I run the code a second time then the row numbers are offset (e.g. C29:C57). Here is the portion of the code that is giving me problems (I think): 'Re-validate the list names and ranges Worksheets("Categories").Activate Worksheets("Categories").Range("B1").Select For SubIntCtr = 1 To 10 On Error Resume Next Selection.Offset(0, 1).Range("A1").Select strName = Selection Names.Add Name:=strName, RefersTo:="=Categories!a28:a52" Next SubIntCtr It's taken me hours to get it this far along and I'm stuck now so I could use some help. Any help is much appreciated. tia :-) -- rpw |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help with automating adding Names ranges
Hi Tom,
Thanks for the quick response. I ended up using Offset and Resize in my original formula after seeing how you used it in your code. Here's my modified code (that works!): 'Re-validate the list names and ranges Worksheets("Categories").Activate Worksheets("Categories").Range("B1").Select For MainIntCtr = 1 To 10 On Error Resume Next Selection.Offset(0, 1).Range("A1").Select strName = Selection Names(strName).Delete Names.Add Name:=strName, RefersTo:=Selection.Offset(27, 0).Resize(29, 1) Next MainIntCtr Thanks again for your quick help. Sorry that my thanks are coming late, but my first attempt didn't post for some reason. Rick... "Tom Ogilvy" wrote: Sub AAAA() For Each cell In Worksheets("Categories").Range("B1:L1") ThisWorkbook.Names.Add Name:=cell.Value, _ RefersTo:=cell.Offset(27, 0).Resize(29, 1) Next End Sub -- Regards, Tom Ogilvy "rpw" wrote in message ... Hi, I've managed to get to the point where the name is added to the Names list, but when I run the code all of the added names have the same range reference. If I step through the code, I see that a name is added (e.g. New_List). I check the Names/Define in Excel and the range is correct (e.g. B28:B56). I step through the next name and it is added correctly, but now both have the same range (e.g.C28:C56). If I run the code a second time then the row numbers are offset (e.g. C29:C57). Here is the portion of the code that is giving me problems (I think): 'Re-validate the list names and ranges Worksheets("Categories").Activate Worksheets("Categories").Range("B1").Select For SubIntCtr = 1 To 10 On Error Resume Next Selection.Offset(0, 1).Range("A1").Select strName = Selection Names.Add Name:=strName, RefersTo:="=Categories!a28:a52" Next SubIntCtr It's taken me hours to get it this far along and I'm stuck now so I could use some help. Any help is much appreciated. tia :-) -- rpw |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help with automating adding Names ranges
Glad it helped.
-- Regards, Tom Ogilvy "rpw" wrote in message ... Hi Tom, Thanks for the quick response. I ended up using Offset and Resize in my original formula after seeing how you used it in your code. Here's my modified code (that works!): 'Re-validate the list names and ranges Worksheets("Categories").Activate Worksheets("Categories").Range("B1").Select For MainIntCtr = 1 To 10 On Error Resume Next Selection.Offset(0, 1).Range("A1").Select strName = Selection Names(strName).Delete Names.Add Name:=strName, RefersTo:=Selection.Offset(27, 0).Resize(29, 1) Next MainIntCtr Thanks again for your quick help. Sorry that my thanks are coming late, but my first attempt didn't post for some reason. Rick... "Tom Ogilvy" wrote: Sub AAAA() For Each cell In Worksheets("Categories").Range("B1:L1") ThisWorkbook.Names.Add Name:=cell.Value, _ RefersTo:=cell.Offset(27, 0).Resize(29, 1) Next End Sub -- Regards, Tom Ogilvy "rpw" wrote in message ... Hi, I've managed to get to the point where the name is added to the Names list, but when I run the code all of the added names have the same range reference. If I step through the code, I see that a name is added (e.g. New_List). I check the Names/Define in Excel and the range is correct (e.g. B28:B56). I step through the next name and it is added correctly, but now both have the same range (e.g.C28:C56). If I run the code a second time then the row numbers are offset (e.g. C29:C57). Here is the portion of the code that is giving me problems (I think): 'Re-validate the list names and ranges Worksheets("Categories").Activate Worksheets("Categories").Range("B1").Select For SubIntCtr = 1 To 10 On Error Resume Next Selection.Offset(0, 1).Range("A1").Select strName = Selection Names.Add Name:=strName, RefersTo:="=Categories!a28:a52" Next SubIntCtr It's taken me hours to get it this far along and I'm stuck now so I could use some help. Any help is much appreciated. tia :-) -- rpw |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Automating TAB names in calculations | Excel Discussion (Misc queries) | |||
Automating Worksheet Names | Excel Worksheet Functions | |||
Automating Tab names | Excel Worksheet Functions | |||
Automating defining names | Excel Programming | |||
XL 2000: automating drag-and-drop for worksheet ranges. Using Code! | Excel Programming |