Naming a cell from VB

B

Brettjg

Hello there, I want to set up a procedure to insert somes names into a
workbook that refer to another workbook (because the formulas in Excel get
WAY too long otherwise) and am using the following code (which of course does
not work):

ActiveWorkbook.Names.Add Name:="dens", _
RefersToR1C1:="=[Tbl.cost.xls]tbl.cost!R" & namerow & "C" & namecol

IT'S THE "RefersTo" THAT IT DOES NOT LIKE.

The following code is what I get from recording a macro, but I can't use it
because the cell locations may change from time to time:
ActiveWorkbook.Names.Add Name:="tbl.cost", _
RefersToR1C1:="=[Tbl.cost.xls]tbl.cost!R3C2:R228C63"

Can someone steer me in the right direction please? Regards, Brett
 
B

Bob Phillips

It worked fine in my little test.

What do you have in namecol and namerow at that point?
 
B

Brettjg

Hi Bob, there was garbage in there, and I assumed there wasn't. Ooops! Thank
for the tip. Brett

Bob Phillips said:
It worked fine in my little test.

What do you have in namecol and namerow at that point?

--
__________________________________
HTH

Bob

Brettjg said:
Hello there, I want to set up a procedure to insert somes names into a
workbook that refer to another workbook (because the formulas in Excel get
WAY too long otherwise) and am using the following code (which of course
does
not work):

ActiveWorkbook.Names.Add Name:="dens", _
RefersToR1C1:="=[Tbl.cost.xls]tbl.cost!R" & namerow & "C" & namecol

IT'S THE "RefersTo" THAT IT DOES NOT LIKE.

The following code is what I get from recording a macro, but I can't use
it
because the cell locations may change from time to time:
ActiveWorkbook.Names.Add Name:="tbl.cost", _
RefersToR1C1:="=[Tbl.cost.xls]tbl.cost!R3C2:R228C63"

Can someone steer me in the right direction please? Regards, Brett
Click to expand...
Click to expand...
 
B

Bob Phillips

Sometimes you just need that other view <g>

--
__________________________________
HTH

Bob

Brettjg said:
Hi Bob, there was garbage in there, and I assumed there wasn't. Ooops!
Thank
for the tip. Brett

Bob Phillips said:
It worked fine in my little test.

What do you have in namecol and namerow at that point?

--
__________________________________
HTH

Bob

Brettjg said:
Hello there, I want to set up a procedure to insert somes names into a
workbook that refer to another workbook (because the formulas in Excel
get
WAY too long otherwise) and am using the following code (which of
course
does
not work):

ActiveWorkbook.Names.Add Name:="dens", _
RefersToR1C1:="=[Tbl.cost.xls]tbl.cost!R" & namerow & "C" & namecol

IT'S THE "RefersTo" THAT IT DOES NOT LIKE.

The following code is what I get from recording a macro, but I can't
use
it
because the cell locations may change from time to time:
ActiveWorkbook.Names.Add Name:="tbl.cost", _
RefersToR1C1:="=[Tbl.cost.xls]tbl.cost!R3C2:R228C63"

Can someone steer me in the right direction please? Regards, Brett
Click to expand...
Click to expand...
Click to expand...
 
Top