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.
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
Sort by date Sort by votes
Re: XY Scatter graph help

Try...

Code: 
[COLOR=darkblue]Sub[/COLOR] CommandButton_Click()

    [COLOR=darkblue]Dim[/COLOR] rSourceData [COLOR=darkblue]As[/COLOR] Range
    
    [COLOR=darkblue]With[/COLOR] Worksheets("Sheet2").UsedRange
        [COLOR=darkblue]Set[/COLOR] rSourceData = .Offset(1, 1).Resize(.Rows.Count - 1, 2)
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]

    [COLOR=darkblue]With[/COLOR] ActiveSheet.ChartObjects.Add(Left:=100, Width:=375, Top:=75, Height:=225)
        .Chart.ChartType = xlXYScatterLines
        .Chart.SetSourceData Source:=rSourceData
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]

[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

Note that I've assumed that you meant Sheet2, not Sheets2. Change the sheet name accordingly.

Hope this helps!
 
Upvote 0
Re: XY Scatter graph help

Many thanks for your help with this Domenic...

Just putting this example into practice and struggling with it a little.

On the data set that I would like to use is to select data starting in column ( "C41" to the unknown end of the data set) and ("I41" to the same range in the column).

the final set will be to include column ("C41..again and the second data set in in column ("J41 to the end).

Is it a matter of adding in....


Code: 
Dim rSourceData As Range
          Dim bSourceData As Range
With Workshets9"Sheet2").UsedRange
Set rSourceData = .Offset(39, 1).Resize(.Rows.count -20, 1)
Set bSourceData = .Offset(3, 5).Resize(.Rows.count - 20, 1)

End With


With ActiveSheet.ChartObjects.Add(Left:=100, Width:=373, Top:=75, Height:=225)
       .chart.ChartType = xlXYScatterLines
       .chart.SetSourceData Source:=rSourceData
       .chart.SetSourceData Source:=bSourceData

End With


I have tried this but the selected range only takes bSourceData and not both....

How can I select data starting in column ( "C41" to the unknown end of the data set) and ("I41" to the same range in the column) and include column ("C41..again and the second data set in in column ("J41 to the end)


Hope that makes sense. Many thanks.
 
Upvote 0
Re: XY Scatter graph help

Try...

Code: 
    [COLOR=darkblue]Dim[/COLOR] rXVals [COLOR=darkblue]As[/COLOR] Range
    [COLOR=darkblue]Dim[/COLOR] rYVals [COLOR=darkblue]As[/COLOR] Range
    [COLOR=darkblue]Dim[/COLOR] LastRow [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
    
    [COLOR=darkblue]With[/COLOR] Worksheets("Sheet2")
        LastRow = .Cells(.Rows.Count, "C").End(xlUp).Row
        [COLOR=darkblue]If[/COLOR] LastRow < 41 [COLOR=darkblue]Then[/COLOR]
            MsgBox "No data found.", vbInformation
            [COLOR=darkblue]Exit[/COLOR] [COLOR=darkblue]Sub[/COLOR]
        [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
        [COLOR=darkblue]Set[/COLOR] rXVals = .Range("C41:C" & LastRow)
        [COLOR=darkblue]Set[/COLOR] rYVals = .Range("J41:J" & LastRow)
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]

    [COLOR=darkblue]With[/COLOR] ActiveSheet.ChartObjects.Add(Left:=100, Width:=375, Top:=75, Height:=225).Chart
        .ChartType = xlXYScatterLines
        [COLOR=darkblue]With[/COLOR] .SeriesCollection.NewSeries
            .Name = "YourSeriesNameHere" [COLOR=green]'optional[/COLOR]
            .XValues = rXVals
            .Values = rYVals
        [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]

Hope this helps!
 
Upvote 0
Re: XY Scatter graph help

Domenic said:
Try...

Code: 
    [COLOR=darkblue]Dim[/COLOR] rXVals [COLOR=darkblue]As[/COLOR] Range
    [COLOR=darkblue]Dim[/COLOR] rYVals [COLOR=darkblue]As[/COLOR] Range
    [COLOR=darkblue]Dim[/COLOR] LastRow [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
    
    [COLOR=darkblue]With[/COLOR] Worksheets("Sheet2")
        LastRow = .Cells(.Rows.Count, "C").End(xlUp).Row
        [COLOR=darkblue]If[/COLOR] LastRow < 41 [COLOR=darkblue]Then[/COLOR]
            MsgBox "No data found.", vbInformation
            [COLOR=darkblue]Exit[/COLOR] [COLOR=darkblue]Sub[/COLOR]
        [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
        [COLOR=darkblue]Set[/COLOR] rXVals = .Range("C41:C" & LastRow)
        [COLOR=darkblue]Set[/COLOR] rYVals = .Range("J41:J" & LastRow)
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]

    [COLOR=darkblue]With[/COLOR] ActiveSheet.ChartObjects.Add(Left:=100, Width:=375, Top:=75, Height:=225).Chart
        .ChartType = xlXYScatterLines
        [COLOR=darkblue]With[/COLOR] .SeriesCollection.NewSeries
            .Name = "YourSeriesNameHere" [COLOR=green]'optional[/COLOR]
            .XValues = rXVals
            .Values = rYVals
        [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]

Hope this helps!
Click to expand...


Many thanks for your help with this...promise this is the last question but if you want to include more series in the same graph can you just add:

Code: 
    [COLOR=darkblue]Dim[/COLOR] rXVals [COLOR=darkblue]As[/COLOR] Range
    [COLOR=darkblue]Dim[/COLOR] rYVals [COLOR=darkblue]As[/COLOR] Range
Dim bYVals As Range
    [COLOR=darkblue]Dim[/COLOR] LastRow [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
    
    [COLOR=darkblue]With[/COLOR] Worksheets("Sheet2")
        LastRow = .Cells(.Rows.Count, "C").End(xlUp).Row
        [COLOR=darkblue]If[/COLOR] LastRow < 41 [COLOR=darkblue]Then[/COLOR]
            MsgBox "No data found.", vbInformation
            [COLOR=darkblue]Exit[/COLOR] [COLOR=darkblue]Sub[/COLOR]
        [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
        [COLOR=darkblue]Set[/COLOR] rXVals = .Range("C41:C" & LastRow)
        [COLOR=darkblue]Set[/COLOR] rYVals = .Range("J41:J" & LastRow)
[COLOR=darkblue]        Set[/COLOR] bYVals = .Range("I41:I" & LastRow)
        [COLOR=darkblue]Set[/COLOR] rXVals = .Range("C41:C" & LastRow)
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]

    [COLOR=darkblue]With[/COLOR] ActiveSheet.ChartObjects.Add(Left:=100, Width:=375, Top:=75, Height:=225).Chart
        .ChartType = xlXYScatterLines
        [COLOR=darkblue]With[/COLOR] .SeriesCollection.NewSeries
            .Name = "YourSeriesNameHere" [COLOR=green]'optional[/COLOR]
            .XValues = rXVals
            .Values = rYVals
        [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]


In other words it picks up ("C") as first column ref and includes ("I") so as to include another set of data in the graph?

Kind regards
 
Upvote 0
Re: XY Scatter graph help

Try...

Code: 
    [COLOR=darkblue]Dim[/COLOR] rXVals [COLOR=darkblue]As[/COLOR] Range
    [COLOR=darkblue]Dim[/COLOR] rYVals [COLOR=darkblue]As[/COLOR] Range
    [COLOR=darkblue]Dim[/COLOR] LastRow [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] i [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
    
    [COLOR=darkblue]With[/COLOR] Worksheets("Sheet2")
        LastRow = .Cells(.Rows.Count, "C").End(xlUp).Row
        [COLOR=darkblue]If[/COLOR] LastRow < 41 [COLOR=darkblue]Then[/COLOR]
            MsgBox "No data found.", vbInformation
            [COLOR=darkblue]Exit[/COLOR] [COLOR=darkblue]Sub[/COLOR]
        [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
        [COLOR=darkblue]Set[/COLOR] rXVals = .Range("C41:C" & LastRow)
        [COLOR=darkblue]Set[/COLOR] rYVals = .Range("I41:J" & LastRow)
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]

    [COLOR=darkblue]With[/COLOR] ActiveSheet.ChartObjects.Add(Left:=100, Width:=375, Top:=75, Height:=225).Chart
        .ChartType = xlXYScatterLines
        [COLOR=darkblue]For[/COLOR] i = 1 [COLOR=darkblue]To[/COLOR] rYVals.Columns.Count
            [COLOR=darkblue]With[/COLOR] .SeriesCollection.NewSeries
                .XValues = rXVals
                .Values = rYVals.Columns(i)
            [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
        [COLOR=darkblue]Next[/COLOR] i
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]

Hope this helps!
 
Upvote 0
Re: XY Scatter graph help

Fantastic, many thanks for your help with this.

Just trying to figure out why there are '<blankseries>' in the graph?

In the legend its showing three other sets of '<blankseries>', would you know how to remove these?

And if i want to include several graphs on the same worksheet but using different datasets from the same sheet would i just need to use the 'Set Add' function and copy and paste what X and Y ranges ( changing the column locations)?

Kind regards</blankseries></blankseries>
 
Last edited:
Upvote 0
Re: XY Scatter graph help

dboone25 said:
Fantastic, many thanks for your help with this.
Click to expand...

You're very welcome!

Just trying to figure out why there are '<blankseries>' in the graph?

In the legend its showing three other sets of '<blankseries>', would you know how to remove these?
Click to expand...

I don't understand. Are those single quotes? Are you getting additional series in your chart?

And if i want to include several graphs on the same worksheet but using different datasets from the same sheet would i just need to use the 'Set Add' function and copy and paste what X and Y ranges ( changing the column locations)? </blankseries></blankseries>
Click to expand...

For each chart, where are the X and Y values located?
 
Upvote 0
Re: XY Scatter graph help

Good morning.

With the graph and the blank series this is what is showing on the graph. Little strange why this is happening.

fV7Ea5


graphtest.jpg
[/URL]
up load pictures
[/IMG]

Series 4 and series 5 is correct and should be showing but not sure why there are 3 <blank series> showing in the legend.



With the multiple graphs the second one is in columns: ("M" and "S") and ("M and "T")

The third is in columns: ("W" and "AC") and ("W and "AD")

The last one is in columns: ("AG" and ""AM") and ("AG and "AN")

Eventually I would like to get the same as above working with 5 different sheets in the same Excel file so when the macro runs after the command button is pressed it will auto populate 4 graphs on one sheet and so on for for the other 5 sheets.


Many thanks again for your help with this. This saves so much time in having to populate every single graph manually!
 
Last edited:
Upvote 0
Re: XY Scatter graph help

The following code will create your 4 charts, and place them side by side in the active sheet. Also, note that it will allow the range to vary for each chart.

Code: 
    [COLOR=darkblue]Dim[/COLOR] rStartCell [COLOR=darkblue]As[/COLOR] Range
    [COLOR=darkblue]Dim[/COLOR] rXVals [COLOR=darkblue]As[/COLOR] Range
    [COLOR=darkblue]Dim[/COLOR] rYVals [COLOR=darkblue]As[/COLOR] Range
    [COLOR=darkblue]Dim[/COLOR] LastRow [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] LeftPos [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] i [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] j [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
    
    [COLOR=darkblue]Const[/COLOR] GAP [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Integer[/COLOR] = 30 [COLOR=green]'gap between charts[/COLOR]
    
    LeftPos = 100 [COLOR=green]'starting left position of first chart[/COLOR]
    
    [COLOR=darkblue]With[/COLOR] Worksheets("Sheet2")
        [COLOR=darkblue]Set[/COLOR] rStartCell = .Range("C41")
        [COLOR=darkblue]For[/COLOR] i = 1 [COLOR=darkblue]To[/COLOR] 4
            LastRow = .Cells(.Rows.Count, rStartCell.Column).End(xlUp).Row
            [COLOR=darkblue]If[/COLOR] LastRow >= 41 [COLOR=darkblue]Then[/COLOR]
                [COLOR=darkblue]Set[/COLOR] rXVals = Range(rStartCell, .Cells(LastRow, rStartCell.Column))
                [COLOR=darkblue]Set[/COLOR] rYVals = rXVals.Offset(, 6).Resize(, 2)
                [COLOR=darkblue]With[/COLOR] ActiveSheet.ChartObjects.Add(Left:=LeftPos, Width:=375, Top:=75, Height:=225).Chart
                    .ChartType = xlXYScatterLines
                    [COLOR=darkblue]Do[/COLOR] [COLOR=darkblue]While[/COLOR] .SeriesCollection.Count > 0
                        .SeriesCollection(1).Delete
                    [COLOR=darkblue]Loop[/COLOR]
                    [COLOR=darkblue]For[/COLOR] j = 1 [COLOR=darkblue]To[/COLOR] rYVals.Columns.Count
                        [COLOR=darkblue]With[/COLOR] .SeriesCollection.NewSeries
                            .XValues = rXVals
                            .Values = rYVals.Columns(j)
                        [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
                    [COLOR=darkblue]Next[/COLOR] j
                    LeftPos = LeftPos + .Parent.Width + GAP
                [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
            [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
            [COLOR=darkblue]Set[/COLOR] rStartCell = rStartCell.Offset(, 10)
        [COLOR=darkblue]Next[/COLOR] i
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]

Hope this helps!
 
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