M
MikeF
Attempting to make a 3d column chart fed from dynamic range columns be more
dynamic, taking into account varying rows and columns.
A column in the table below is added almost every day. The 3d column chart
properly accomodates it using this dynamic range
[dynrngRevProg]:
RevenueProgress!$A$1:$A$6,OFFSET(RevenueProgress!$C$1,0,0,6,COUNTA(RevenueProgress!$C$1:$g$1)-1)
And the following code:
Sheets("RevProg").Select
ActiveSheet.ChartObjects("Chart 2").Activate
With ActiveChart
..SetSourceData _
ActiveWorkbook.Names("dynrngrevprog").RefersToRange, _
PlotBy:=xlColumns
End With
Would now like to have the chart fed by dynamic *rows* as well as columns,
because the territories sometimes change [a country is added] or do not
report.
As follows is my last of many attempts at a working dynamic range:
OFFSET(RevenueProgress!$A$1,0,0,COUNTA(RevenueProgress!$a$1:$a$99),0),OFFSET(RevenueProgress!$C$1,0,0,COUNTA(RevenueProgress!$a$1:$a$99),COUNTA(RevenueProgress!$C$1:$g$1)-1)
Date Region 3/27/2010 3/29/2010 3/30/2010 3/31/2010 Last Variance
7/2/2010 North 159687 177527 183333 187428 4095
7/3/2010 South 153852 169548.5 175211 178392 3181
7/17/2010 East 248488 267398 271895 276200 4305
7/20/2010 West 53172 58403 59038 60620 1582
7/24/2010 Canada 484468 505834 510813 518862 8049
Is it the dynamic range that's wrong, or does the code need to be adjusted?
Any assistance will be greatly appreciated.
Thanx.
- Mike
dynamic, taking into account varying rows and columns.
A column in the table below is added almost every day. The 3d column chart
properly accomodates it using this dynamic range
[dynrngRevProg]:
RevenueProgress!$A$1:$A$6,OFFSET(RevenueProgress!$C$1,0,0,6,COUNTA(RevenueProgress!$C$1:$g$1)-1)
And the following code:
Sheets("RevProg").Select
ActiveSheet.ChartObjects("Chart 2").Activate
With ActiveChart
..SetSourceData _
ActiveWorkbook.Names("dynrngrevprog").RefersToRange, _
PlotBy:=xlColumns
End With
Would now like to have the chart fed by dynamic *rows* as well as columns,
because the territories sometimes change [a country is added] or do not
report.
As follows is my last of many attempts at a working dynamic range:
OFFSET(RevenueProgress!$A$1,0,0,COUNTA(RevenueProgress!$a$1:$a$99),0),OFFSET(RevenueProgress!$C$1,0,0,COUNTA(RevenueProgress!$a$1:$a$99),COUNTA(RevenueProgress!$C$1:$g$1)-1)
Date Region 3/27/2010 3/29/2010 3/30/2010 3/31/2010 Last Variance
7/2/2010 North 159687 177527 183333 187428 4095
7/3/2010 South 153852 169548.5 175211 178392 3181
7/17/2010 East 248488 267398 271895 276200 4305
7/20/2010 West 53172 58403 59038 60620 1582
7/24/2010 Canada 484468 505834 510813 518862 8049
Is it the dynamic range that's wrong, or does the code need to be adjusted?
Any assistance will be greatly appreciated.
Thanx.
- Mike