动态选择单元格并在图表中输入 [英] Dynamically select cells and input in chart

问题描述

我想创建一个宏，该宏运行表中的一系列数据并能够自动对其进行图形处理.事实是，数据可以具有更多或更少的点，但是代码仍然需要能够选择和绘制所需的数据.

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.

这是我正在使用的(下).我希望能够在同一张图表上绘制每个点编号(在列编号为Point No.下)的垂直坐标与垂直位移的关系.如您所见，有四个不同的点编号(1,2,3,4)，每个点编号重复9次.但是，这些数字可以更改(例如，可能有8个点号，每个点号都有3次迭代)，并且代码仅需要能够基于点号值本身来选择数据.

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:

由于我是VBA的新手，所以我仍然对语法没有完全的了解，但这是我的想法(有些不是VBA语言):

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 !!!!!!!!!!!!!!!-----------------------

在最初的案例中，我收到了@Viktor的好评，但是我想知道是否有任何方法可以修改代码以使代码更具挑战性(而这又超出了我的脑袋):

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):

我在表中添加了更多列(请参见下文)，并希望代码创建一个额外的图表来绘制*垂直坐标vs.垂直应力"，同时仍保持图表不受垂直坐标vs .垂直位移.当前代码不满足此要求的原因是因为它假定表所在的工作表上没有其他数据(但有)，我希望能够添加更多数据列并创建更多图表(所有图表均相对于垂直坐标绘制)而不会影响其他图表.如果有任何修改代码的方法，将不胜感激！

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

.com/GYsZo.png

推荐答案

实际上，我认为使用公式+命名范围"更容易完成任务，但是这是一个挑战，需要学习编写代码.希望它对您有用. 我还尝试对其进行评论以更好地理解.

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

这篇关于动态选择单元格并在图表中输入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！