Hi everyone,
Here is the problem :
In one sheet (nammed "DATA") : the data set
In another sheet (nammed "Graphs"; same excel file) : the charts I want to make
My real data set is very huge, but in order to simplify the topic, I made a typical example of it : see the attached file "Data.JPG".
You will also find the example of graphics I want to generate from this data set.in the attached image "graphs.JPG"
So, as you can see, my goal is to produce :
For each variety and compound, I want to represent in a chart : the values of the compound (in Y) according to the Stage (in X) and by treatment (series 1 and series 2).
For each series, I represent the mean values (upper table in the image) with their respective standard deviations (lower table in the image).
So I have to build a loop allowing me to create a graph for each compound and variety.
I would also like to order the graphs as in my example.
As you can understand, I have two factors that have to be set in the loop.
The "Varieties" factor, which corresponds to differents lines.
The "Compound" factor, which corresponds to differents columns.
Please find in the following code, the lines I made in order to build the first chart (top left).
- Sub Macro24()
- '
- ' Macro24 Macro
- '
- '
- ActiveSheet.Shapes.AddChart2(240, xlXYScatterLines).Select
- ActiveChart.SeriesCollection.NewSeries
- ActiveChart.FullSeriesCollection(1).Name = "=DATA!$B$6:$C$6"
- ActiveChart.FullSeriesCollection(1).XValues = "=DATA!$D$6:$D$8"
- ActiveChart.FullSeriesCollection(1).Values = "=DATA!$E$6:$E$8"
- ActiveChart.SeriesCollection.NewSeries
- ActiveChart.FullSeriesCollection(2).Name = "=DATA!$B$9:$C$9"
- ActiveChart.FullSeriesCollection(2).XValues = "=DATA!$D$9:$D$11"
- ActiveChart.FullSeriesCollection(2).Values = "=DATA!$E$9:$E$11"
- ActiveChart.FullSeriesCollection(2).HasErrorBars = True
- ActiveChart.FullSeriesCollection(2).ErrorBars.Select
- ActiveChart.FullSeriesCollection(2).ErrorBar Direction:=xlY, Include:= _
- xlBoth, Type:=xlCustom, Amount:="=DATA!$E$27:$E$29", MinusValues:="=DATA!$E$27:$E$29"
- ActiveChart.FullSeriesCollection(1).HasErrorBars = True
- ActiveChart.FullSeriesCollection(1).ErrorBars.Select
- ActiveChart.FullSeriesCollection(1).ErrorBar Direction:=xlY, Include:= _
- xlBoth, Type:=xlCustom, Amount:="=DATA!$E$24:$E$26", MinusValues:="=DATA!$E$24:$E$26"
- ActiveChart.FullSeriesCollection(2).Select
- With Selection.Format.Line
- .Visible = msoTrue
- .ForeColor.RGB = RGB(255, 0, 0)
- End With
- With Selection.Format.Fill
- .Visible = msoTrue
- .ForeColor.RGB = RGB(255, 0, 0)
- End With
- ActiveChart.Axes(xlValue).MajorGridlines.Select
- ActiveChart.FullSeriesCollection(2).ErrorBars.Select
- With Selection.Format.Line
- .Visible = msoTrue
- .ForeColor.RGB = RGB(255, 0, 0)
- End With
- ActiveChart.FullSeriesCollection(1).Select
- With Selection.Format.Line
- .Visible = msoTrue
- .ForeColor.ObjectThemeColor = msoThemeColorAccent1
- .ForeColor.TintAndShade = 0
- End With
- With Selection.Format.Fill
- .Visible = msoTrue
- .ForeColor.ObjectThemeColor = msoThemeColorAccent1
- .ForeColor.TintAndShade = 0
- End With
- ActiveChart.FullSeriesCollection(1).ErrorBars.Select
- With Selection.Format.Line
- .Visible = msoTrue
- .ForeColor.ObjectThemeColor = msoThemeColorAccent1
- .ForeColor.TintAndShade = 0
- End With
- ActiveChart.ChartArea.Select
- ActiveChart.SetElement (msoElementLegendRight)
- ActiveChart.SetElement (msoElementChartTitleAboveChart)
- Selection.Caption = "=DATA!R5C5"
- ActiveChart.Axes(xlValue).MinimumScale = 0
- ActiveChart.SeriesCollection(1).ErrorBar Direction:=xlX, Include:=xlNone, _
- Type:=xlPercent, Amount:=5
- ActiveChart.SeriesCollection(2).ErrorBar Direction:=xlX, Include:=xlNone, _
- Type:=xlPercent, Amount:=5
- End Sub
I am used to programming loops under R.
However, VBA language is completely unknown to me, so I have a lot of trouble to manage this issue on my own or to identify usefull topics that could help me.
Also I would be very grateful for any help or discussion around this "devil loop", for which I did not find any solutions for several days.
If my question is not specific enough or if you are missing information, please let me know.
Best regards,
Greg.
