creating a macro to draw a graph depending on the value in a particular cell

[can2c]

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?

[Andy Pope]

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

more like this

ActiveChart.SeriesCollection(1).Values = Sheet3.Range(Sheet3.Cells(44,3),Sheet3.Cells(44,i+2))

[can2c]

Sorry about that.

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