Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding name to a range of cells
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding name to a range of cells
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" -- HTH RP "Tammy" wrote in message om... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sum func. is adding cells not in specified range | Excel Worksheet Functions | |||
adding the same prefix or suffix to a range of cells | Excel Discussion (Misc queries) | |||
Adding colour to a range of cells based on one of the cells v... | Excel Discussion (Misc queries) | |||
Adding comments in a range (!) of cells | Excel Programming | |||
sheets.range and adding cells that are out of the range | Excel Programming |