Defining series range for named range

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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" <[email protected]>
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
Click to expand...

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
 
Back
Top