Dynamic Graph in excel 2007

Question

I want to graph the result of my table in excel. here is the code that I have :

Sub bow()
'
' bow Macro
'

'
Worksheets("Sheet3").Select
tahX = Cells(3, 3).Value + 2
Xval = Offset(A1, 1, 2, 1, 1)
    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.SetSourceData Source:=Range("'Sheet3'!$12:$12,'Sheet3'!$10:$10")
    ActiveChart.ChartType = xlXYScatter
    ActiveChart.SeriesCollection(1).Name = "=""bowe"""
    ActiveChart.SeriesCollection(1).XValues = "='Sheet3'!$12:$12"
    ActiveChart.SeriesCollection(1).Values = "='Sheet3'!$10:$10"
End Sub

now I want to limit the X-values to the value that is defined in the cell C3. So for example if C3 = 20, I want it to only use the first 20 values in the row number 12. How can I do that ?

Answer 1

Hello hakim-Sina , I'm not sure what exactly your problem is, but in this line:

ActiveChart.SeriesCollection(1).XValues = "='Sheet3'!$12:$12"

you are specifying row 12 to be plotted.Instead of !$12:$12 you can have !$C$12:$L$12 which will set range from C3 to L12 .If you want to set the row dynamically you can use :

Set mcStartX = Worksheets(1).Cells(12, 3)
startColX = mcStartX.Address()
//we assume that in cell C3 we have specified number of cells to be plotted 
toAdd = Cells(3, 3).Value
Set mcEndX = Worksheets().Cells(12, 2 + toAdd)
endColX = mcEndX.Address()

Using this code, and assuming that you have 10 in C3, you will get $C$12 from startColX and $L$12 from endColX.The rest is straight forward you can use following code:

ActiveChart.SeriesCollection(1).XValues = "='Sheet3'!" + startColX + ":" + endColX

I hope that this could be helpful.

Cheers,