I need to create a bunch of named ranges. The series range I want to use
would be created using this OFFSET formula

I want do do the following:

For i = 2 To Cells(Rows.Count, "A").End(xlUp).Row
seriesrange =
OFFSET(Statics!$C$1,MATCH(Statics!$C:$C,Statics!$B $1)-1,0,COUNTIF(Statics!$C:$C,Statics!$B$1),3)

Next I

Anything that reads Statics!$B$1 will be replaced with Cells(i, 2)

What needs to change so that seriesrange is dynamically determined based on
the value of Cells(i,2)?

Thanks in advance,
Barb Reinhardt

U¿ytkownik "Barb Reinhardt"
napisa³ w wiadomo¶ci
...
I need to create a bunch of named ranges. The series range I want to use
would be created using this OFFSET formula

I want do do the following:

For i = 2 To Cells(Rows.Count, "A").End(xlUp).Row
seriesrange =

OFFSET(Statics!$C$1,MATCH(Statics!$C:$C,Statics!$B $1)-1,0,COUNTIF(Statics!$C
:$C,Statics!$B$1),3)

Next I

Anything that reads Statics!$B$1 will be replaced with Cells(i, 2)

What needs to change so that seriesrange is dynamically determined based
on
the value of Cells(i,2)?

Thanks in advance,
Barb Reinhardt


this sub will name each of range using value of cells(i,2)

For i = 2 To Cells(Rows.Count, "A").End(xlUp).Row
seriesrange = Cells(i, 1).Value
ActiveWorkbook.Names.Add Name:="seriesrange", _
RefersToR1C1:="=OFFSET(Statics!r1c3,MATCH(c3,r1c2)-1,0,COUNTIF(c3,r1c2),3)"
Next i

hth
mcg