Defining series range for named range
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
|