Excel VBA Loop breaks half-way through

Question

This VBA script, based off of this tutorial is supposed to update a chart when called. What is catching me is the For loop.

Sub updateChart()
    Dim i As Integer
    Dim lw As Long
    Dim sh As Worksheet
    Dim ws As Chart

    Set sh = Sheet4 'Table
    Set ws = Charts(1) 'Chart
    lw = sh.Range("B" & sh.Rows.Count).End(xlUp).Row

    ws.Activate
    ActiveChart.ChartArea.Select
    ActiveChart.SetSourceData sh.Range(sh.Range("E" & lw & ":N" & lw), sh.Range("E" & lw & ":N" & lw)), xlColumns

    For i = 1 To lw 'Headers to be added (Change if more headers required).
        ActiveChart.SeriesCollection(i).Name = "='Data Input (Updated 9.11.20)'!B" & i + 1
    Next i
    ActiveChart.SeriesCollection(1).Values = "='Data Input (Updated 9.11.20)'!E" & lw & "C1:E" & lw & "N" & lw
End Sub

When running the script, I get

Runtime Error '1004'

Parameter not valid

in the middle of the For loop, when i = 9. There is no difference in the source data at row 9, so I'm unclear why this breaks the loop. The source data is:

You declare `Dim i As Integer` but `Dim lw As Long` and here `For i = 1 To lw` you try to push the Long `lw` into the smaller Integer `i`. That cannot workout well. Both need to be of the same type. — Pᴇʜ, Jun 23 '21 at 15:07
Are the series in columns E to N which is maximum of 10 so loop should be `For i = 1 To 10` ? is `Data Input (Updated 9.11.20)` the same as Sheet4. ? Which rows do you want to plot, you seem to have only 1. — CDP1802, Jun 23 '21 at 15:44
@Pᴇʜ This resolved the issue and the code now works as intended. I also made several other changes to it as well which resolve the problem CDP1802 brought up. — Chris, Jun 23 '21 at 17:04

score 0 · Answer 1 · answered Jun 23 '21 at 17:10

As per the comments in the question, the solution was to change 'Dim lw as Long' to 'Dim lw as Integer'

In addition, numerous other changes were made to the function at the bottom. For comparison's sake, I will post the now working code here for anyone who views the question later.

    Sub updateChart()
        Dim i As Integer
        Dim lw As Integer
        Dim sh As Worksheet
        Dim ws As Chart
    
        Set sh = Sheet4 'Table
        Set ws = Charts(1) 'Chart
        lw = sh.Range("B" & sh.Rows.Count).End(xlUp).Row
    
        ws.Activate
        ActiveChart.ChartArea.Select
        
        For i = 1 To lw 'Headers to be added (Change if more headers required).
            ActiveChart.SeriesCollection.Item(i).Name = "='Data Input (Updated 9.11.20)'!$B$" & i
            ActiveChart.SeriesCollection.Item(i).Values = "='Data Input (Updated 9.11.20)'!$E$" & i & ":$N$" & i
        Next i
End Sub

Among others, the biggest changes were to remove the SetSourceData method that was unnecessary, moving the Values method into the For loop, changing the loop to expand to the entire dataset by "For I to lw" instead of whatever was there before, and rewriting the Names and Values to reflect what you would put in the dialogue box in Excel.

That is not correct they should both be `Long` as Excel has more rows than `Integer` can handle. Actually there is no point in using `Integer` in VBA. See [here](https://stackoverflow.com/questions/26409117/why-use-integer-instead-of-long/26409520#26409520) — Pᴇʜ, Jun 24 '21 at 08:32

Excel VBA Loop breaks half-way through

1 Answers1