Hi
I'm trying to create a macro, that I will assign to a button, to draw a graph on an empty chart that I have placed in Sheet1, and the values that are used to plot the graph depend on the value in cell D18 of Sheet2. So for e.g if the value in D18 is 5, then the y-axis values are taken from C44:G44 from Sheet3 for one series, and C12:G12 from Sheet3 for another series. The x-axis values would be C3:G3 from Sheet1. I want cell D18 in Sheet2 to allow for numbers between 1 and 180. At the moment I have tried the following code:
Dim i As Long
For i = 1 To 180
Sheets("Sheet2").Select
If Range("D18") = i Then
Sheets("Sheet1").Select
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).Name = "=""A"""
ActiveChart.SeriesCollection(1).Values = "='Sheet3'!Range(Cells(44,3),Cells(44,i+2))"
ActiveChart.SeriesCollection(1).XValues = "='Sheet1'!Range(Cells(3,3),(3,i+2))"
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(2).Name = "=""B"""
ActiveChart.SeriesCollection(2).Values = "='Sheet3'!Range(Cells(12,3),Cells(12,i+2))"
Else
Sheets("Sheet1").Select
Range("C3").Select
End If
Next
End Sub
It is not working because of the way I have specified the values for the series. Does anyone know how to adjust it so that it works?
Bookmarks