How to redim the XValues on .SeriesCollection(2)?

[podi]

I have the coding like this:

For Each chtObj In chtObjs
Select Case chtObj.Name
Case Is = "PriceTime"
With chtObj.Chart
.SeriesCollection(1).XValues = "='Q1'!R41C13:R" & MaxEnd & "C13"
.SeriesCollection(1).Values = "='Q1'!R41C14:R" & Bond1End & "C14"
.SeriesCollection(2).XValues = "='Q1'!R41C13:R" & MaxEnd & "C13"
.SeriesCollection(2).Values = "='Q1'!R41C15:R" & Bond2End & "C15"
End With
End Select
Next

When each time the field change, it will auto trigger this module and I expect the X-axis can refresh each time. However it can't. Is it because the array to set X-axis should be redim?

 

[Andrew Poulsom]

Are you getting an error? What is assigned to MaxEnd, Bond1End and Bond2End?

If you want to make your chart dynamic look here:

http://peltiertech.com/Excel/Charts/DynamicColumnChart1.html

 

[podi]

They are integers that needs to create the chart. When I reassign the values to them the chart cannot refresh automatically. Like I assign the X-axis value "100" and then change "80" the x-axis still set "100"

 

[Andrew Poulsom]

Sorry, I don't understand. Please post the entire procedure.

 

[podi]

Here is the whole function:

Public Function Calibrate(ByVal Bond1M As Double, ByVal Bond2M As Double) As Integer
'
' Re-calibrate the graph
'

Dim chtObjs As ChartObjects
Dim chtObj As ChartObject
Set chtObjs = ActiveSheet.ChartObjects

Dim Bond1End, Bond2End, MaxEnd As Double
Bond1End = CInt(ActiveSheet.Range("Bond1M") * 2 + 41)
Bond2End = CInt(ActiveSheet.Range("Bond2M") * 2 + 41)
MaxEnd = Application.WorksheetFunction.Max(Bond1End, Bond2End)


For Each chtObj In chtObjs
Select Case chtObj.Name
Case Is = "PriceTime"
With chtObj.Chart
.SeriesCollection(1).XValues = "='Q1'!R41C13:R" & MaxEnd & "C13"
.SeriesCollection(1).Values = "='Q1'!R41C14:R" & Bond1End & "C14"
.SeriesCollection(2).XValues = "='Q1'!R41C13:R" & MaxEnd & "C13"
.SeriesCollection(2).Values = "='Q1'!R41C15:R" & Bond2End & "C15"
End With
End Select
Next

Calibrate = 0

End Function

The idea is to re-assign the value to .SeriesCollection() to re-plot the graph. The graph does re-plot but the scale would not change. For example, if the initial size of X is change from 100 unit to 50 unit, the graph will plot to 50 but 50-100 is still shown (which have the value 0). What I like to achieve is to re-plot it from 0-50 only.

Thanks!

 

[Andrew Poulsom]

If the axis scale maximum is automatic it should adjust. You can set it to automatic like this:

.Axes(xlCategory).MaximumScaleIsAuto = True

Or you can assign it a value like this:

.Axes(xlCategory).MaximumScale = 50