XY Scatter graph

[dboone25]

Scatter graph learning curve.

Hello all. I am trying a simple method of creating a scatter graph using the code below. This is using the macro recording but eventually I would like to apply this to capture data on multiple sheets and creating multiple graphs on the same page, but for now this is to get a better understanding.

I would like to create a scatter graph where the range in each column can either finish at lets say A30 or A45 as the data set can change...how would I capture the 'unknown range in each column...e.g..

Data	X	Y
11	15	132
154	158	4
1254	458	8883
45	8	4863
454	45848	453
4882	45	448
8564	75	458
5888	15	487
878	49	69
689	987	65

<tbody>
</tbody>

The code I have below:

Sub CommandButton_Click()

With ActiveSheet.ChartObjects.Add(Left:=100,Width:=375,Top:=75, Height:=225)
.chart.SetSourceData Source:=Sheets("Sheets2").range("A2:A10").range("B2:B10")
.chart.ChartType = xlXYScatterLines
End With

End Sub

Not sure if that makes sense but the code above does not work.

Basically I would like something dynamic as the range of data will be unknown. The range of data will have to be ( Data/X) and ( Data /Y )..

Am I going in the right direction? Please may someone help.

 

[dboone25]

Re: XY Scatter graph help

For instance...The data in columns in B, C and D for example have a range of text data like:

Column B	Column C	Column D	Column E
SD334	Machinereadingcells	Lock1	86.38735199 <tbody> </tbody>
SH335	Machinereading cells	Lock2	128.872818 <tbody> </tbody>

<tbody>
</tbody>

Everytime the data is loaded into the sheet it moves all the columns due to the different size of the text/values in the columns.

If you select Formate chart Area> Properties>Object positioning there is a section where you can lock the graphs so if the columns move due to data length the graphs will be locked into place.

Is there a way to add this in?

 

[Domenic]

Re: XY Scatter graph help

You can add the following line in red...

With .ChartObjects.Add(Left:=rChartStartCell.Left, Width:=rChartStartCell.Resize(, 7).Width, Top:=rChartStartCell.Top, Height:=rChartStartCell.Resize(38).Height).Chart
    [COLOR=#ff0000].Parent.Placement = xlFreeFloating[/COLOR]
    .ChartType = xlXYScatterLines
    'etc
    '
    '
End With

 

[dboone25]

Re: XY Scatter graph help

Thanks Domenic, unfortunately this doesnt work..

[/URL]
host gifs
[/IMG]

[/URL]
easy picture upload
[/IMG]


I have had to blank out the data set below the graphs but just to show this does not line up the graphs into a 'locked' position.

I have seperated each graph with individual code to see if that works but alas this doesnt..

'GRAPH 1 **********  
    Dim rChartStartCell As range
    Dim rDataStartCell As range
    Dim rXVals As range
    Dim rYVals As range
    Dim LastRow As Long
    Dim i As Long
    Dim j As Long
    
    
    Dim WidthPos1 As Long
    WidthPos1 = 620
    
    
    
    With Worksheets("Sheet2")
        Set rChartStartCell = .range("B2")
        Set rDataStartCell = .range("C41")
        For i = 1 To 1
            LastRow = .Cells(.Rows.count, rDataStartCell.Column).End(xlUp).Row
            If LastRow >= 41 Then
                Set rXVals = range(rDataStartCell, .Cells(LastRow, rDataStartCell.Column))
                Set rYVals = rXVals.Offset(, 6).Resize(, 2)
                With .ChartObjects.Add(Left:=rChartStartCell.Left, Width:=WidthPos1, Top:=rChartStartCell.Top, Height:=rChartStartCell.Resize(26).Height).chart
                    .Parent.Placement = xlFreeFloating
                    .ChartType = xlXYScatter
                    Do While .SeriesCollection.count > 0
                        .SeriesCollection(1).Delete
                    Loop
                    For j = 1 To rYVals.Columns.count
                        With .SeriesCollection.NewSeries
                            If j = 1 Then
                                .Name = "=" & rDataStartCell.Offset(, 2).Address(, , , True)
                            Else
                                .Name = "=" & rDataStartCell.Offset(-1, 7).Address(, , , True)
                            End If
                            .XValues = rXVals
                            .Values = rYVals.Columns(j)
                                                        
                            '.ChartObjects.Placement = xlMove
                            'Change Marker Style For CI and Backstop
                            Select Case j
                            Case 1
                            .MarkerStyle = xlMarkerStyleStar
                            .MarkerSize = 5
                            Case Else
                            .MarkerSize = 6
                            .MarkerBackgroundColor = RGB(200, 0, 0)
                            .MarkerStyle = xlMarkerStyleDot
                            With .Format.Line
                            .Style = msoLineSingle
                            .BackColor.RGB = RGB(200, 0, 0)
                            .ForeColor.RGB = RGB(200, 0, 0)
                            End With
                             End Select
                                                                    
                                                                        '.Axes(xlXYScatter).TickLabels.Orientation = xlDownward
                        '.PlotArea.Width = ch.ChartArea.Width / 2
                        '.PlotArea.Height = ch.ChartArea.Height / 2
                        '.Axes(xlCategory).AxisTitle.Orientation = 90
                        
                        End With
                    Next j
                End With
            End If
            Set rChartStartCell = rChartStartCell.Offset(, 8)
            Set rDataStartCell = rDataStartCell.Offset(, 10)
                                               
'            With Worksheets("Sheet2").ChartObjects.chart
'        With .Axes(xlCategory)
'            .TickLabels.Orientation = -90
'        End With
    'End With
            
        Next i
    End With
  
  
  
  


  'Graph2
  
  Dim WidthPos2 As Long
  WidthPos2 = 620


  With Worksheets("Sheet2")
        Set rChartStartCell = .range("L2")
        Set rDataStartCell = .range("M41")
        For i = 1 To 1
            LastRow = .Cells(.Rows.count, rDataStartCell.Column).End(xlUp).Row
            If LastRow >= 41 Then
                Set rXVals = range(rDataStartCell, .Cells(LastRow, rDataStartCell.Column))
                Set rYVals = rXVals.Offset(, 6).Resize(, 2)
                With .ChartObjects.Add(Left:=rChartStartCell.Left, Width:=WidthPos2, Top:=rChartStartCell.Top, Height:=rChartStartCell.Resize(26).Height).chart
                    .Parent.Placement = xlFreeFloating
                    .ChartType = xlXYScatter
                    Do While .SeriesCollection.count > 0
                        .SeriesCollection(1).Delete
                    Loop
                    For j = 1 To rYVals.Columns.count
                        With .SeriesCollection.NewSeries
                            If j = 1 Then
                                .Name = "=" & rDataStartCell.Offset(, 2).Address(, , , True)
                            Else
                                .Name = "=" & rDataStartCell.Offset(-1, 7).Address(, , , True)
                            End If
                            .XValues = rXVals
                            .Values = rYVals.Columns(j)


                            'Change Marker Style For CI and Backstop
                            Select Case j
                            Case 1
                            .MarkerStyle = xlMarkerStyleStar
                            .MarkerSize = 5
                            Case Else
                            .MarkerSize = 6
                            .MarkerBackgroundColor = RGB(200, 0, 0)
                            .MarkerStyle = xlMarkerStyleDot
                            With .Format.Line
                            .Style = msoLineSingle
                            .BackColor.RGB = RGB(200, 0, 0)
                            .ForeColor.RGB = RGB(200, 0, 0)
                            End With
                             End Select


                        End With
                    Next j
                End With
            End If
            Set rChartStartCell = rChartStartCell.Offset(, 10)
            Set rDataStartCell = rDataStartCell.Offset(, 10)
        Next i
    End With




  'Graph 3
  Dim LeftPos3 As Long
  Dim WidthPos3 As Long


  LeftPos3 = 1608
  WidthPos3 = 620


  With Worksheets("Sheet2")
        Set rChartStartCell = .range("V2")
        Set rDataStartCell = .range("W41")
        For i = 1 To 1
            LastRow = .Cells(.Rows.count, rDataStartCell.Column).End(xlUp).Row
            If LastRow >= 41 Then
                Set rXVals = range(rDataStartCell, .Cells(LastRow, rDataStartCell.Column))
                Set rYVals = rXVals.Offset(, 6).Resize(, 2)
                With .ChartObjects.Add(Left:=LeftPos3, Width:=WidthPos3, Top:=rChartStartCell.Top, Height:=rChartStartCell.Resize(26).Height).chart
                    .Parent.Placement = xlFreeFloating
                    .ChartType = xlXYScatter
                    Do While .SeriesCollection.count > 0
                        .SeriesCollection(1).Delete
                    Loop
                    For j = 1 To rYVals.Columns.count
                        With .SeriesCollection.NewSeries
                            If j = 1 Then
                                .Name = "=" & rDataStartCell.Offset(, 2).Address(, , , True)
                            Else
                                .Name = "=" & rDataStartCell.Offset(-1, 7).Address(, , , True)
                            End If
                            .XValues = rXVals
                            .Values = rYVals.Columns(j)
                            
                            'Change Marker Style For CI and Backstop
                            Select Case j
                            Case 1
                            .MarkerStyle = xlMarkerStyleStar
                            .MarkerSize = 5
                            Case Else
                            .MarkerSize = 6
                            .MarkerBackgroundColor = RGB(200, 0, 0)
                            .MarkerStyle = xlMarkerStyleDot
                            With .Format.Line
                            .Style = msoLineSingle
                            .BackColor.RGB = RGB(200, 0, 0)
                            .ForeColor.RGB = RGB(200, 0, 0)
                            End With
                             End Select
                            
                        End With
                    Next j
                End With
            End If
            Set rChartStartCell = rChartStartCell.Offset(, 10)
            Set rDataStartCell = rDataStartCell.Offset(, 10)
        Next i
    End With


  'Graph 4
  Dim LeftPos4 As Long
  Dim WidthPos4 As Long


  LeftPos4 = 2470
  WidthPos4 = 620


  With Worksheets("Sheet2")
        Set rChartStartCell = .range("AF2")
        Set rDataStartCell = .range("AG41")
        For i = 1 To 1
            LastRow = .Cells(.Rows.count, rDataStartCell.Column).End(xlUp).Row
            If LastRow >= 41 Then
                Set rXVals = range(rDataStartCell, .Cells(LastRow, rDataStartCell.Column))
                Set rYVals = rXVals.Offset(, 6).Resize(, 2)
                With .ChartObjects.Add(Left:=LeftPos4, Width:=WidthPos4, Top:=rChartStartCell.Top, Height:=rChartStartCell.Resize(26).Height).chart
                    .Parent.Placement = xlFreeFloating
                    .ChartType = xlXYScatter
                    Do While .SeriesCollection.count > 0
                        .SeriesCollection(1).Delete
                    Loop
                    For j = 1 To rYVals.Columns.count
                        With .SeriesCollection.NewSeries
                            If j = 1 Then
                                .Name = "=" & rDataStartCell.Offset(, 2).Address(, , , True)
                            Else
                                .Name = "=" & rDataStartCell.Offset(-1, 7).Address(, , , True)
                            End If
                            .XValues = rXVals
                            .Values = rYVals.Columns(j)
                            
                            
                            'Change Marker Style For CI and Backstop
                            Select Case j
                            Case 1
                            .MarkerStyle = xlMarkerStyleStar
                            .MarkerSize = 5
                            Case Else
                            .MarkerSize = 6
                            .MarkerBackgroundColor = RGB(200, 0, 0)
                            .MarkerStyle = xlMarkerStyleDot
                            With .Format.Line
                            .Style = msoLineSingle
                            .BackColor.RGB = RGB(200, 0, 0)
                            .ForeColor.RGB = RGB(200, 0, 0)
                            End With
                             End Select
                            
                            
                        End With
                    Next j
                End With
            End If
            Set rChartStartCell = rChartStartCell.Offset(, 10)
            Set rDataStartCell = rDataStartCell.Offset(, 10)
        Next i
    End With


Ive tried the [CODE].Parent.Placement = xlFreeFloating

Even using the offset changing the value....

 Set rChartStartCell = rChartStartCell.Offset(, 10)

This is a bit of a strange one :-/



[/CODE]

 

[Domenic]

Re: XY Scatter graph help

I missed it earlier, but I just noticed that you've changed the code completely. You're repeating the code four times, one for each chart. Why?

In your earlier post, you said the following...

Graph 1 Size:- (B2 to Wide H2) and (B39 to H39 Long)

Graph 2 Size:- (L2 to Wide R2) and (L39 to R39 Long)

Graph 3 Size:- (V2 to Wide AB2) and (V39 to AB39 Long)

Graph 4 Size:- (AF2 to Wide AL2) and (AF39 to AL39 Long)

Therefore, I amended the code, and so the charts should have been positioned accordingly. Isn't this what you want? If not, can you please clarify what it is exactly that you want?

 

[dboone25]

Re: XY Scatter graph help

You are absolutely right, my fault to blame! I have bben keeping the same code which I have commented:

This is the last code you sent me:

Dim rChartStartCell As range    Dim rDataStartCell As range
    Dim rXVals As range
    Dim rYVals As range
    Dim LastRow As Long
    Dim i As Long
    Dim j As Long
    
    With Worksheets("Sheet2")
        Set rChartStartCell = .range("B2")
        Set rDataStartCell = .range("C41")
        For i = 1 To 4
            LastRow = .Cells(.Rows.count, rDataStartCell.Column).End(xlUp).Row
            If LastRow >= 41 Then
                Set rXVals = range(rDataStartCell, .Cells(LastRow, rDataStartCell.Column))
                Set rYVals = rXVals.Offset(, 6).Resize(, 2)
                With .ChartObjects.Add(Left:=rChartStartCell.Left, Width:=rChartStartCell.Resize(, 7).Width, Top:=rChartStartCell.Top, Height:=rChartStartCell.Resize(38).Height).chart
                    .ChartType = xlXYScatter
                    Do While .SeriesCollection.count > 0
                        .SeriesCollection(1).Delete
                        .Parent.Placement = xlFreeFloating
                    Loop
                    For j = 1 To rYVals.Columns.count
                        With .SeriesCollection.NewSeries
                            If j = 1 Then
                                .Name = "=" & rDataStartCell.Offset(, 2).Address(, , , True)
                            Else
                                .Name = "=" & rDataStartCell.Offset(-1, 7).Address(, , , True)
                            End If
                            .XValues = rXVals
                            .Values = rYVals.Columns(j)
                            
                            'Change Marker Style For CI and Backstop
                            Select Case j
                            Case 1
                            .MarkerStyle = xlMarkerStyleStar
                            .MarkerSize = 5
                            Case Else
                            .MarkerSize = 6
                            .MarkerBackgroundColor = RGB(200, 0, 0)
                            .MarkerStyle = xlMarkerStyleDot
                            With .Format.Line
                            .Style = msoLineSingle
                            .BackColor.RGB = RGB(200, 0, 0)
                            .ForeColor.RGB = RGB(200, 0, 0)
                            End With
                             End Select
                            
                        End With
                    Next j
                End With
            End If
            Set rChartStartCell = rChartStartCell.Offset(, 10)
            Set rDataStartCell = rDataStartCell.Offset(, 10)
        Next i
    End With

The only thing I have added in that code above is the marker size and changed xlScatterLines to xlScatter.

The current size of all four graphs is perfect!..This I can tweek slightly using:

With .ChartObjects.Add(Left:=rChartStartCell.Left, Width:=rChartStartCell.Resize(, 7).Width, Top:=rChartStartCell.Top, Height:=rChartStartCell.Resize(38).Height).chart

The only thing i would like is for the graphs to be lined up so when the size of the columns change due to data size the graphs will still start exactly at:

Graph 1 Start: ( B2)

Graph 2 Start: ( L2)

Graph 3 Start: ( V2)

Graph 4 Start: ( AF2)


My sincere apologies!...I have been rushing this and need to slow down!

Really appreciate your help. Thank you.

 

[Domenic]

Re: XY Scatter graph help

The size of the individual columns shouldn't matter. Each chart will span the total width of their respective columns. So, for example, since the starting cell is B2, and you're resizing the starting cell to 7 columns, the chart should span Columns B through H, regardless of individual column widths. And, if the width of any individual column is adjusted, the chart should also adjust accordingly. Isn't this what you want?

 

[dboone25]

Re: XY Scatter graph help

Yes, that is exactly what is needed but this does not happen. Currently, when I run the code as it stands the start of each graph:

Graph 1 starts at (B2) - Correct

Graph 2 starts at ( mid point of P)

Graph 3 starts at (AB)

Graph 4 starts at (AP)

The only graph that seems to line up is the first one.

This is excactly what I would like with the graphs.

[/URL]
multiple image uploader
[/IMG]

[/URL][/IMG]

 

[Domenic]

Re: XY Scatter graph help

From the looks of the images you posted, you've made a number of changes to the original code that I offered you, which has affected the outcome. I'm not sure why you did so, since it originally positioned the charts exactly as you've described.

 

[dboone25]

Re: XY Scatter graph help

The only changes I made were adding in the marker styles and changing the graph to xlScatter. This was taken from the last original code you sent me...thanks for your help with this. i will try get the graphs positioned.

Kind regards