HELP!!! Variable Charting Range

[christoferlives]

Hi Everyone,

What I'm having trouble with is detirmining how to create a variable range on an line graphing macro. Right now I have a static list of data, but I expect that list to change day by day. Somtimes adding rows, some times deleting rows. In order to get around this problem, I created a macro that counts through the sheet and records how many rows of data there are.

After the number of data rows are found, all i want to do is have a the starting point begin at "V10" and end at the bottom of the Sheet. This i will accomplish may necessity of charting this range.

Below you can see the important pieces of my macro:

in the ActiveChart area is where I get into problems;
"ActiveChart.SeriesCollection(1).Values = "='Main'!Y10,:YNumRange"
I know this is wrong, but my problem is precisely this.

my active chart puts in the, ( from my main sheet) a range that starts at Y10 and needs to proceed to the end of the sheet. As dictated by the Y(column) and the NumRows is the length until the sheet ends.

in an example; the range would have been Y10:Y163..... but i replaced the 163 with my NumRows variable in the case that the number of rows may increase/decrease. This way, the range of numbers is always correct.

Can anyone debug my code?






Sub RangeFinder()

Dim x As Integer
Dim NumRows As Integer
Dim NumRealized As Integer
Dim NumUnrealized As Integer

Sheets("Main").Range("V10").Select
NumRealized = Range(Selection, Selection.End(xlDown)).Count
Range("H3").Value = NumRealized

Sheets("Main").Range("D10").Select
NumRows = Range(Selection, Selection.End(xlDown)).Count + 10
Range("H4").Value = NumRows

ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlLine
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).Name = "='Main'!$Y$9"
ActiveChart.SeriesCollection(1).Values = "='Main'!Y10,:YNumRows "
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(2).Name = "='Main'!$V$9"
ActiveChart.SeriesCollection(2).Values = "='Main'!$Y$10:$Y$NumRealized"
ActiveChart.SeriesCollection(2).XValues = "='Main'!$D$10:$D$NumRows"

 

[AlphaFrog]

You could do this without using a macro. Here's a step-by-step guide:
Dynamic Charting Formulas in Excel

If you really want to use VBA code, let us know.

 

[christoferlives]

Yes, please put it into VBA code. Its a simple debug (I think). But please, the code has more than one application area in other workbooks.

Thanks

 

[AlphaFrog]

    ActiveChart.SeriesCollection(1).Values = "=[COLOR="Red"]'Main'!Y10,:Y" & NumRows[/COLOR]
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection(2).Name = "='Main'!$V$9"
    ActiveChart.SeriesCollection(2).Values = "=[COLOR="Red"]'Main'!$Y$10:$Y$" & NumRealized[/COLOR]
    ActiveChart.SeriesCollection(2).XValues = [COLOR="Red"]"='Main'!$D$10:$D$" & NumRows[/COLOR]