.Chart.SeriesCollection.NewSeries.Values

[Kevin]

I loop through the following code to populate a 1D array (Series4Array) from
one of two different 2D arrays (HistDB8 or HistDB9).

If ChartToggle = True Then
ReDim Series4Array(UBound(HistDB8, 1) - 1) 'total percent
For ii& = LBound(HistDB8, 1) + 1 To UBound(HistDB8, 1)
For jj& = LBound(HistDB8, 2) + 1 To UBound(HistDB8, 2)
If ThisDG = HistDB8(1, jj&) Then
Series4Array(ii& - 1) = Round(HistDB8(ii&, jj&), 4)
End If
Next jj&
Next ii&
Else
ReDim Series4Array(UBound(HistDB9, 1) - 1) 'total cost
For ii& = LBound(HistDB9, 1) + 1 To UBound(HistDB9, 1)
For jj& = LBound(HistDB9, 2) + 1 To UBound(HistDB9, 2)
If ThisDG = HistDB9(1, jj&) Then
Series4Array(ii& - 1) = HistDB9(ii&, jj&)
End If
Next jj&
Next ii&
End If

I then use the 1D array (and three others) to initialize values in separate
chart SeriesCollections. I loop through the following code to create one
total percent chart and one total cost chart for a few dozen configurations.

With ActiveSheet.ChartObjects.Add _
(Left:=MonChPosL, Width:=MonChPosW, Top:=MonChPosT, Height:=MonChPosH)

.Chart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:="Line -
Column on 2 Axes"

With .Chart.SeriesCollection.NewSeries
.Values = Series1Array
.XValues = SeriesXValuesArray
.ChartType = xlColumnStacked
End With
With .Chart.SeriesCollection.NewSeries
.Values = Series2Array
.ChartType = xlColumnStacked
End With
With .Chart.SeriesCollection.NewSeries
.Values = Series3Array
.ChartType = xlColumnStacked
End With
With .Chart.SeriesCollection.NewSeries
.Values = Series4Array
.ChartType = xlLineMarkers
.AxisGroup = 2
End With

....
....
....

End With


My problem is the values of the fourth SeriesCollection is always a set of
zeros when Series4Array is taken from HistDB9 but not when its taken from
HistDB8. The strange thing is I get no runtime errors.

I've embedded a msgbox and confirmed that Series4Array is good.

With .Chart.SeriesCollection.NewSeries

If ChartToggle = False Then
For ii& = LBound(Series4Array) To UBound(Series4Array)
MsgBox "DG = " & ThisDG & "; Series4Array(" & ii& & ") = " &
Series4Array(ii&)
Next ii&
End If

.Values = Series4Array
.ChartType = xlLineMarkers
.AxisGroup = 2
End With

Any help will be greatly appreciated.

Thanks much.


Kevin

 

[Kevin]

Problem solved!

I changed the ChartToggle switch around to see if the problem was with the
source data; changed the first line below to:

If ChartToggle = False Then

The result was that the percent data presented in the cost chart. But still
no cost data in the other chart.

So I went to the source of the cost data (an Access table) and changed the
data type from currency to Number (Long Integer). And voila!

So it goes.


Kevin