VBA Excel动态图表使用For 循环偶数奇数列

Question

请参阅下面的代码：

Sub CreatePieCharts()
'Declare the variables
    Dim wks As Worksheet
    Dim AddtionalCharts As Chart
    Dim MySeries As Series
    Dim Rng As Range
    Dim CatRange As Range
    Dim SourceRange As Range
    Dim SourceData As Range
    Dim LeftPos As Double
    Dim TopPos As Double
    Dim Gap As Integer
    Dim i As Long
    Dim j As Long
    Dim k As Long
    'Set the range for the source data from the active worksheet
    Set Rng = Range("A1").CurrentRegion
    'Set the position of the first chart
    LeftPos = Range("M3").Left
    TopPos = Range("M3").Top
    'Set the gap between charts
    Gap = 5
    'Set the range for the category values
    For j = 1 To Rng.Columns.Count
        For i = 2 To Rng.Columns.Count
            If j Mod 2 = 1 And i Mod 2 = 0 Then _
                Set SourceData = Union(Rng.Columns(j), Rng.Columns(i))
            'Create the pie charts
            Set AddtionalCharts = ActiveSheet.Shapes.AddChart.Chart
            With AddtionalCharts
                .SetSourceData SourceData, xlColumns
                .ChartType = xlPie
                .ApplyDataLabels xlDataLabelsShowValue, False
                .Parent.Left = LeftPos
                .Parent.Top = TopPos
                TopPos = TopPos + .Parent.Height + Gap
            End With
        Next i
    Next j
End Sub

基本上，宏需要遍历列并根据列偶数或奇数状态创建图表。例如：图表 1 和答案 1 应该是一个图表，图表 2 和答案 2 应该是下一个图表，依此类推。

现在我能够创建图表，但由于某种原因，还有其他一些额外的图表显示我不需要的图表。我做错了什么？

Answer 1

我认为您只需要将饼图创建位包含在If结构中即可。现在不是，所以无论如何都会创建一个图表。

你正在做的是这样的：

If <condition> Then <statement1>
<statement2>

在这里，无论<condition>如何，<statement2>都将始终执行。

请改为执行以下操作：

If <condition> Then
    <statement1>
    <statement2>
    '...
End If

在这种情况下，只有在满足<condition>的情况下，<statement2>才会被执行（连同<statement1>）。

在您的具体情况中：

            If j Mod 2 = 1 And i Mod 2 = 0 Then
                Set SourceData = Union(Rng.Columns(j), Rng.Columns(i))
                'Create the pie charts
                Set AddtionalCharts = ActiveSheet.Shapes.AddChart.Chart
                With AddtionalCharts
                    .SetSourceData SourceData, xlColumns
                    .ChartType = xlPie
                    .ApplyDataLabels xlDataLabelsShowValue, False
                    .Parent.Left = LeftPos
                    .Parent.Top = TopPos
                    TopPos = TopPos + .Parent.Height + Gap
                End With
            End If