绘图时自动数据范围识别Excel VBA

Question

我有一个生成两列数据的VBA代码。重复此分析以在相同列中生成更多数据，但针对不同的条件，并将此数据置于第一次迭代的输出之下。我如何让代码生成一个XY散射平滑线旁边的每个块生成的数据?我的尝试是在下面，但我遇到了三个问题:1)它只生成一个图形，然后调试停止宏，2)它生成一个条形图，而不是散点3)我如何得到生成的每个图形被缩放放置在数据旁边，它是从这样的我得到一列图形?

    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

此代码查找列中的代码并在指定范围内绘制图形。当第一个代码通过循环时，它将抵消查找和绘图。

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. 当我们没有你正在处理的数据时，可能会有很多难以分析的问题。然而，看看下面几点可能会有所帮助，并解决第一个问题。

2. 如果你最终把你的图表作为ChartObject放在一些表格上，你可以用不同的方式来做。此外，默认新建图表为条形样式。为了避免将其更改为ScatterLines，然后(我猜这会导致一些异常)尝试使用以下代码(而不是在某些区域):

   Dim c As Chart ==>变Dim c as Shape

从:Set c=Activeworkbook.Charts.Add开始直到End With的整个部分替换为以下代码:

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. 要设置图表的位置，请尝试在循环结束前使用类似的解决方案:

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

而不是Range("M10")，把你的数据范围的引用