How to implement looping range into a chart

Question

Basically, I am trying to automate one of the tasks of my job into a macro, which is taking some data and putting it into a 5 series clustered column chart to make a visually appealing way to present the info. The issue is that the range length changes week to week depending on # of parts dealt with. I'm just running into a slight error when adding the series values, as excel isn't accepting my string argument. Below is what I have.


Sub FilterRankAndCreateChart()
Dim i As Integer
Dim rnge As String

i = 0

Sheets("MC Spares Risk Calculations").Select
Range("A1").Select
Do Until ActiveCell.Value = ""
    ActiveCell.Offset(1, 0).Select
    i = i + 1
Loop

Let rnge = "$J$2:$J$" & i

Worksheets("MC Risk Chart").Activate
ActiveSheet.ChartObjects(1).Activate
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.FullSeriesCollection(1).Name = "=""Low Risk"""
    ActiveChart.FullSeriesCollection(1).Values = "='Risk Charts'!rnge"
    ActiveChart.FullSeriesCollection(1).XValues = "='Risk Charts'!$B$2:$B$149"

The value of rnge as displayed in the immediate window is correct. but, when i use rnge to define the values, I get a 1004 error (application or object-defined error). Any insight on how to fix this?

See [this question](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba) for how to find the last row. — BigBen, Jul 11 '19 at 15:39
Still got the same error finding the last row that way. I can find the proper range, VBA just wont take the string as a solid argument for the range of my data series. — Eric Carey, Jul 11 '19 at 16:59
`Dim rnge as Range`, then `Set rnge = ActiveCell.Range("$J$2:$J$" & i)`. Looks like [`Series.Values`](https://learn.microsoft.com/en-us/office/vba/api/excel.series.values) takes a `Range` argument, not a string. Same with `XValues`, based on the examples in the documentation. Untested, but if it does take a string, then you can't have `rnge` within the quotes there. — BigBen, Jul 11 '19 at 17:03

score 0 · Answer 1 · answered Jul 11 '19 at 18:04

Just to close this question out:

1: As noted, no need to loop to find the last cell.

2: From the documentation, Series.Values can take a Range object. Same for Series.XValues.

With Worksheets("MC Spares Risk Calculations")
    Dim lastRow as Long
    lastRow = .Cells(.Rows.Count, 1).End(xlUp).Row

    Dim rng as Range
    Set rng = .Range("J2:J" & lastRow)
End With

With Worksheets("MC Risk Chart").ChartObjects(1).Chart
    ...
    .FullSeriesCollection(1).Values = rng
    .FullSeriesCollection(1).XValues = rng2 ' another Range object
End With

How to implement looping range into a chart

1 Answers1