Is there a way to easily generate a series of named ranges

[Ulrik Willemoes]

I am looking for a way to easily generate a series of names for some dynamic ranges.


For instance I want to define these dynamic ranges for later use in charts:


KPI_1 = OFFSET(KPI,1,3,1,MTH)

KPI_2 = OFFSET(KPI,2,3,1,MTH)

KPI_3 = OFFSET(KPI,3,3,1,MTH)

KPI_4 = OFFSET(KPI,4,3,1,MTH)

KPI_5 = OFFSET(KPI,5,3,1,MTH)

.

.

KPI_50 = OFFSET(KPI,50,3,1,MTH)


'KPI' is just a named starting point for the offset.

'MTH' is just a named range containing the dynamic width of the range.


As you can see it is only the row number that varies in the ranges.


When I have 50 KPIs, it takes a while to define all the ranges in the normal way, or by means of the name manager addin I have.


So, therefore I am wondering if there is a way of importing/generating the formulas? For instance from a list in excel?


Brgds,

Ulrik

 

[Hui]

Ulrik

Have a try of this macro

You will need to copy and paste it into a code or worksheet module in VBA

and run it just once

[pre]

Sub Setup_KPI_Names()

For i = 1 To 50

Nm = "KPI_" & Trim(Str(i))
NmTo = "=Offset(KPI," + Trim(Str(i)) + ", 3, 1, MTH)"

ActiveWorkbook.Names.Add Name:=Nm, RefersToR1C1:=NmTo
ActiveWorkbook.Names(Nm).Comment = ""
Next

End Sub

[/pre]

 

[Ulrik Willemoes]

Hi Hui,


Thanks for the quick reply.


However, I can't make the code work.

Initially I got a compile error so I added the Dim's but that didn't help either.


Sub Setup_KPI_Names()


Dim i As Integer

Dim Nm As String

Dim NmTo As String


For i = 1 To 50


Nm = "KPI_" & Trim(Str(i))

NmTo = "=Offset(KPI," + Trim(Str(i)) + ", 3, 1, MTH)"


ActiveWorkbook.Names.Add Name:=Nm, RefersToR1C1:=NmTo

ActiveWorkbook.Names(Nm).Comment = ""

Next


End Sub

 

[Ulrik Willemoes]

Could language have something to say - I use a non English edition?

I am thinking of the Offset string...

 

[Hui]

The above code works in 2007 and 2010


The following code works in Excel 2003 and before

[pre]

Sub Setup_KPI_Names()

For i = 1 To 50

Nm = "KPI_" & Trim(Str(i))
NmTo = "=Offset(KPI," + Trim(Str(i)) + ", 3, 1, MTH)"

ActiveWorkbook.Names.Add Name:=Nm, RefersToR1C1:=NmTo

Next

End Sub

[/pre]

Make sure you have a defined Name KPI and MTH already setup or you will get an error message.


I cannot comment on non-English versions of excel as I have no experience with them, although you should be able to easily work out the differences

 

[Ulrik Willemoes]

Hui,


You just made my day - thanks a lot!


I am actually running 2007 but can only make your 2003 code work.

Btw, no translation of the offset string was needed.


Ulrik