dynamically increase x axis

[vicky]

I know there have been earlier posts on this but I am new to VBA and
i am finding it difficult to find a solution for this.... i have few
charts in a sheet and its range is 1 ROW .BUT every month it keeps
increasing by 1 column. i.e if the data range for a chart is $b$7:$cm
$7 then next month it will be $b$7:$cn$7.... for all the charts in
that sheet the data range will be increased by 1 column. month is on x
axis . so charts have to get updated with every month ....... hope
anyone can give a code snippet for this.......

i.e data range for few graphs present in tat sheet

from $b$8:$cm$8 .. it will change to $b$8:$cn$8

from $b$9:$cm$9 to it will change to $b$9:$cn$9

from $b$10:$cm$10 to it will change to $b$10:$cn$10

 

[Paul]

Name the ranges you wish to use - use the named ranges in your chart.

As you add data to the right, rename the ranges and the chart will
automatically update.
If you're happy with a (one column) gap at the right end of your chart ,
make the range one more column than your data, then insert a column to enter
the next set. This will automatically change your range and it won't need
renaming.

 

[Jarek Kujawa]

Excel 2003
Insert->Name-Define->

in Refers To window insert the formula =OFFSET(your_worksheet!$B
$7,,,COUNT(your_worksheet!$B$7:$IV$7),1)

give it a name say your_worksheet!Data

do the same to define a range for your X axis

and for other ranges and charts

change datasources for all charts

 

[Andreww]

Excel 2003
Insert->Name-Define->

in Refers To window insert the formula =OFFSET(your_worksheet!$B
$7,,,COUNT(your_worksheet!$B$7:$IV$7),1)

give it a name say your_worksheet!Data

do the same to define a range for your X axis

and for other ranges and charts

change datasources for all charts







- Show quoted text -

Sorry folks, I hate that offset function. I tend to have a cell where
the number of column is hardcoded and use that within a range.

I spent a good chunk of time trying to do exact same thing yesterday
and concluded it wasn't worth the effort in getting the offset...
counta thing working.

HTH.

Andrew

 

[Jarek Kujawa]

with number of cols in A1 define a name with this function:

=INDIRECT("$B$7:"&ADDRESS(1,COLUMN($B$7)+A1))

 

[john]

Vicky,
if you want to try a VBA solution then something like following may do what
you want.
I have assumed that you have 3 charts on same worksheet & that sheet is
named "Sheet1" You will need to correct this as required.

Place cose in standard module - when run should update the sourcedata for
each chart:

Sub UpdateCharts()
Dim lastcol As Integer
Dim na As Integer
Dim TheChartObj As ChartObject
Dim TheChart As Chart
Dim SourceData As Range
Dim ws1 As Excel.Worksheet

'name of the worksheet where charts are stored
'change name as required
Set ws1 = ThisWorkbook.Worksheets("Sheet1")

'3 charts on same worksheet?
For na = 1 To 3

Set TheChartObj = ws1.ChartObjects(na)
Set TheChart = TheChartObj.Chart

'find last used column in row
lastcol = ws1.Cells.Rows(na + 7).Find(What:="*", _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByColumns).Column


Set SourceData = ws1.Range(ws1.Cells(na + 7, 2), ws1.Cells(na + 7,
lastcol))

'update sourcedata
TheChart.SetSourceData _
Source:=SourceData, _
PlotBy:=xlRows

TheChartObj.Visible = True


Set TheChartObj = Nothing
Set TheChart = Nothing

Next na

End Sub