Naming a cell from VB

  • Thread starter Thread starter Brettjg
  • Start date Start date
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
 
It worked fine in my little test.

What do you have in namecol and namerow at that point?
 
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...
 
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...
 
Back
Top