Dynamically select cells and input in chart

Question

I would like to create a macro that run through a series of data in a table and is able to automatically graph it. The thing is, the data could have more or less points, but the code still needs to be able to select and graph this required data.

Here is what I'm working with (below). I would like to be able to graph the vertical coordinate vs. the vertical displacement for each of the point numbers (under column Point No.) on the same graph. As you can see there are four different point numbers (1,2,3,4) and each point number is iterated 9 times. However, these numbers can change (for example there could be 8 Point numbers with three iterations each) and the code just needs to be able to select the data based on the point number value itself.

Here is an example of the graph that I wish to produce with this code:

Since I'm very new to VBA, I still don't have a full grasp for the syntax but here was my thinking (some NOT in VBA language):

    Sub CreateChart()
Dim x as Range
    Range("C8").Select
        Range(Selection, Selection.End(xlDown)).Select 'selects whole column which will always start from cell C8  

For each x in selection 

'Select the columns of *Vertical Coordinate* and *Vertical Displacement* corresponding to Point No. 1
'Graph the relationship as a new series in a scatterplot 

x = x+1

Next x

End Sub

I understand that this is completely incorrect syntax but again I only have very limited command of the language at this point. Any help is appreciated! Thx.

------------------------------!!!!!!!!!!!! EDIT !!!!!!!!!!!!!!!-----------------------

I have received a great response from @Viktor for the scenario in the original case, but I was wondering if there was any way to modify the code for a slightly more challenging one (and one which is way above my head):

I have added a few more columns to my table (see below) and would like the code to create an additional chart that plots *Vertical Coordinate vs. Vertical Stress", while still keeping the chart from Vertical Coordinate vs. Vertical Displacement. The reason why the current code doesn't satisfy this is because it assumes that there is no other data on the sheet where the table is (but there is). I want to be able to add more columns and create more charts (all of them plotted against vertical coordinate) without affecting the other charts. Please if there is any way to modify the code then that would be much appreciated! Thx.

.com/GYsZo.png

Answer 1

Actually I think the task is easier to complete using Formula + Named Ranges, but it was a challenge an a learning possibility to write the code. I hope it will work well for you. I also tried to comment it for better understanding.

Sub MakeXYGraph()
    'https://stackoverflow.com/questions/62285791/dynamically-select-cells-and-input-in-chart
    Dim ws As Worksheet
    Set ws = Sheet1 'This is the codename of the sheet where the data is
    'For the test, deleting all the previous charts
    Dim vChartObject As ChartObject
    For Each vChartObject In ws.ChartObjects
        vChartObject.Delete
    Next vChartObject
    'rngData is the range where the data are. It is assumed that nothing else is on the sheet than what you displ
    Dim rngData As Range
    Set rngData = ws.UsedRange.Offset(1).Resize(ws.UsedRange.Rows.Count - 1)
    ' Get the number of series
    Dim iMaxSeries As Integer
    iMaxSeries = Application.WorksheetFunction.Max(rngData.Columns(1))
    ' Is the actual Series, but in the sheet it called Point
    Dim iPoint As Integer
    'Used for setting the ranges for the series data
    Dim lFirstRow As Long, lLastRow As Long, lFirstColumn As Long, lLastColumn As Long
    lFirstColumn = rngData(1).Column
    lLastColumn = rngData.Columns(rngData.Columns.Count).Column
    'Creating the Chart
    Dim cht As ChartObject
    Set cht = ws.ChartObjects.Add(Left:=250, Width:=500, Top:=50, Height:=300)
    With cht.Chart
        .ChartType = xlXYScatterLines
        'X axis name
        .Axes(xlCategory, xlPrimary).HasTitle = True
        .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Vertical Displacement"
        'Y-axis name
        .Axes(xlValue, xlPrimary).HasTitle = True
        .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Vertical Coordinate"
        ' deleting the unwanted series (Excel tries to find out the data, but no need for it.)
        Do Until .SeriesCollection.Count = 0
            .SeriesCollection(1).Delete
        Loop
    End With
    For iPoint = 1 To iMaxSeries
        'Search for the first occurence of the point
        lFirstRow = rngData.Columns(1).Offset(-1).Find(what:=iPoint).Row
        'Search for the first occurence of the second point -1 is the last of this point
        If iPoint = iMaxSeries Then
            lLastRow = rngData.Rows(rngData.Rows.Count).Row - 1
        Else
            lLastRow = rngData.Columns(1).Find(what:=iPoint + 1).Row - 1
        End If
        'Add the series
        With cht.Chart.SeriesCollection.NewSeries
            .XValues = ws.Range(Cells(lFirstRow, lFirstColumn + 1), Cells(lLastRow, lLastColumn - 1))
            .Values = ws.Range(Cells(lFirstRow, lFirstColumn + 2), Cells(lLastRow, lLastColumn))
            .Name = "Point " & CStr(iPoint)
        End With
    Next iPoint
End Sub