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

Show Comments

One Response

  1. Rajesh Sinha

Leave a Reply