smr122
07-15-2008, 12:32 PM
I have come across a problem in my code that I am having trouble identifying. I have created 4 arrays based on data in a spreadsheet that was given to me, and I am trying to chart them; one as the x-axis and the others as y-axis values. I have provided my code for the array creation loop as well as the chart with-blocks. I don't get any run-time errors, but when the data is graphed, my three y-axis arrays show up as {0,0,...,0}. The x values are fine, its just the y values that are wrong. Also, when stepping through the program and printing values from the arrays at various steps, the data is correct. I am very new to VBA (two days in) and don't understand what I am doing wrong.
ReDim ClkDate(rowCount)
ReDim LSeries(rowCount)
ReDim MSeries(rowCount)
ReDim NSeries(rowCount)
For i = 1 To rowCount - 1
ClkDate(i) = Cells(counter, "H")
LSeries(i) = Cells(counter, "L")
MSeries(i) = Cells(counter, "M")
NSeries(i) = Cells(counter, "N")
counter = counter + 1
Next i
Sheet1.Activate
With Charts(tabTitle).SeriesCollection.NewSeries
.Name = "AttainedStd"
.Values = LSeries
.XValues = ClkDate
End With
With Charts(tabTitle).SeriesCollection.NewSeries
.Name = "Std Variance"
.Values = MSeries
.XValues = ClkDate
End With
With Charts(tabTitle).SeriesCollection.NewSeries
.Name = "%Efficiency"
.Values = NSeries
.XValues = ClkDate
End With
Any advice you guys can provide would be greatly appreaciated.
Edit: I found my solution. It was simply that I defined the arrays as strings, but to use them as a series they had to be integer values. It was a pretty stupid mistake and a simple fix, I just couldn't see it for the longest time.
ReDim ClkDate(rowCount)
ReDim LSeries(rowCount)
ReDim MSeries(rowCount)
ReDim NSeries(rowCount)
For i = 1 To rowCount - 1
ClkDate(i) = Cells(counter, "H")
LSeries(i) = Cells(counter, "L")
MSeries(i) = Cells(counter, "M")
NSeries(i) = Cells(counter, "N")
counter = counter + 1
Next i
Sheet1.Activate
With Charts(tabTitle).SeriesCollection.NewSeries
.Name = "AttainedStd"
.Values = LSeries
.XValues = ClkDate
End With
With Charts(tabTitle).SeriesCollection.NewSeries
.Name = "Std Variance"
.Values = MSeries
.XValues = ClkDate
End With
With Charts(tabTitle).SeriesCollection.NewSeries
.Name = "%Efficiency"
.Values = NSeries
.XValues = ClkDate
End With
Any advice you guys can provide would be greatly appreaciated.
Edit: I found my solution. It was simply that I defined the arrays as strings, but to use them as a series they had to be integer values. It was a pretty stupid mistake and a simple fix, I just couldn't see it for the longest time.