Relative Reference NAME Ranges

Gunswick

Board Regular
Joined
Jun 18, 2008
Messages
179
How can I make creating a named range relative to the active cell? So that I can click on any cell and a named range is created in the adjacent column for 5 rows.
e.g. click cell c5, and a named range from cells d5:d10 is created when i run the macro

Code:
ActiveCell.Offset(0, 1).Range("A1:A11").Select
    ActiveWorkbook.Names.Add Name:="fygainloss", RefersToR1C1:= _
        "='2007'!R5C3:R15C3"

this is what i have so far in my macro, I cant figure how to make it set the range specified by the offset, it always just uses absolute ranges from cell A1.
 

Some videos you may like

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,067
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Code:
    ActiveWorkbook.Names.Add Name:="fygainloss", RefersToR1C1:= _
        "='2007'!RC[1]:R[5]C[1]"
should work.
 

Gunswick

Board Regular
Joined
Jun 18, 2008
Messages
179
that works perfectly, thank you very much!

what about making the name unique? so that I could create multiple named ranges.

i dont mind what the names actually are, as long as they are unique. perhaps a random number generator or an incremental counter?

(this is to help make copying charts with different data sources easier by the way).
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,067
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Ah, I think I may have misunderstood. That creates a named range that is always one column to the right of the activecell and 6 rows high. So if you select D5, it refers to E5:E10. If you want a fixed reference that is established relative to the cell that is active at the time, then you need something like this:
Code:
    ActiveWorkbook.Names.Add Name:="fygainloss" & ActiveCell.Address(0, 0), _
         RefersTo:="='2007'!" & ActiveCell.Offset(0, 1).Resize(6).Address
 

Gunswick

Board Regular
Joined
Jun 18, 2008
Messages
179

ADVERTISEMENT

its basicly so that i can copy a template chart for about 30 different records, which are all on one sheet.

so that i can just click on the top left cell of each "record", i.e the information that will go into the chart (e.g. 2007 gains + losses, 2008 gains+losses etc)

so i need to combine what you wrote for me...

Code:
ActiveWorkbook.Names.Add Name:="fygainloss", RefersToR1C1:= _
        "='2007'!RC[1]:R[10]C[1]"

and..

Code:
Sheets("CES Eur Frt07").Select
    Sheets("CES Eur Frt07").Copy After:=Sheets(13)
    ActiveChart.ChartArea.Select
 
ActiveChart.SeriesCollection(1).Values = _
        "='FFR Forecasting Accuracy 2007 and 08.xls'!fygainloss"

which works, it copies the chart and updates the data (there are actually 8 series, but its the same thing repeated 8 times).

so i can now just click on the top left of each record and it makes a chart (rather than manually editing the data source of each copied chart).

However I need to make 30 individual ones, so obviously if i set new ranges for the same names, i will have 30 charts with the same info! = uselss :LOL:

so i need to make the name unique, so that i can create each chart uniquely.

I dont think randomise will do it well, so i think i need an counter for the name. e.g. fygainloss1, fygainloss2, fygainloss3 etc for each named range i create with your code :cool:
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,067
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
The version I just posted appends the cell address to the name of the range so they will all be unique.
 

Gunswick

Board Regular
Joined
Jun 18, 2008
Messages
179

ADVERTISEMENT

ah yes i see, i didnt understand it before. It uses offset instead of the rc[0]r[1]c[0] etc

so that gives a unique name, which i need. So then how do i get it into my series change code

Code:
Sheets("CES Eur Frt07").Select
    Sheets("CES Eur Frt07").Copy After:=Sheets(13)
    ActiveChart.ChartArea.Select

ActiveChart.SeriesCollection(1).Values = _
        "='FFR Forecasting Accuracy 2007 and 08.xls'!fygainloss"

so that fygainloss is fygainlossActiveCellAddress (i.e. the unique name we just created) ?
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,067
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Dunno! How do you know which range relates to which chart?
 

Gunswick

Board Regular
Joined
Jun 18, 2008
Messages
179
as an example, i name the range and as you intended it becomes fygainlossB47 for example

so i need to get fygainlossB47 into my data series code in the chart, else the charts are not unique. can we use the active cell code you used before? fygainloss" & ActiveCell.Address(0, 0)

e.g.

Rich (BB code):
Sub chartcopy8()
 
'set my range as a unique name (thanks rorya)
      ActiveWorkbook.Names.Add Name:="fygainloss" & ActiveCell.Address(0, 0), _
         RefersTo:="='2007'!" & ActiveCell.Offset(0, 1).Resize(11).Address
 
 
'selects my template chart, and makes a copy
     Sheets("CES Eur Frt07").Select
    Sheets("CES Eur Frt07").Copy After:=Sheets(13)
    ActiveChart.ChartArea.Select
 
'changes the series range from the original to my new unique name range 
'(should be fygainlossB47 for example)
      ActiveChart.SeriesCollection(1).Values = _
        "='FFR Forecasting Accuracy 2007 and 08.xls'!fygainloss"      
 
End Sub
 
Last edited:

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,067
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Try this:
Code:
   Dim strName As String
   strName = "fygainloss" & ActiveCell.Address(0, 0)
'set my range as a unique name (thanks rorya)
      ActiveWorkbook.Names.Add Name:=strName, _
         RefersTo:="='2007'!" & ActiveCell.Offset(0, 1).Resize(11).Address
 
 
'selects my template chart, and makes a copy
     Sheets("CES Eur Frt07").Select
    Sheets("CES Eur Frt07").Copy After:=Sheets(13)
    ActiveChart.ChartArea.Select
 
'changes the series range from the original to my new unique name range
'(should be fygainlossB47 for example)
      ActiveChart.SeriesCollection(1).Values = _
        "='FFR Forecasting Accuracy 2007 and 08.xls'!" & strName
 

Watch MrExcel Video

Forum statistics

Threads
1,122,561
Messages
5,596,849
Members
414,107
Latest member
Tigretto

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top