Learn to Plot A “XYScatter Chart” on Fly : VBA Macro Code

Learn to Plot A "XYScatter Chart" on Fly : VBA Macro Code

Learn to Plot A “XYScatter Chart” on Fly : VBA Macro Code

VBA Macro Code to Plot A “XYScatter Chart” on Fly

Sub chartData() 
    On Error Goto ErrHandler 
    Dim xRow As Long 
    Dim wsName As String 
    Dim ws As Worksheet 
    Set ws = ActiveSheet 
    wsName = ws.Name 
    xRow = Cells(Rows.Count, "A").End(xlUp).Row 
    ActiveSheet.Shapes.AddChart.Select 
    ActiveChart.ChartType = xlXYScatterSmooth 
    ActiveChart.SeriesCollection.NewSeries 
    ActiveChart.SeriesCollection(1).Name = "='" & wsName & "'!$A$17" 
    ActiveChart.SeriesCollection(1).XValues = "='" & wsName & "'!$A$20:$A$" & xRow 
    ActiveChart.SeriesCollection(1).Values = "='" & wsName & "'!$B$20:$B$" & xRow 
    With ActiveChart.Parent 
        .Height = 325 ' resize
        .Width = 700 ' resize
        .Top = 200 ' reposition
        .Left = 400 ' reposition
    End With 
     
    Exit Sub 
ErrHandler: 
    MsgBox "The Error Number is - " & Err.Number & ": " & Err.Description 
    MsgBox "Unable to continue" 
End Sub

This will create a scatter graph and will adjust for how many row there are as long as the data starts at row 20. To add this as a macro hit Alt F11 and this will open the VBA editor. On the Insert menu select Module. Paste the code into the module and then close the VBA editor. To run the macro just select Macros and run the macro called createGraph. It will create the scatter graph and you can resize to your liking by changing the areas I have marked as height/width/position

Add Multiple Series

With ActiveChart 
    strName = "AddChartSeries" 
        With .SeriesCollection.NewSeries 
            .XValues = Sheets(strName).Range("E3:E" & arrLength(a)) 
           .Values = Sheets(strName).Range("G3:G" & arrLength(a)) 
           .Name = strName 
        End With 
End With

If you need any more help just ask

Courtesy: Smuzoen

1

One Response

Show all responses
  1. Rajesh Sinha
    November 6, 2016

Write a response