时间戳

首页 > 解决方案 > VBA - 创建具有不同列范围的图表

vba - VBA - 创建具有不同列范围的图表

问题描述

在我的工作簿中，我使用宏创建了一些图表，这些图表从工作表“FG_Count”中获取数据。但我不知道如何编写它始终使用正确列的宏。

图表的数据在 B3 / D3 / F3 / H3 范围内，图表是使用 For-Loop 创建的。

这是我用于创建图表的宏。

Private Sub CommandButton1_Click()
    Dim v
    Dim a As Integer 

    For Each s In ActiveWorkbook.Sheets
        If s.Visible = True Then
            v = v + 1
        End If
    Next s

    a = 1
    For i = 2 to v 
        ActiveSheet.Shapes.AddChart2(240, xlXYScatter).Select
        With ActiveChart
            For Each s In ActiveChart.SeriesCollection
                s.Delete
            Next s
            .SeriesCollection.NewSeries
            .FullSeriesCollection(1).Name = "='Project Overview'!$B$" & a

            ' Here I don't know how to write the Code that it works
            .FullSeriesCollection(1).XValues = "='FG_Count'!$ ...."

            .FullSeriesCollection(1).Values = "={1}"
            .Axes(xlCategory).Select
            .Axes(xlCategory).MaximumScale = 1
        End With
        a = a + 1
    Next i
End Sub

编辑：

我可以找到一个解决方案..也许它不是最好的，但它有效:)

For i = 2 To v
    Dep = Worksheets(i).Name

    If i = 2 Then
        ColumnLetter = "B"
    ElseIf i = 3 Then
        ColumnLetter = "D"
    ElseIf i = 4 Then
        ColumnLetter = "F"
    ElseIf i = 5 Then
        ColumnLetter = "H"
    ElseIf i = 6 Then
        ColumnLetter = "J"
    ElseIf i = 7 Then
        ColumnLetter = "L"
    ElseIf i = 8 Then
        ColumnLetter = "N"
    End If

    ActiveSheet.Shapes.AddChart2(240, xlXYScatter).Select
    With ActiveChart
        For Each s In ActiveChart.SeriesCollection
            s.Delete
        Next s
        .SeriesCollection.NewSeries
        .FullSeriesCollection(1).Name = "='Project Overview'!$B$" & e
        .FullSeriesCollection(1).XValues = "='FG_Count'!$" & ColumnLetter & "$3"
        .FullSeriesCollection(1).Values = "={1}"
        .Axes(xlCategory).Select
        .Axes(xlCategory).MaximumScale = 1
    End With
Next

标签： vbaexcel

解决方案

秘密是知道字母 A 的 ASCII 码是 65。然后我们可以使用 CHR$ 函数将 ASCII 值转换为文本。所以你可以更换你的整个

 If i = 2 Then
    ColumnLetter = "B"

阻止

  ColumnLetter = Chr$(64 + i)

推荐阅读