How to define a range using R for a loop to create charts
I have a dataset in excel that looks like this:
MA M1 M2 T1 T2 W1 W2 Th1 Th2 F1 F2
100 1 2 2 1 2 0 0 2 2 1
100 2 0 2 1 2 2 1 2 2 0
101 1 3 0 1 1 0 1 0 1 1
101 0 2 1 1 0 1 1 1 1 1
102 1 1 1 2 0 1 0 0 2 2
102 1 2 0 1 1 0 1 1 0 3
I am trying to create a bar chart for each code (100, 101, 102) where each code will have 2 datasets and the horizontal values will be m1, m2, t1, etc. So in the end I need 3 columnar graphs. I am trying to use a for loop to generate these graphs in VBA and this is what I was trying:
Sub MA()
Dim i As Integer
Dim row1 As Integer, row2 As Integer
For i = 1 To 6 Step 2
Dim MAChart As Chart
Set MAChart = ActiveSheet.Shapes.AddChart.Chart
With MAChart
row1 = i + 1
row2 = i + 2
.ChartType = xlColumnClustered
.SetSourceData Source:=ActiveSheet.Range("Q& row1 & : & Z & row2")
End With
Next i
End Sub
I keep getting the "Application or Object Defined" error. I am having trouble defining the range of each graph as it changes based on i. I would like to find a clean way to make a series of charts using a for loop, without redefining the range / dataset every time for every other chart. Does anyone know a good way to do this?
source to share
The following treats the entire "range" as a string that will not match the range
Range("Q& row1 & : & Z & row2")
Try using below, you don't need row1 and row2 rows. Notice how I am creating a string that makes a valid range
Range("Q" & 1+i & ":Z" & 2+i)
Used with your code something like, note that I also moved your "Dim" out of the loop, this doesn't need to be created every loop, but it needs to "Set" every loop
Sub MA()
Dim i As Integer
Dim MAChart As Chart
Dim row1 As Integer, row2 As Integer
For i = 1 To 6 Step 2
Set MAChart = ActiveSheet.Shapes.AddChart.Chart
With MAChart
.ChartType = xlColumnClustered
.SetSourceData Source:=ActiveSheet.Range("Q" & i & ":Z" & 1+i)
End With
Next i
End Sub
You can also consider the position of the above diagrams to stack them on top of each other.
source to share