Excel VBA运行时错误1004参数无效求助：批量创建多系列图表

Hey there! First off, props to you for troubleshooting so far and thanks for tagging @Rory earlier. Let's dig into that Run-time Error 1004 you're hitting at .SeriesCollection(j).XValues = ws.Range(rs) and get your batch XY scatter charts working correctly.

First, let's break down the key issues in your original code that are causing the error:

• Mixing .SetSourceData (which auto-creates a series) with manual .SeriesCollection.NewSeries leads to inconsistent series indexing
• Unqualified range references (in some code versions) can cause worksheet mismatch errors
• Confusing math for calculating Y-value ranges led to invalid range selections

Here's a revised, working version of your code with clear comments explaining each improvement:

Sub CreateBatchXYCharts()
    Dim ws As Worksheet
    Dim chObj As ChartObject
    Dim ch As Chart
    Dim xRange As Range
    Dim ySeriesRange As Range
    Dim i As Integer, j As Integer
    Dim chartHeight As Double, chartWidth As Double, chartLeft As Double
    
    ' Target worksheet containing your data
    Set ws = ThisWorkbook.Sheets("S1")
    
    ' Clear existing charts to avoid clutter
    ws.ChartObjects.Delete
    
    ' Define shared X-axis data (S2:S21 for all series)
    Set xRange = ws.Range("S2:S21")
    
    ' Grab positioning settings from your reference range (F1:J10)
    chartHeight = ws.Range("F1:J10").Height
    chartWidth = ws.Range("F1:J10").Width
    chartLeft = ws.Range("F1:J10").Left
    
    ' Loop through columns 20 to 45 (each column gets its own chart)
    For i = 20 To 45
        ' Create a new chart object on the worksheet
        Set chObj = ws.ChartObjects.Add( _
            Left:=chartLeft, _
            Top:=(i - 20) * chartHeight, _
            Width:=chartWidth, _
            Height:=chartHeight)
        Set ch = chObj.Chart
        
        ' Configure basic chart properties
        ch.ChartType = xlXYScatterLines
        ch.HasTitle = True
        ch.ChartTitle.Text = ws.Range("T1").Value
        ch.HasLegend = True
        
        ' Add 20 series to each chart
        For j = 1 To 20
            ' Calculate the 20-row block for this series' Y-values
            ' Starts at row 2 + (j-1)*20, ends at row 21 + (j-1)*20
            Set ySeriesRange = ws.Range( _
                ws.Cells(2 + (j - 1) * 20, i), _
                ws.Cells(21 + (j - 1) * 20, i) _
            )
            
            ' Add a new series and assign data directly (avoids indexing bugs)
            With ch.SeriesCollection.NewSeries
                .Name = "Series " & j ' Optional: replace with a cell reference for meaningful names
                .XValues = xRange
                .Values = ySeriesRange
            End With
        Next j
        
        ' Rename the chart object for easy identification
        chObj.Name = "cht" & (i - 19)
    Next i
End Sub

Key Fixes & Improvements: #

• Removed .SetSourceData: Instead of letting Excel auto-create a series, we add each series manually with NewSeries—this eliminates indexing conflicts that caused the 1004 error
• Simplified range math: The Y-value range calculation uses clear, predictable logic to select 20-row blocks without confusing offsets
• Used ChartObject directly: This is more straightforward for worksheet-based charts than working with Shape objects
• Fully qualified ranges: All range references are explicitly tied to ws to prevent errors if another worksheet is active
• Reused positioning values: We only calculate the chart's size/position once instead of recreating the reference range every loop

Why Your Original Code Threw the Error: #

In your first code snippet, .SetSourceData created an initial series automatically. When you added new series with NewSeries, your loop tried to reference .SeriesCollection(j) starting at j=1—this meant you were modifying the auto-created series first, and if the range assignment didn't align with Excel's expectations (or if the range was invalid), it triggered the "parameter not valid" error. By assigning data directly to each new series object, we avoid this indexing confusion entirely.

内容的提问来源于stack exchange，提问作者Joe Park