Creating a Range and Range Name

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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(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



--
Regards,
Tom Ogilvy

TroyH said:
I wish I could send my tries, but I really messed them up. So I will have
Click to expand...
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
Click to expand...
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
Click to expand...
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
Click to expand...
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.
 
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.
 
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
 
Back
Top