Excel VBA可在不使用范围的情况下制作多个图形 [英] Excel VBA for making multiple graph without using Range

问题描述

我想要多个序列图.

我将这段代码显示为三行.

I made this code to show three lines.

但是，它不起作用，我不能使用 Range ，因为我的数据源在三行("I1:I30"，"I51:I80"，"I101:I131")

However, it doesn't work, I can't use Range because my data source is sparse at three line ("I1:I30","I51:I80","I101:I131")

With ActiveSheet.Shapes.AddChart.Chart
    .HasTitle = True
    .ChartTitle.Text = "My Graph"
    .ChartType = xlLine
    .SetSourceData Range("I1:I30","I51:I80","I101:I131") 'thats the data for three lines I want to show.
    .SeriesCollection(1).Name = "item1" 
    .SeriesCollection(1).XValues = Range("G1:G30") 

我该如何解决?

推荐答案

范围设置错误.

Range("I1:I30","I51:I80","I101:I131")

范围应设置如下.

Range("I1:I30, I51:I80, I101:I131")

但是，如果x轴的范围恒定，则创建散点图是适当的.

However, if the range of the x-axis is constant, it will be appropriate to create a scatter chart.

Sub test()
    Dim sourRange(1 To 3) As Range
    Dim Srs As Series
    Dim Cht As Chart
    Dim i As Integer
    
    Set sourRange(1) = Range("i1:i30")
    Set sourRange(2) = Range("i51:i80")
    Set sourRange(3) = Range("i101:i131")
    
    Set Cht = ActiveSheet.Shapes.AddChart.Chart
    With Cht
        .HasTitle = True
        .ChartTitle.Text = "My Graph"
        .ChartType = xlXYScatterLinesNoMarkers
        For Each Srs In .SeriesCollection
            Srs.Delete
        Next Srs
        For i = 1 To 3
            Set Srs = .SeriesCollection.NewSeries
            With Srs
                .Name = "item" & i
                .Values = sourRange(i)
                .XValues = Range("G1:G30")
            End With
        Next i
    End With
End Sub

这篇关于Excel VBA可在不使用范围的情况下制作多个图形的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！