How to loop through a range using a variable in vba

Question

I'm trying to loop through a range below and get runtime error 1004. The highlighted row is this one here:

ActiveChart.SeriesCollection(i).Values = Worksheets("Chart Help").Range(Cells(10 + j, 5), Cells(10 + j, 1006))

Can anyone tell me what's wrong?

If Worksheets("Chart Help").Cells(4, 9 + j) <> " " Then
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection(i).Name = Worksheets("Chart Help").Cells(4, 9 + j)
    ActiveChart.SeriesCollection(i).XValues = Worksheets("Chart Help").Range("J5:J1006")
    ActiveChart.SeriesCollection(i).Values = Worksheets("Chart Help").Range(Cells(10 + j, 5), Cells(10 + j, 1006))
    ActiveChart.SeriesCollection(i).Select

    With Selection.Format.Line
        .Visible = msoTrue
        .ForeColor.ObjectThemeColor = msoThemeColorAccent6
        .ForeColor.TintAndShade = 0
        .ForeColor.Brightness = 0
        .Transparency = 0
    End With

    i = i + 1
End If

j = j + 1

There is no Loop in the sample code, but it looks like you should change the indices in Cells property. First is row, second column. — BrakNicku
– BrakNicku, Commented May 13, 2015 at 18:11
Your 2 calls to Cells refer to the ActiveSheet and not the Chart Help worksheet like you intend. You will need to prefix Cells with Worksheets("Chart Help").Cells for it to not error. — Byron Wall
– Byron Wall, Commented May 13, 2015 at 18:27

John Alexiou · Accepted Answer · 2015-05-13 20:16:03Z

1

Replace

.Range(Cells(10 + j, 5), Cells(10 + j, 1006))

with

.Range("E10").Offset(j,0).Resize(1, 1001)

In general avoid using the Cells to target cells, and instead start from a fixed address (like "E10" above) and using a combination of .Offset() and .Resize() to tweak the range of cells you want to act upon.

answered May 13, 2015 at 20:16

John Alexiou

29.4k12 gold badges83 silver badges136 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Byron Wall Over a year ago

I agree in the general case, this the preferred way to go. Using Cells really prompts index based ranges (For i = 1 to 10... Cells(i,5)) which is impossible to maintain.

Byron Wall · Accepted Answer · 2015-05-13 18:33:18Z

I assume your loop has the proper code and you simply didn't paste it all.

Your 2 calls to Cells refer to the ActiveSheet and not the Chart Help worksheet like you intend. You will need to prefix Cells with Worksheets("Chart Help").Cells for it to not error.

Something like this:

ActiveChart.SeriesCollection(i).Values = Worksheets("Chart Help").Range(Worksheets("Chart Help").Cells(10 + j, 5), Worksheets("Chart Help").Cells(10 + j, 1006))

Ideally you would define a reference to that worksheet to clean up the code. You also do not have to prefix the Range with the worksheet in this case. Those two ideas combined give:

Dim sht_chart As Worksheet
Set sht_chart = Worksheets("Chart Help")
ActiveChart.SeriesCollection(i).Values = Range(sht_chart.Cells(10 + j, 5), sht_chart.Cells(10 + j, 1006))

Collectives™ on Stack Overflow

How to loop through a range using a variable in vba

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related