XY Multiple rows charting
 

Hi there,

I'm trying to create charts, where the y-data for each chart is a
different row, but the x-data is fixed. I'd like to create an x-y
scatter chart and create each chart as a new sheet, labeled with the
item in Col A. (Y data is in cols B-D, X data is in Row 1). Lastly,
I'd like to add a linear trendline to each.

I've been working unsuccessfully with macros. Any help would be much
appreciated.

XY Multiple rows charting
 

Hi,

Here is some code to get you started.

Sub Macro3()
'
Dim lngRow As Long
Dim shtData As Worksheet
Dim strData As String

Set shtData = Worksheets("Sheet1")
lngRow = 2

Do While shtData.Cells(lngRow, 1) < ""
With Charts.Add
.Location Whe=xlLocationAsNewSheet
.ChartType = xlXYScatter
strData = "B1:D1,B" & lngRow & ":D" & lngRow
.SetSourceData Source:=shtData.Range(strData), _
PlotBy:=xlRows
.SeriesCollection(1).Name = _
"='" & shtData.Name & "'!R" & lngRow & "C1"
.HasTitle = True
.ChartTitle.Characters.Text = .SeriesCollection(1).Name
.HasLegend = False
.SeriesCollection(1).Trendlines.Add _
Type:=xlLinear, Forward:=0, _
Backward:=0, DisplayEquation:=False, _
DisplayRSquared:=False
End With
lngRow = lngRow + 1
Loop
End Sub

Cheers
Andy

wrote:
Hi there,

I'm trying to create charts, where the y-data for each chart is a
different row, but the x-data is fixed. I'd like to create an x-y
scatter chart and create each chart as a new sheet, labeled with the
item in Col A. (Y data is in cols B-D, X data is in Row 1). Lastly,
I'd like to add a linear trendline to each.

I've been working unsuccessfully with macros. Any help would be much
appreciated.


--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info

XY Multiple rows charting
 

Thanks for that help.

Can anyone also help me to combine everything on 1 graph (but now each
row = 1 series) and help me take the equations generated by the linear
fit and put them into the worksheet, alongside the adjacent row number?

Thanks.

Andy Pope wrote:
Hi,

Here is some code to get you started.

Sub Macro3()
'
Dim lngRow As Long
Dim shtData As Worksheet
Dim strData As String

Set shtData = Worksheets("Sheet1")
lngRow = 2

Do While shtData.Cells(lngRow, 1) < ""
With Charts.Add
.Location Whe=xlLocationAsNewSheet
.ChartType = xlXYScatter
strData = "B1:D1,B" & lngRow & ":D" & lngRow
.SetSourceData Source:=shtData.Range(strData), _
PlotBy:=xlRows
.SeriesCollection(1).Name = _
"='" & shtData.Name & "'!R" & lngRow & "C1"
.HasTitle = True
.ChartTitle.Characters.Text = .SeriesCollection(1).Name
.HasLegend = False
.SeriesCollection(1).Trendlines.Add _
Type:=xlLinear, Forward:=0, _
Backward:=0, DisplayEquation:=False, _
DisplayRSquared:=False
End With
lngRow = lngRow + 1
Loop
End Sub

Cheers
Andy

wrote:
Hi there,

I'm trying to create charts, where the y-data for each chart is a
different row, but the x-data is fixed. I'd like to create an x-y
scatter chart and create each chart as a new sheet, labeled with the
item in Col A. (Y data is in cols B-D, X data is in Row 1). Lastly,
I'd like to add a linear trendline to each.

I've been working unsuccessfully with macros. Any help would be much
appreciated.


--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info

XY Multiple rows charting
 

Ok, this does rows as series in 1 chart

Sub Macro1()
'
'
Dim lngRow As Long
Dim shtData As Worksheet

Set shtData = Worksheets("Sheet2")
lngRow = 2

With Charts.Add
.ChartType = xlXYScatterLines
Do While .SeriesCollection.Count 0
.SeriesCollection(1).Delete
Loop
Do While shtData.Cells(lngRow, 1) < ""
With .SeriesCollection.NewSeries
.XValues = "='" & shtData.Name & "'!R1C2:R1C4"
.Values = "='" & shtData.Name & _
"'!R" & lngRow & "C2:R" & lngRow & "C4"
.Name = "='" & shtData.Name & "'!R" & lngRow & "C1"
End With
lngRow = lngRow + 1
Loop
End With

End Sub

Cheers
Andy

wrote:
Thanks for that help.

Can anyone also help me to combine everything on 1 graph (but now each
row = 1 series) and help me take the equations generated by the linear
fit and put them into the worksheet, alongside the adjacent row number?

Thanks.

Andy Pope wrote:

Hi,

Here is some code to get you started.

Sub Macro3()
'
Dim lngRow As Long
Dim shtData As Worksheet
Dim strData As String

Set shtData = Worksheets("Sheet1")
lngRow = 2

Do While shtData.Cells(lngRow, 1) < ""
With Charts.Add
.Location Whe=xlLocationAsNewSheet
.ChartType = xlXYScatter
strData = "B1:D1,B" & lngRow & ":D" & lngRow
.SetSourceData Source:=shtData.Range(strData), _
PlotBy:=xlRows
.SeriesCollection(1).Name = _
"='" & shtData.Name & "'!R" & lngRow & "C1"
.HasTitle = True
.ChartTitle.Characters.Text = .SeriesCollection(1).Name
.HasLegend = False
.SeriesCollection(1).Trendlines.Add _
Type:=xlLinear, Forward:=0, _
Backward:=0, DisplayEquation:=False, _
DisplayRSquared:=False
End With
lngRow = lngRow + 1
Loop
End Sub

Cheers
Andy

wrote:

Hi there,

I'm trying to create charts, where the y-data for each chart is a
different row, but the x-data is fixed. I'd like to create an x-y
scatter chart and create each chart as a new sheet, labeled with the
item in Col A. (Y data is in cols B-D, X data is in Row 1). Lastly,
I'd like to add a linear trendline to each.

I've been working unsuccessfully with macros. Any help would be much
appreciated.


--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info



--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info