Adding name to a range of cells

[Tammy]

I need to write some code which creates a name around a range of
cells. The worksheet is linked to another workbook so the size of the
range may change. The macro works out how many rows need to be
included in the name and then creates it. This works except the
starting cell differs depending on the location of the cursor. It
creates a name with the correct number of rows but if the cursor is in
A14 for example the name is created around A15:G30. It only works if
I have the cursor in A1 it works but this isn't feasible as the
worksheet is going to be hidden.

The code I've written has the RefersToR1C1 as a variable but I can't
get it to work even if the range is hardcoded in (see below). I've
debugged the macro but the variable holding the range is correct each
time.

ActiveWorkbook.Names.Add "CourseInformation",
RefersToR1C1:="=Courses!$A2:$G17"

I've searched in groups but can't find anything that refers to this
problem.

Any assistance greatly appreciated!

Tammy

 

[Bob Phillips]

Tammy,

You don't say where you store the the 'how many rows'. Assuming you mean the
final row by this, in a variable called cRows, then you can get the range
address with

Set myRng = worksheets("Courses").Cells(1,
Activecell.column).Resize(cRows,1)

Then set the name with

myRng.Name = "CourseInformation"

 

[keepITcool]

Tammy,

your first mistake is assigning an A1 cell reference to the R1C1
property.

BUT...

When you use defined names I dont think you want RELATIVE references.
(which in fact would insert a formula rather than a range..)
The calculation of names with relative formulas is dependent on the
active cell...


So ..

assuming you want to add the ABSOLUTE reference "$A$2:$G$17"
then the easiest alternative, will both add ABSOLUTE references:
Range("Courses!A2:G17").Name = "CourseInfo"
Or
Names.add "CourseInfo", RefersTo:="=Courses!$A$2:$G$17"



keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >