VBA Code for variable sized chart

[marksc92]

Hi, I'm very new to VBA and cannot get my code formatted properly to do what I want!

I have this code below that almost works the way I want it to:

ActiveSheet.Shapes.AddChart.Select
Application.ActiveChart.Parent.Name = "StackedChart-PBS"
ActiveChart.ChartType = xlColumnStacked100
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).Name = "=""Not Started"""
ActiveChart.SeriesCollection(1).Values = "='Sheet1'!$P$3:$Q$3"


However, I am wanting to define my range with variables so the chart grows and shrinks depending on the amount of data entered in Sheet 1. I know how to define the variables but anytime I use this type of formatting: Sheets(Sheet1).Range(Cells(IIRTStartRow, IIRStartCol), Cells(100, IIREndCol)) to define: 'Sheet1'!$P$3:$Q$3 it doesn't work...



Hope someone understands my problem and can explain how to use variables to define my range..

 

[pgc01]

Hi

You are defining the value range for 1 series. It should have just 1 row or 1 column. It seems you are defining a rectangular range. What are the values of IIRTStartRow, IIRStartCol and IIREndCol when you get the error?

 

[marksc92]

Sorry about the confusion, I should have given you a code example relating more to my problem. So I am used to formatting ranges with variable like this: Sheets(Sheet1).Range(Cells(3, 16), Cells(3, 16 + colnum )), but I don't know how to do that with this type of formatting: 'Sheet1'!$P$3:$Q$3. Ideally I would like to format 'Sheet1'!$P$3:$Q$3 as Sheets(Sheet1).Range(Cells(3, 16), Cells(3, 16 + colnum )) so the range grows in row 3 from column P to Q,or R, or S...ect based on the value of colnum. Currently I don't get an error, it just doesn't do anything.

 

[pgc01]

Hi

I see


For the chart, try:

ActiveChart.SeriesCollection(1).Values = Sheets("Sheet1").Range(Sheets("Sheet1").Cells(3, 16), Sheets("Sheet1").Cells(3, 16 + lColNum))

 

[marksc92]

Thank you pgc01! The solution you provided is behaving the way I was wanting it to!

 

[pgc01]

You're welcome. Thanks for the feedback.