First of all, just wanted to say thanks for all the useful info on here. I lurked, like most people, getting the solutions from other people's threads. You guys save people a lot of time, which I think is awesome.
Now, to my problem, which I haven't been able to solve despite a couple hours' searching and tinkering. I came close, but in other ways, not really, and it's bound to be something simple. I'm only a noob who goes around scavenging other people's code, please bear with me.
Anyway, let's say I've got a column of data, about 600 cells. I need to make multiple small graphs based on two non-adjacent cells in that same column. The data comes from individuals at certain time points, each individual has about twenty timepoints, so I need 30 lines to appear on the same graph to see whether they are going up or down, and to exclude those graphs aren't doing what they're supposed to, check for outliers, etc.
<tbody>
</tbody>The problem is, while creating those multiple series with a .SeriesCollection loop was successful, it takes a range of cells and not two cells separately.
I would need graphs of: B2 and B4 (the two points only!), then respectively B22 and B24, B42 and B44... you get it.
Here's approximately the code I came up with, typed from memory:
Of course, the problem is that code takes three points instead of two (like in B2:B4, instead of just B2 and B4). The macro recorder made me beat my head against the wall for a while because it simply records the two cells as "$A$2; $A$4" or something, which doesn't actually work.
I tried my best to google stuff like "vba chart non adjacent cells" but it always seems like the problems that people have are different, more complicated than mine, and there's talk of saving cells in arrays and stuff... which I've no clue about.
Course I could just manually change the work sheet so that the two time points are adjacent; but there's a very good chance that other analyses will require different time points, once again not adjacent, so I would just be constantly shuffling the worksheet around. Not an elegant solution. In fact I would prefer to have some dialogue where I would enter the time points, but at the moment my heads thinking about it. It would suffice to make this work.
Appreciate your wise counsel.
Now, to my problem, which I haven't been able to solve despite a couple hours' searching and tinkering. I came close, but in other ways, not really, and it's bound to be something simple. I'm only a noob who goes around scavenging other people's code, please bear with me.
Anyway, let's say I've got a column of data, about 600 cells. I need to make multiple small graphs based on two non-adjacent cells in that same column. The data comes from individuals at certain time points, each individual has about twenty timepoints, so I need 30 lines to appear on the same graph to see whether they are going up or down, and to exclude those graphs aren't doing what they're supposed to, check for outliers, etc.
A | B |
Time | Data |
1 | 40 |
2 | 35 |
3 | 38 |
4 | 30 |
5 | 39 |
6 | 45 |
7 | 47 |
etc |
<tbody>
</tbody>
I would need graphs of: B2 and B4 (the two points only!), then respectively B22 and B24, B42 and B44... you get it.
Here's approximately the code I came up with, typed from memory:
Code:
Sub GraphTry()
Dim ws As Worksheet
Dim i As Long
Dim myChtObj As ChartObject
Set ws = Sheets("Overall")
Set myChtObj = ActiveSheet.ChartObjects.Add _
(Left:=100, Width:=375, Top:=75, Height:=500)
With myChtObj.Chart
.ChartType = xlXYScatterLines
.HasLegend = True
For i = 1 To 700 Step 20
With .SeriesCollection.NewSeries
.Values = ws.Range("B" & i + 1, "B" & i + 3)
End With
Next
End With
End Sub
Of course, the problem is that code takes three points instead of two (like in B2:B4, instead of just B2 and B4). The macro recorder made me beat my head against the wall for a while because it simply records the two cells as "$A$2; $A$4" or something, which doesn't actually work.
I tried my best to google stuff like "vba chart non adjacent cells" but it always seems like the problems that people have are different, more complicated than mine, and there's talk of saving cells in arrays and stuff... which I've no clue about.
Course I could just manually change the work sheet so that the two time points are adjacent; but there's a very good chance that other analyses will require different time points, once again not adjacent, so I would just be constantly shuffling the worksheet around. Not an elegant solution. In fact I would prefer to have some dialogue where I would enter the time points, but at the moment my heads thinking about it. It would suffice to make this work.
Appreciate your wise counsel.