Automatic Data Range Recognition When Plotting Graph Excel VBA

Question

I have a VBA code that generates two colums of data. This analysis repeats to generate more data in the same columns but for different conditions and places this data below the first iteration's output. How do I get the code to generate a XY Scatter with smooth lines next to each block of data generated? My Attempt is below however I run into three problems: 1) It only generates one graph before the debug stops the macro, 2) It generates a barchart and not a scatter 3) How do I get each graph that is generated to be scaled placed next to the data it is drawing from such that I get a column of graphs?

    Sub TestExample()

   Dim NoIteration As Integer
   Dim Iteration As Integer
    NoIteration = Range("N26").Value

    Dim YieldIteration As Integer
    Dim NoOfJumps As Long
    Dim minyield As Long
    Dim maxyield As Long
    Dim jump As Long
        NoOfJumps = Range("Q24").Value
        minyield = Range("Q25").Value
        maxyield = Range("Q26").Value
        jump = Range("Q27").Value

    Dim xaxis As Range
    Dim yaxis As Range
        Dim c As Chart
       Dim Sh As String
        Sh = ActiveSheet.Name


    Range("M29:T1000").Select
    Selection.Clear

    For jump = 0 To NoOfJumps


        For Iteration = 0 To NoIteration
                    'Print Intervals
                    Range("M30").Offset(NoIteration + Iteration + 4, 0).Value = Range("V19").Value * Iteration

                    'Solve weights for minimum Spot SD for each given interval
                    SolverReset
                    SolverOk SetCell:="$T$18", MaxMinVal:=2, ValueOf:="0", ByChange:="$O$20:$R$20"
                    SolverAdd CellRef:="$T$17", Relation:=2, FormulaText:=Range("M30").Offset(NoIteration + Iteration + 4, 0).Value
                    SolverAdd CellRef:="$T$20", Relation:=2, FormulaText:=1
                    SolverAdd CellRef:="$T$7", Relation:=1, FormulaText:=minyield + jump 'State min required yield
                    SolverAdd CellRef:="$T$7", Relation:=3, FormulaText:=maxyield + jump 'State max required yield
                    SolverAdd CellRef:="$O$20:$R$20", Relation:=3, FormulaText:="0"
                    SolverSolve UserFinish:=True
                    SolverFinish KeepFinal:=1

                    'Print Income Return, SD
                    Range("N30").Offset(Iteration + jump * 50, 0).Value = Range("T7").Value
                    Range("O30").Offset(Iteration + jump * 50, 0).Value = Range("T8").Value

                    'Print Spot Return, SD
                    Range("N30").Offset(NoIteration + Iteration + 4 + jump * 50, 0).Value = Range("T17").Value
                    Range("O30").Offset(NoIteration + Iteration + 4 + jump * 50, 0).Value = Range("T18").Value


                    'Print Total Return, SD
                    Range("N30").Offset(NoIteration * 2 + Iteration + 8 + jump * 50, 0).Value = Range("AC17").Value
                    Range("O30").Offset(NoIteration * 2 + Iteration + 8 + jump * 50, 0).Value = Range("AC18").Value


        Next Iteration

        Set yaxis = Range(Range("N30").Offset(Iteration + jump * 50, 0), Range("N30").End(xlDown))
        Set xaxis = Range(Range("O30").Offset(Iteration + jump * 50, 0), Range("$O30").End(xlDown))


Set c = ActiveWorkbook.Charts.Add
ActiveChart.Location Where:=xlLocationAsObject, Name:=Sh
'Set c = c.Location(Where:=xlLocationAsObject, Name:="Sheet1")
With c
    .ChartType = xlXYScatterLines
    ' set other chart properties
End With

Dim s As Series
Set s = c.SeriesCollection.NewSeries
With s
    .Values = yaxis
    .XValues = xaxis

End With


    Next jump
End Sub

Answer 1

This code looks up the code in the columns and plots the graph in the specified range. It offsets the lookup and plotting as the first code goes through the loop.

Dim srs As Series

Set cht = ActiveChart
'GRAPH 1
    Set rng1 = ActiveSheet.Range(Range("W30").Offset(jump * 50, 0), Range("AA40").Offset(jump * 50, 0))


With ActiveSheet.ChartObjects.Add(Left:=rng1.Left, Width:=rng1.Width, Top:=rng1.Top, Height:=rng1.Height)
 '(Left:=100, Width:=375, Top:=75, Height:=225)
    .Chart.ChartType = xlXYScatterLines

     Set srs = .Chart.SeriesCollection.NewSeries
    srs.Name = "Graph1" '
    srs.XValues = Range(Range("O30").Offset(jump * 50, 0), Range("O30").Offset(jump * 50, 0).End(xlDown))
    srs.Values = Range(Range("N30").Offset(jump * 50, 0), Range("N30").Offset(jump * 50, 0).End(xlDown))

End With
    End With

Answer 2

1. There could be lot's of problems which are difficult to analyse when we don't have data you are working with. However, see next points which could be helpful and solve the 1st issue.

2. If you finally place your chart as ChartObject on some sheet you could do it a bit different way. Moreover, by default new chart is bar style. To avoid changing it into ScatterLines then (which I guess causes some exceptions) try to use the following code (instead of your in some areas):

   Dim c As Chart ==>change into Dim c as Shape

The whole section starting with: Set c=Activeworkbook.Charts.Add until End With replace with the following code:

Set c = Sheets(Sh).Shapes.AddChart(xlXYScatterLines, 10, 10, 200, 200)

Dim s As Series
Set s = c.Chart.SeriesCollection.NewSeries
With s
    .Values = yaxis
    .XValues = xaxis

End With

1. To set position of your chart try to use similar solution before end of the loop:

   'setting position
   With c
       .Left = Range("M10").Offset(0,1).Left
       .Top = Range("M10").Offset(0,1).Top
   End With
   

but instead of Range("M10") put the reference of your data Range.