Chart Macro help needed

[henio]

Hi all,

I inherited this code which changes the start and end dates on the X axis for a graph. I can't figure out how to change the series ranges to match.
As always, any help would be appreciated.

Cheers,
Henio

For Each MyChart In ActiveSheet.ChartObjects
MyChart.Select 'select chart

On Error Resume Next
'actually change date
With ActiveChart.Axes(xlCategory)
.MinimumScale = StartDate 'start date
.MaximumScale = EndDate 'end date
.BaseUnitIsAuto = True
.MajorUnit = 1
.MajorUnitScale = xlDays
.MinorUnitIsAuto = True
.Crosses = xlAutomatic
.AxisBetweenCategories = True
.ReversePlotOrder = False
End With
'change direction of text
With ActiveChart.Axes(xlCategory).TickLabels
.Alignment = xlCenter
.Offset = 100
.Orientation = xlUpward
End With

 

[Adamba]

henio,

Are your ranges individual or counted as one source?

if it is a single range the 'record macro' function gave me range access like:


Sub Macro1()
ActiveChart.SetSourceData Source:=Sheets("YOY Pivots&quot.Range("B62:E87&quot, _
PlotBy:=xlColumns
End Sub


If its multiple/seperate ranges it gave me:


Sub Macro2()
ActiveChart.SeriesCollection(1).Values = "='Weekly Pivots'!R117C2:R117C144"
ActiveChart.SeriesCollection(2).Values = "='Weekly Pivots'!R124C3:R124C144"
ActiveChart.Axes(xlCategory).Select
End Sub



Adam

 

[RobBroekhuis]

What are you trying to accomplish? You need code that finds the data range corresponding to the axis range?


Rob

 

[henio]

All,

I found a thread thread68-685140 with excellent contributions from Skip about how to do this without VBA. Obviously the guy who bequeathed the spreadsheet to me didn't know about this either!

Rgds,
Henio

 

[SkipVought]

henio

Use the OFFSET Function to define each value range AND the Category range.

The OFFSET function is inserted in the Insert/Name/Define window, each with a unique range name. By using a cell reference in the 2nd or 3rd argument, the STARTING point of the range can be varied and by using another cell reference in the 4th or 5th argument, the Height or Width (number of data points) can be varied if required.

Then, just use the range names in the Source Data window of your chart.



Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com