Plotting Data on a Scatter Chart

[bennhrios9]

Hello, I have this data table that I need to plot on a scatter chart. How can I plot them quickly, maybe using VBA? In the screenshot below, each series would be two columns starting at C:D with "Temp" being the x-values and "Atten1" would being the y-values. There's about 1,800 series from this worksheet needed to be plotted so that's why I would like a way to do this quickly, please. The chart would look similar to what is on the screenshot.

 

[NdNoviceHlp]

I'm fairly certain that U won't be able to add 1800 series' to a chart but here's some code that seems like it would work for you. Adjust the sheet name to suit. HTH. Dave

Sub charttest()
Dim XRng As Range, YRng As Range, Cnt As Integer, LastRow As Integer
Dim objCht As ChartObject, LastCol As Integer, Cntr As Integer
Dim MyChart As Chart, ChartName As String, MyChartFrame As ChartObject
Dim ws As Worksheet

Set ws = Sheets("sheet1")
'remove previous chart
If ws.ChartObjects.Count > 0 Then
Set MyChartFrame = ws.ChartObjects(ws.ChartObjects.Count)
Set MyChart = MyChartFrame.Chart
'Remove SheetName from ChartName
ChartName = MyChart.Name
ChartName = Right(ChartName, Len(ChartName) - Len(ws.Name) - 1)
ws.ChartObjects(ChartName).Delete
End If

'add chart
Set objCht = ws.ChartObjects.Add(10, 20, 500, 200)
With ws
    LastCol = .Cells(3, .Columns.Count).End(xlToLeft).Column
    LastRow = .Range("C" & .Rows.Count).End(xlUp).Row
End With
For Cnt = 3 To LastCol Step 2
With ws
Set XRng = .Range(.Cells(3, Cnt), .Cells(LastRow, Cnt))
Set YRng = .Range(.Cells(3, Cnt + 1), .Cells(LastRow, Cnt + 1))
End With
Cntr = Cntr + 1
With objCht
.Chart.SeriesCollection.NewSeries
.Chart.SeriesCollection(Cntr).Values = XRng
.Chart.SeriesCollection(Cntr).XValues = YRng
.Chart.SeriesCollection(Cntr).ChartType = xlXYScatterSmoothNoMarkers
End With
Next Cnt

 

[bennhrios9]

I'm fairly certain that U won't be able to add 1800 series' to a chart but here's some code that seems like it would work for you. Adjust the sheet name to suit. HTH. Dave

Sub charttest()
Dim XRng As Range, YRng As Range, Cnt As Integer, LastRow As Integer
Dim objCht As ChartObject, LastCol As Integer, Cntr As Integer
Dim MyChart As Chart, ChartName As String, MyChartFrame As ChartObject
Dim ws As Worksheet

Set ws = Sheets("sheet1")
'remove previous chart
If ws.ChartObjects.Count > 0 Then
Set MyChartFrame = ws.ChartObjects(ws.ChartObjects.Count)
Set MyChart = MyChartFrame.Chart
'Remove SheetName from ChartName
ChartName = MyChart.Name
ChartName = Right(ChartName, Len(ChartName) - Len(ws.Name) - 1)
ws.ChartObjects(ChartName).Delete
End If

'add chart
Set objCht = ws.ChartObjects.Add(10, 20, 500, 200)
With ws
    LastCol = .Cells(3, .Columns.Count).End(xlToLeft).Column
    LastRow = .Range("C" & .Rows.Count).End(xlUp).Row
End With
For Cnt = 3 To LastCol Step 2
With ws
Set XRng = .Range(.Cells(3, Cnt), .Cells(LastRow, Cnt))
Set YRng = .Range(.Cells(3, Cnt + 1), .Cells(LastRow, Cnt + 1))
End With
Cntr = Cntr + 1
With objCht
.Chart.SeriesCollection.NewSeries
.Chart.SeriesCollection(Cntr).Values = XRng
.Chart.SeriesCollection(Cntr).XValues = YRng
.Chart.SeriesCollection(Cntr).ChartType = xlXYScatterSmoothNoMarkers
End With
Next Cnt

Thank you for your code. You were right, a chart can't have 1800 series. It says a chart can only have up to 256 series, so it won't run the code. Can you update the code so it can have up to 256 series?

 

[bennhrios9]

I'm fairly certain that U won't be able to add 1800 series' to a chart but here's some code that seems like it would work for you. Adjust the sheet name to suit. HTH. Dave

Sub charttest()
Dim XRng As Range, YRng As Range, Cnt As Integer, LastRow As Integer
Dim objCht As ChartObject, LastCol As Integer, Cntr As Integer
Dim MyChart As Chart, ChartName As String, MyChartFrame As ChartObject
Dim ws As Worksheet

Set ws = Sheets("sheet1")
'remove previous chart
If ws.ChartObjects.Count > 0 Then
Set MyChartFrame = ws.ChartObjects(ws.ChartObjects.Count)
Set MyChart = MyChartFrame.Chart
'Remove SheetName from ChartName
ChartName = MyChart.Name
ChartName = Right(ChartName, Len(ChartName) - Len(ws.Name) - 1)
ws.ChartObjects(ChartName).Delete
End If

'add chart
Set objCht = ws.ChartObjects.Add(10, 20, 500, 200)
With ws
    LastCol = .Cells(3, .Columns.Count).End(xlToLeft).Column
    LastRow = .Range("C" & .Rows.Count).End(xlUp).Row
End With
For Cnt = 3 To LastCol Step 2
With ws
Set XRng = .Range(.Cells(3, Cnt), .Cells(LastRow, Cnt))
Set YRng = .Range(.Cells(3, Cnt + 1), .Cells(LastRow, Cnt + 1))
End With
Cntr = Cntr + 1
With objCht
.Chart.SeriesCollection.NewSeries
.Chart.SeriesCollection(Cntr).Values = XRng
.Chart.SeriesCollection(Cntr).XValues = YRng
.Chart.SeriesCollection(Cntr).ChartType = xlXYScatterSmoothNoMarkers
End With
Next Cnt

Nvm. It works. It still plots the chart even with that error message. Thank you.

 

[NdNoviceHlp]

It seems: 256 x 2 = 512 + 2 (start on column "C") = 514...

For Cnt = 3 To 514 Step 2

Dave