Creating a Range and Range Name
 

I wish I could send my tries, but I really messed them up. So I will have to explain what I am looking for.

What I am trying to do is take a row of data, create a range based on the first cell to the last cell, and name the range based on the data found in a cell. For example:

I start my search from D1. The range name will be based on the date (The value of the cell) found in D1. The range would be created from A1 to the last cell in the row. (The number of cells is not always the same.) This would be repeated there the entire sheet.

I can get the routine to create the range but I am having problems getting it to name it. My code is very long and ugly, I know someone out there can think of a quick and friendly way to do this. Any help will be very appreciated.

Creating a Range and Range Name
 

Dim cell as Range
Dim rng as Range
for each cell in Range(Cells(1,1),Cells(rows.count,1).End(xlup))
set rng = Range(Cell,Cells(cell.Row,"IV").End(xltoLeft))
' range name will be based on the date
rng.name = "AA" & Format(cell.offset(0,3).Value,"yyyymmdd")
'or
' name the range based on the data found in a cell
' rng.Name = Cell.offset(0,3).Value
Next

When you say from A1 I assume you mean for row 1. If every range is From A1

Dim cell as Range
Dim rng as Range
for each cell in Range(Cells(1,1),Cells(rows.count,1).End(xlup))
set rng = Range(Range("A1"),Cells(cell.Row,"IV").End(xltoLef t))
' range name will be based on the date
rng.name = "AA" & Format(cell.offset(0,3).Value,"yyyymmdd")
'or
' name the range based on the data found in a cell
' rng.Name = Cell.offset(0,3).Value
Next



--
Regards,
Tom Ogilvy

TroyH wrote in message
...
I wish I could send my tries, but I really messed them up. So I will have
to explain what I am looking for.

What I am trying to do is take a row of data, create a range based on the
first cell to the last cell, and name the range based on the data found in a
cell. For example:

I start my search from D1. The range name will be based on the date (The
value of the cell) found in D1. The range would be created from A1 to the
last cell in the row. (The number of cells is not always the same.) This
would be repeated there the entire sheet.

I can get the routine to create the range but I am having problems getting
it to name it. My code is very long and ugly, I know someone out there can
think of a quick and friendly way to do this. Any help will be very
appreciated.

Creating a Range and Range Name
 

Thanks for the quick reply. This is great.

I ran the code you gave me and it produced an error.
Run-Time error '1004':
That name is not valid.

It comes from the rng.Name = cell.Offset(0, 3).Value code. The value of the cell is a date.

Thanks again for your help.

Creating a Range and Range Name
 

a short format date isn't a valid name (and most date formats would not be
valid names) -
a name has to start with a character.
a name can't contain spaces.

That is why I suggested code that could handle a date. If you don't want to
append characters at the start,

You could possibly do

rng.Name = format(cell.offset(0,3).Value,"mmmddyyyy")

then you would get a name like jan272004

or
rng.Name = format(cell.offset(0,3).Value,"mmm_dd_yyyy")

jan_27_2004
--
Regards,
Tom Ogilvy



TroyH wrote in message
...
Thanks for the quick reply. This is great.

I ran the code you gave me and it produced an error.
Run-Time error '1004':
That name is not valid.

It comes from the rng.Name = cell.Offset(0, 3).Value code. The value of
the cell is a date.

Thanks again for your help.