Generate chart based on range computed with loop index

Question

I have a dataset that has 38 variables over 4 sheets. I want to produce trendline charts for each of these variables across the 4 sheets.

Example data layout

Desired graph format

Sub CreateChart_Ex1()

'works, proivdes a chart with: =SERIES(Spring!$A$4,Spring!$B$1:$P$1,Spring!$B$4:$P$4,1)
Set WS = Worksheets("Spring")

WS.Activate

WS.Range("A1:P1, A4:P4").Select

WS.Shapes.AddChart2(227, xlLine).Select

'why doesn't this loop work? provides a chart with =SERIES(,,Spring!$A$1:$P$1,1)
For i = 4 To 38

    WS.Activate

    WS.Range("A1:P1, Ai:Pi").Select

    WS.Shapes.AddChart2(227, xlLine).Select

    ActiveChart.ChartArea.Copy
    Sheets("Graphs").Select
    ActiveSheet.Pictures.Paste.Select
    Sheets("Spring").Select

Next i

End Sub

The for loop does not produce a chart with data. It looks like it gets lost when it tries to compute "A1:P1, Ai:Pi".

How could I produce all 38 graphs?

Also how can I paste the graphs after one another instead of on top of each other?

You might benefit from reading [How to avoid using Select in Excel VBA](https://stackoverflow.com/q/10714251/4996248). If you take that to heart and rewrite your code, the problem might even go away. Even if it doesn't, the debugging will be easier. — John Coleman, Apr 26 '21 at 11:29
@JohnColeman Will do, I don't normally write excel vba macros, so I don't know any better. Still, any tips on getting anywhere are appreciated. — Wyatt Sutcliffe, Apr 26 '21 at 11:35
Currently you are telling Excel to find the literal range `"Ai:Pi"`. I think you mean to concatenate like so: `"A" & i & ":P" & i` — Samuel Everson, Apr 26 '21 at 11:42
VBA handles `"A1:P1, Ai:Pi"` as one string and does no substitute for `i` (no programming language does that). Write `("A1:P1, A" & i & ":P" & i)`. With that, VBA will put the actual content of the variable `i` into the string that defines your range — FunThomas, Apr 26 '21 at 11:48
@WyattSutcliffe The problem is that macro-recorded code makes heavy use of select, but that isn't the optimal way to write Excel VBA. The link I gave gives pretty good advice. — John Coleman, Apr 26 '21 at 12:57
@FunThomas Thank you so much, this lets me use my hodegepodge code and it worked perfectly. now i just have to figure out how ot control where it pastes to and I am golden. thank you! — Wyatt Sutcliffe, Apr 26 '21 at 21:48

John Alexiou · Answer 1 · 2021-04-26T12:39:51.097

To select the i-th row under "A1" use

Range("A1").Cells(i,1)   ' reference [Ai]

and to expand the reference to column P (16-th column) use

Range("A1").Cells(i,1).Resize(1,16)      'reference [Ai:Pi]

In your case i is a number (integer) and the range object expects a string value for the cell reference. So Ai is invalid, and you could (but shouldn't) do is combine with Range("A" & i). The better solution is to start from a known cell reference (like "A1") and then use

VBA	Explanation
`.Cells(i,j)`	reference the `i`-th row and `j`-th column of a table
`.Offset(i,j)`	Move the reference by `i` rows and `j` columns
`.Resize(n,m)`	Expand the reference to include `n` rows and `m` columns

Generate chart based on range computed with loop index

1 Answers1