Unable to set Xvalues of Series
I am so confused on why setting the xvalues in the fashion I am doing below would make it error?
I checked the debugger and the variables are correct,i.e. the sheetname is referring to the correct name, the column and row numbers are correct.
Anyone know a better or error-free way of setting the Xvalues? I looked everywhere on the internet, and it seems returning a range by using the Range property to Values property is the most efficient way.
This would error if I replot different data to the same graph. This function is used to plot all my data in my spreadsheet. Doesn't make sense why this woudl fail after a certain number of time. any help? Thanks!
VB Code:
Public Function plotGraph(ByVal mychart As Chart, _
ByVal xaxisCol As Integer, ByVal secaxisCol As Integer, _
ByVal avgcol As Integer, ByVal stdcol As Integer, _
ByVal startRow As Integer, ByVal endRow As Integer, _
ByVal appOpt As Boolean, ByVal sheetname As String, ByVal startprimrow As Integer)
Static commcount As Integer
Static primcount As Integer
Static avgcount As Integer, stdcount As Integer
Static saveInitRow As Integer, primRowCount As Integer
Dim x As Integer, row As Integer, labelrow As Integer
Dim storeValues()
labelrow = 6
With mychart
.Axes(xlCategory, xlSecondary).TickLabelSpacing = 1
.Axes(xlCategory, xlPrimary).TickLabelSpacing = 1
With .Axes(xlCategory, xlSecondary).TickLabels.Font
.Name = "Arial Narrow"
.FontStyle = "Regular"
.Size = 6
End With
With .Axes(xlCategory, xlPrimary).TickLabels.Font
.Name = "Arial Narrow"
.FontStyle = "Regular"
.Size = 6
End With
'plot secondary x axis values
.SeriesCollection(2).XValues = ""
.SeriesCollection(2).XValues = Range(Sheets(sheetname).Cells(startRow, secaxisCol), Sheets(sheetname).Cells(endRow, secaxisCol))
'"=" & sheetname & "!R" & startRow & "C" & secaxisCol & ":R" & endRow & "C" & secaxisCol
'plot primary x axis values
.SeriesCollection(1).XValues = ""
.SeriesCollection(1).XValues = Range(Sheets(sheetname).Cells(startRow, xaxisCol), Sheets(sheetname).Cells(endRow, xaxisCol)) 'errors here
end with