XY Scatter graph

D

dboone25

Board Regular
Joined
May 8, 2015
Messages
185
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..


[TABLE="width: 500"]
<tbody>[TR]
[TD]Data[/TD]
[TD]X[/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]15[/TD]
[TD]132[/TD]
[/TR]
[TR]
[TD]154[/TD]
[TD]158[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]1254[/TD]
[TD]458[/TD]
[TD]8883[/TD]
[/TR]
[TR]
[TD]45[/TD]
[TD]8[/TD]
[TD]4863[/TD]
[/TR]
[TR]
[TD]454[/TD]
[TD]45848[/TD]
[TD]453[/TD]
[/TR]
[TR]
[TD]4882[/TD]
[TD]45[/TD]
[TD]448[/TD]
[/TR]
[TR]
[TD]8564[/TD]
[TD]75[/TD]
[TD]458[/TD]
[/TR]
[TR]
[TD]5888[/TD]
[TD]15[/TD]
[TD]487[/TD]
[/TR]
[TR]
[TD]878[/TD]
[TD]49[/TD]
[TD]69[/TD]
[/TR]
[TR]
[TD]689[/TD]
[TD]987[/TD]
[TD]65[/TD]
[/TR]
</tbody>[/TABLE]


The code I have below:

HTML: 
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.
 
Sort by date Sort by votes
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:



[TABLE="width: 500"]
<tbody>[TR]
[TD]Column B[/TD]
[TD]Column C[/TD]
[TD]Column D[/TD]
[TD]Column E[/TD]
[/TR]
[TR]
[TD]SD334[/TD]
[TD]Machinereadingcells[/TD]
[TD]Lock1[/TD]
[TD][TABLE="width: 97"]
<tbody>[TR]
[TD="width: 97, align: right"]86.38735199[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]SH335[/TD]
[TD]Machinereading cells[/TD]
[TD]Lock2[/TD]
[TD][TABLE="width: 97"]
<tbody>[TR]
[TD="width: 97, align: right"]128.872818[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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?
 
Upvote 0

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
Re: XY Scatter graph help

You can add the following line in red...

Code: 
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
 
Upvote 0
Re: XY Scatter graph help

Thanks Domenic, unfortunately this doesnt work..

url
[/URL]
host gifs
[/IMG]


url
[/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..


Code: 
'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....
Code: 
 Set rChartStartCell = rChartStartCell.Offset(, 10)

This is a bit of a strange one :-/



[/CODE]
 
Upvote 0
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)
Click to expand...

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?
 
Upvote 0
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:

Code: 
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:

Code: 
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.
 
Upvote 0
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?
 
Upvote 0
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
[/URL]
multiple image uploader
[/IMG]


url
[/URL][/IMG]
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question Question
Scatter plot based on selection
Replies
1
Views
682
Jon Peltier
J
J
  • Question Question
Scatter Plot Graph - Issue with x-axis Label
Replies
1
Views
791
RoryA
RoryA
G
  • Solved
Loop to create charts automatically
Replies
3
Views
1K
Jon Peltier
J
J
  • Question Question
Need some help with permutation code, convert from one cell to a range
Replies
3
Views
435
johnnyL
J
J
  • Question Question
Excel macro for Remove comma's and spaces from a cell and place the data in different cell
Replies
3
Views
1K
JEC
JEC

Forum statistics

Threads
1,224,524
Messages
6,179,308
Members
452,904
Latest member
CodeMasterX

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top