RunTime Error 13 in Dynamically Creating a Chart

[vkent]

Hi,

I am currently trying to dynamically build a line graph using the following VBA macro. What is really strange is that when I step through this code I do not encounter any issues, however, when I run the macro without stepping through, I encounter a Type Mismatch error in the line "sLOUForeChart.Chart.SeriesCollection.Add Source:=ActiveSheet.Range(Cells(CurRow, "AG"), Cells(CurRow, NumCols)) " in the first For/Next loop during the second iteration. Again, for some reason, I do not encounter an error when I step through the code. Even after it first blows up and I then Debug and step through, I do not run into the error.

Any thoughts???

***********
Sub refresh_table()

Dim CurRow As Integer
Dim NumRows As Integer
Dim NumCols As String
Dim ColCnt As Integer
Dim sLOURange As Range
Dim i As Integer
Dim sLOUForeChart As ChartObject

NumRows = 0
NumCols = ""
i = 0

Cells("80", "AG").Select

NumRows = 80

Do Until ActiveCell.Value = ""
ActiveCell.Offset(1, 0).Select
NumRows = NumRows + 1
Loop

NumRows = NumRows - 1

ColCnt = 0

Cells("80", "AG").Select

Do Until ActiveCell.Value = ""
ActiveCell.Offset(0, 1).Select
ColCnt = ColCnt + 1
Loop

ColCnt = ColCnt - 1

If ColCnt = 1 Then
NumCols = "AH"
ElseIf ColCnt = 2 Then
NumCols = "AI"
ElseIf ColCnt = 3 Then
NumCols = "AJ"
ElseIf ColCnt = 4 Then
NumCols = "AK"
End If


Set sLOUForeChart = ActiveSheet.ChartObjects("LOUForeChart")
sLOUForeChart.Chart.ChartType = xlLineMarkersStacked
Set sLOURange = ActiveSheet.Range(Cells("80", "AH"), Cells("80", NumCols))
sLOUForeChart.Chart.SetSourceData Source:=ActiveSheet.Range(Cells("1", "A"), Cells("1", "B"))

For i = 81 To NumRows
CurRow = i
sLOUForeChart.Chart.SeriesCollection.Add Source:=ActiveSheet.Range(Cells(CurRow, "AG"), Cells(CurRow, NumCols))
Next i

sLOUForeChart.Chart.SeriesCollection(1).Delete

x = 1
For i = 81 To NumRows
sLOUForeChart.Chart.SeriesCollection(x).XValues = sLOURange
x = x + 1
Next i

sLOUForeChart.Chart.ChartType = xlLineMarkers

sLOUForeChart.Select

End Sub

***********

[vkent]

Hi All,

I have figured out the issue with this..For some reason Excel requires me to select the chart after the creation of each series. So I just needed to add a line of code that selects the Chart at the beginning of each iteration in the For/Next Loop.

Thanks!