How to plot graph when data lies in every alternate column in excel VBA

Question

How do i plot graphs when the data that i want to plot lies on every alternate column? I tried to use the below code but it gives me two empty graph which im not sure which part of my codes am i missing out or done wrong. If done correctly, it should be graphs something like the one shown in "expected output".

Edited:

The X values are in the 1st column while the y values are 2,4,6,8 etc..

data use for plotting

Expected output

Sub plotgraph()


 Dim i As Long, c As Long
    Dim shp As Shape
    Dim Cht As Chart
    Dim rngDB As Range, rngX As Range
    Dim Srs As Series
    Dim ws As Worksheet

     Set ws = Sheets("Data")

    Set rngDB = ws.UsedRange

    c = rngDB.Columns.Count
 Set shp = ws.Shapes.AddChart
Set Cht = shp.Chart

    With Cht
        For i = 1 To c Step 2 'For every alternate column so in step2
            With ws
                Set rngX = ws.Range(.Cells(2, i), .Cells(2, i).End(xlDown))

            End With

        Set Srs = .SeriesCollection.NewSeries
            With Srs
                .XValues = rngX
            End With
        Next i

     ws.Shapes.AddChart.Select
    Cht.ChartType = xlXYScatter
'    ActiveChart.SetSourceData Source:=Range("Data!$A:$A")
    Cht.Axes(xlValue).Select
    Cht.Axes(xlValue).MinimumScale = 6.45
    Cht.Axes(xlValue).MinimumScale = 5
    Cht.Axes(xlValue).MaximumScale = 6.8
    Cht.Axes(xlValue).MaximumScale = 9

         Cht.Axes(xlValue).TickLabels.NumberFormat = "0.00E+00"
        Cht.Axes(xlCategory, xlPrimary).HasTitle = True
        Cht.Axes(xlValue, xlPrimary).HasTitle = True
End With

End Sub

Issue

@TimWilliams ah sorry about that, i have included the X values in my updated post, the part that is With Srs .XValues = rngX End With — cena
– cena, Commented Mar 23, 2020 at 5:42
@TimWilliams btw is Set shp = ws.Shapes.AddChart necessary tho? Because when i tried record the macro and plot the graph, it doesn't include this.. — cena
– cena, Commented Mar 23, 2020 at 5:44
Still not seeing any x-values. In your "expected" plots the worksheet data = y-values Where are the x-values (201751 etc) ? What are you plotting those mA values against? — Tim Williams
– Tim Williams, Commented Mar 23, 2020 at 5:49

Tim Williams · Accepted Answer · 2020-03-23 06:07:00Z

1

Something like this:

Sub plotgraphs()


    Dim i As Long, c As Long
    Dim shp As Shape
    Dim Cht As Chart, co As Shape
    Dim rngDB As Range, rngX As Range, rngY As Range
    Dim Srs As Series
    Dim ws As Worksheet

    Set ws = Sheets("Data")

    Set rngDB = ws.Range("A1").CurrentRegion

    Set rngX = rngDB.Columns(1)
    Set rngY = rngDB.Columns(2)

    Do While Application.CountA(rngY) > 0

        Set co = ws.Shapes.AddChart
        Set Cht = co.Chart

        With Cht
            .ChartType = xlXYScatter
            'remove any data which might have been
            '  picked up when adding the chart
            Do While .SeriesCollection.Count > 0
                .SeriesCollection(1).Delete
            Loop
            'add the data
            With .SeriesCollection.NewSeries()
                .XValues = rngX.Value
                .Values = rngY.Value
            End With
            'formatting...
            With Cht.Axes(xlValue)
                .MinimumScale = 5
                .MaximumScale = 9
                .TickLabels.NumberFormat = "0.00E+00"
            End With
            Cht.Axes(xlCategory, xlPrimary).HasTitle = True
            Cht.Axes(xlValue, xlPrimary).HasTitle = True
        End With

        Set rngY = rngY.Offset(0, 2) 'next y values
    Loop

End Sub

answered Mar 23, 2020 at 6:07

Tim Williams

169k8 gold badges104 silver badges144 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

cena Over a year ago

for some reasons, the 2nd graph didn't appear. Anyways, does this code works for more than 2 y alternating column data as well

Tim Williams Over a year ago

Works fine for me - the plots will be stacked so you'll need to add code to position them.

cena Over a year ago

i posted the output that i am having currently in my updated post so that you can see whether did i miss out anything..

Tim Williams Over a year ago

Looks like the y-axis max/min settings are hiding your points. Change those as needed.

cena Over a year ago

ah my bad, i went to make my y axis fix just now. Now it works as intended. Thanks alot..

Collectives™ on Stack Overflow

How to plot graph when data lies in every alternate column in excel VBA

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related