Excel宏：每次该列中的名称更改时，如何循环使宏通过特定列的图表

Alright so my goal is to create a chart making macro since I have about 90-some different Station name; and Each station needs it's own chart.

好吧所以我的目标是创建一个图表制作宏,因为我有大约90个不同的站名;每个站都需要它自己的图表。

The multiple series I would like to use are my estimated CFS, simulated CFS, stress period number (no.), and the Station Name. I'm attempting to make simple xy scatter line graphs where the No. value would be my x range, and the est:CFS and sim:CFS would both be my y ranges to create 2 rather simple lines.

我想要使​​用的多个系列是我估计的CFS,模拟CFS,压力期数(编号)和工作站名称。我正在尝试制作简单的xy散点线图,其中No.值将是我的x范围,而est:CFS和sim:CFS都是我的y范围,可以创建2条相当简单的线条。

Now my problem is simply: How should I design the code in VBA so that it knows to stop receiving data for a series in the chart for Niobrara River Station chart, and start using the following data the Snake River station chart... and on so forth until it loops through all 90-some charts.

现在我的问题很简单:如何在VBA中设计代码,以便它知道停止接收Niobrara River Station图表中图表系列的数据,并开始使用以下数据Snake River站图...等等等等,直到它遍历所有90个图表。

I'm stumped on how to phrase a command to loop through and read that Station Name column and for it to understand that all those rows that correspond to that station belong to it, and for it to understand that's all the data it needs for one chart while when the station changes I'd like it to move on to the next.

我很难说如何用一个命令来循环并读取该站名列,并让它理解所有那些对应于该站的行都属于它,并让它理解它所需要的所有数据。图表,当车站改变时,我希望它继续前进到下一个。

The image below shows how the data on the worksheet is laid out:

下图显示了工作表中数据的布局方式:

I apologize if that's a little difficult to understand if there is any more information I can provide to make my question more clear I'd be happy to post more.

如果我能提供更多信息以便更清楚地表达我的问题,我很难理解,如果这有点难以理解我会很乐意发布更多信息。

1 个解决方案

#1

The code below will allow you to retrieve all the unique Station names from the worksheet and put them into the Stations() string array.

下面的代码将允许您从工作表中检索所有唯一的站名称,并将它们放入Stations()字符串数组中。

Dim TopRowOfData As Integer
Dim StationNameColumn As Integer    
Dim Stations() As String

Sub GetUniqueChartNames()

    Dim rngTmp As Range
    Dim outRange As Range

    'Select the first data cell of the Station name column.
    Cells(TopRowOfData, StationNameColumn).Select
    'Select the rest of the data in the column.
    Range(Selection, Selection.End(xlDown)).Select
    'Assign this data to a range variable.
    Set rngTmp = Selection
    'Find a row that occurs below the area of the range data.  This will be used
    'to paste the filtered values into temporarily.
    outRow = rngTmp.Row + rngTmp.Rows.Count + 10 '10 is arbitrary, could be any number.
    Set outRange = Cells(outRow, 1)
    'Filter the data values by unique values and paste the results into the outRange area.
    rngTmp.AdvancedFilter Action:=xlFilterCopy, CopyToRange:=outRange, Unique:=True
    'Get the output results of the filter operation and store them in a range object.
    'outRow contains the heading of the filtered column.  outRow + 1 is where the data starts.
    Cells(outRow + 1, 1).Select
    Range(Selection, Selection.End(xlDown)).Select
    Dim rngFinal As Range
    Set rngFinal = Selection
    'Add the output results into the Stations array.
    ReDim Stations(rngFinal.Rows.Count - 1)
    Dim i As Integer
    For i = 0 To rngFinal.Rows.Count - 1
        Stations(i) = Cells(rngFinal.Row + i, rngFinal.Column).Value
    Next

    'Delete the temporary range.
    rngFinal.Clear

End Sub

The TopRowOfData variable is just that, an integer telling your code what the top row is where the data starts. StationNameColumn is the number of the column containing station names ( A=1, B=2, etc.)

TopRowOfData变量就是这个,一个整数告诉你的代码顶行是数据开始的位置。 StationNameColumn是包含站名的列号(A = 1,B = 2等)

Once you have the array of Station names you can step down through the station name column and retrieve all the data values associated with each item in the array. Then create the individual charts based on that data.

获得站名称数组后,可以逐步执行站名称列,并检索与阵列中每个项目关联的所有数据值。然后根据该数据创建单个图表。

Or you can step through the values in the station name column and just retrieve the address of the first and last row associated with the current station name, and then create your chart based on that data. The code below will do that, assuming your data is sorted by Station name so that all identical station names are grouped together.

或者,您可以单步执行工作站名称列中的值,只检索与当前工作站名称关联的第一行和最后一行的地址,然后根据该数据创建图表。下面的代码将执行此操作,假设您的数据按工作站名称排序,以便将所有相同的工作站名称组合在一起。

Sub FindRowsAssociatedWithStationName()

    Dim i As Integer
    Dim j As Integer
    Dim rng As Range
    'Temp variables to store the first and last row numbers.
    Dim first As Integer
    Dim last As Integer

    'Loop through all of the station names.
    For i = 0 To UBound(Stations)
        'Select the first data cell of the station names column.
        Cells(TopRowOfData, StationNameColumn).Select
        'Select the rest of the data in the column.
        Range(Selection, Selection.End(xlDown)).Select
        'Assign this data to a range variable.
        Set rng = Selection

        'Initialize both of the row number variables to 0.
        first = 0
        last = 0

        'Loop through all the data rows.
        For j = 0 To rng.Rows.Count - 1
            If Cells(rng.Row + j, StationNameColumn).Value = Stations(i) Then
                'Set the first variable.
                If first = 0 Then
                    first = rng.Row + j
                End If
                'Set the last variable.
                last = rng.Row + j
             End If
        Next

        'Call a method to create the actual charts, passing in the first and last row associated with the current Station name.
        Call CreateChart(first, last)

    Next
End Sub

Your code to create the actual charts could then use the values of first and last (rows) to retrieve the appropriate data for those rows.

然后,创建实际图表的代码可以使用first和last(rows)的值来检索这些行的相应数据。