using cell data to name a range
 

I have the following problem:
I have writen a macro that goes to the last row that contains data then
count up until it finds data in the first column (A). From this I have
the first row that contins data in the first column and the last row
that has data. I can now say that I have the C1Rx:C16Rx (the last
column with data always P).

What I need to do is take the info that is in C1Rx (its text) and name
the range.
From the macro recorder I get this

ActiveWorkbook.Names.Add Name:="test",
RefersToR1C1:="=Sheet4!R2C1:R8C4"

But for the life of me I cant figure out how to use this to ad the name
and refer to the range correctly...

Something like this

ActiveWorkbook.Names.Add Name:= Range("A" & StartDataRow),
RefersToR1C1:=" =C1R(MyStartDataRow):R(LastDataRow)C16

I would greatly appreciate any help....

Thanks

using cell data to name a range
 

Maybe like this (note the reference to the sheet name):

Dim rngName As String
rngName = Cells(StartDataRow, 1).Text
ActiveWorkbook.Names.Add Name:=rngName, _
RefersToR1C1:="=Sheet4!R" & StartDataRow & "C1:R" _
& LastDataRow & "C16"

Hope this helps
Rowan

Chris Salcedo wrote:
I have the following problem:
I have writen a macro that goes to the last row that contains data then
count up until it finds data in the first column (A). From this I have
the first row that contins data in the first column and the last row
that has data. I can now say that I have the C1Rx:C16Rx (the last
column with data always P).

What I need to do is take the info that is in C1Rx (its text) and name
the range.
From the macro recorder I get this

ActiveWorkbook.Names.Add Name:="test",
RefersToR1C1:="=Sheet4!R2C1:R8C4"

But for the life of me I cant figure out how to use this to ad the name
and refer to the range correctly...

Something like this

ActiveWorkbook.Names.Add Name:= Range("A" & StartDataRow),
RefersToR1C1:=" =C1R(MyStartDataRow):R(LastDataRow)C16

I would greatly appreciate any help....

Thanks

using cell data to name a range
 

This Works GREAT !!!!!!!

I have a question, does the sheet name have to be explicit?? Can it be
the active sheet ???

using cell data to name a range
 

Try:

rngName = Cells(StartDataRow, 1).Text
ActiveWorkbook.Names.Add Name:=rngName, _
RefersToR1C1:="=" & ActiveSheet.Name & "!R" & _
StartDataRow & "C1:R" & LastDataRow & "C16"

Regards
Rowan

Chris Salcedo wrote:
This Works GREAT !!!!!!!

I have a question, does the sheet name have to be explicit?? Can it be
the active sheet ???

using cell data to name a range
 

And if you are only going to be refering to the activesheet then more
simply:

Range(Cells(StartDataRow, 1), Cells(LastDataRow, 16)).Name _
= Cells(StartDataRow, 1).Value

Regards
Rowan

Chris Salcedo wrote:
This Works GREAT !!!!!!!

I have a question, does the sheet name have to be explicit?? Can it be
the active sheet ???

using cell data to name a range
 

Hi chris,

It looks like you want the range named after the top left cell.
see if there is anything here you can use.

http://vbaexpress.com/forum/showthread.php?t=5569

Dave

Chris Salcedo Wrote:
I have the following problem:
I have writen a macro that goes to the last row that contains data
then
count up until it finds data in the first column (A). From this I have
the first row that contins data in the first column and the last row
that has data. I can now say that I have the C1Rx:C16Rx (the last
column with data always P).

What I need to do is take the info that is in C1Rx (its text) and name
the range.
From the macro recorder I get this

ActiveWorkbook.Names.Add Name:="test",
RefersToR1C1:="=Sheet4!R2C1:R8C4"

But for the life of me I cant figure out how to use this to ad the
name
and refer to the range correctly...

Something like this

ActiveWorkbook.Names.Add Name:= Range("A" & StartDataRow),
RefersToR1C1:=" =C1R(MyStartDataRow):R(LastDataRow)C16

I would greatly appreciate any help....

Thanks


--
Piranha
------------------------------------------------------------------------
Piranha's Profile: http://www.excelforum.com/member.php...o&userid=20435
View this thread: http://www.excelforum.com/showthread...hreadid=474649