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

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

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