Urgent, help needed on range selection!

[aiyer]

Hi all!

I was trying to plot values for 2 variables as a XY scatter plot i
Excel, using Macros and recorded a macro while performing the abov
task.
The following is the macro I recorded.

Sub le( )

Charts.Add
ActiveChart.ChartType = xlXYScatter
ActiveChart.SetSourceData Source:=Sheets("Comparison of LE ti
distance"). _
Range("L6")
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).XValues = _
"='Comparison of LE tip distance'!R2C4:R102C4"
ActiveChart.SeriesCollection(1).Values = _
"='Comparison of LE tip distance'!R2C2:R102C2"
ActiveChart.Location Where:=xlLocationAsObject, Name:= _
"Comparison of LE tip distance"

End Sub


However, in this case since I manually selected the require
rows/columns for the graph, we can see that the cells ranging from
!R2C4:R102C4 were selected etc....
This might not be the general case. Hence I counted the no: of activ
rows and stored it into a variable called 'i'. I was wondering if ther
would be a way to select the cells like !R2C4:R"i"C4 instead o
!R2C4:R102C4, so that the code can be generalized.

I would appreciate your suggestions guys.
Thanks alot.

Arun.
Vtec Corp

 

[Tom Ogilvy]

Sub le( )

Charts.Add
ActiveChart.ChartType = xlXYScatter
ActiveChart.SetSourceData Source:=Sheets("Comparison of LE tip
distance"). _
Range("L6")
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).XValues = _
"='Comparison of LE tip distance'!R2C4:R" & i & "C4"
ActiveChart.SeriesCollection(1).Values = _
"='Comparison of LE tip distance'!R2C2:R" & i & "C2"
ActiveChart.Location Where:=xlLocationAsObject, Name:= _
"Comparison of LE tip distance"

End Sub