Creating a ScatterLines chart using VBA coding I need to create a scatter lines

Question

Creating a ScatterLines chart using VBA coding

I need to create a scatter lines chart using two columns in sheet2 of my activeworkbook. These columns are E and F but I am having trouble getting the chart to actually create. The code I currently have is below and the parts in bold are the bits I am not 100% sure about, whether they are correct or not.

column E is for the XVales

column F is for the YValues

Particularly unsure about the C2 part in the sourcedata lines. What does that part of the ranges actually mean? I feel a bit of an idiot as this should be pretty easy.

Sub deviationcharts(increment As Integer, Company As String, StartDate As String, TankNum As Integer, Sheetname As String, height As Integer)

Range("E1:E" & increment).Select
Charts.Add
ActiveChart.ChartType = xlXYScatterLines
ActiveChart.SetSourceData Source:=Sheets(Sheetname).Range("E1:E" & increment), _
PlotBy:=xlColumns
ActiveChart.SeriesCollection(1).XValues = "=" & Sheetname & "!E1C2:E" & (increment) & _
"C2"
ActiveChart.Location Where:=xlLocationAsObject, Name:=ActiveWorkbook.Sheet2
With ActiveChart
.HasTitle = False
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Angle (degrees)"
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Font.Size = 8
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Deviation (mm)"
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Font.Size = 8
End With
With ActiveChart
.HasAxis(xlCategory, xlPrimary) = True
.HasAxis(xlValue, xlPrimary) = True
End With
ActiveChart.Axes(xlCategory, xlPrimary).CategoryType = xlAutomatic
With ActiveChart.Axes(xlCategory)
.HasMajorGridlines = True
.HasMinorGridlines = True
End With
With ActiveChart.Axes(xlValue)
.HasMajorGridlines = True
.HasMinorGridlines = True
End With
ActiveChart.HasLegend = False

*** Rest of chart settings, already set as I want

End Sub

Explanation / Answer

^You have not pasted complete sub-routine. Paste the entire Sub to help us understand../

By now, I can tell you that increment is a number variable and should have some value so that Range becomes complete. I am pasting a sample code for your reference, you can use it if you like.,.

Sub Create_Chart()

Dim CO As ChartObject
Dim Chart_Rng As Range

'Use your worksheet name and exact chart range in the below line
Set Chart_Rng = ThisWorkbook.Worksheets("Sheet1").Range("E1:F10")

'Left, Width.. are in pixels, you can change it to position your chart in your worksheet
Set CO = WS.ChartObjects.Add(Left:=5, Width:=375, Top:=75, Height:=225)

With CO.Chart
    .ChartType = xlXYScatter
    .SetSourceData Source:=Chart_Rng
    .HasLegend = False
    .HasTitle = True
    .ChartTitle.Caption = "Automatically Created Bubble"
    .Axes(xlValue).HasMajorGridlines = True
    .Axes(xlValue).HasMinorGridlines = True

End With
End Sub

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.