VBA Code for variable sized chart

marksc92

New Member
Joined
Sep 4, 2014
Messages
10
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.. :)
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
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?
 
Upvote 0
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.
 
Upvote 0
Hi

I see


For the chart, try:

Code:
ActiveChart.SeriesCollection(1).Values = Sheets("Sheet1").Range(Sheets("Sheet1").Cells(3, 16), Sheets("Sheet1").Cells(3, 16 + lColNum))
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,286
Messages
6,171,189
Members
452,390
Latest member
kemafa77

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top