Modifying chart source range using variable

[Carl]

I know there have been previous posts on this but I am relatively new
to VBA and am having trouble adapting it to my needs.

I currently have a workbook that is charting a trend of data verses
time. Every month we dump in more data and refresh the pivot tables
that sort it out. The charts are all on one sheet and the pivot
tables are spread out throughout different sheets. I am currently
trying to make a macro that will automatically adjust the source data
to include new data every month by using the COUNTA function to set
the amount of rows included in the chart. I would also like it to
only use a max of 48 months (48 rows) but I cant work that out later.

Right now I have the followingprimarily from boards I've read-
explanations/efforts in caps)

Sub Macro1()

Dim rng As Range

Set rng = Range("C6:C27").Offset(Range("DO2").Value, 0) (' I HAVE A
COUNTA FUNTION HERE THAT TELLS HOW MANY ROWS ARE USED)

Sheets("Sheet3").Select '(THIS IS WHERE SOURCE DATA IS)

sRange = "='" & ActiveSheet.Name & "'!" & rng.Address(1, 1, xlR6C3)
(THIS PART STUMPS ME BUT DATA STARTS ON ROW6 AND COLUMN 3)

Sheets("Charts").Select

ActiveSheet.ChartObjects("Chart 2").Chart.SeriesCollection(1).Values =
sRange (THIS IS WHERE I GET RUN TIME ERROR "UNABLE TO SET VALUES
PROPERTY OF SERIES CLASS)

END SUB

 

[Joel]

No sure if time in your source data are rows or columns. You need to
increaes the range of the chart as well as to change the series collection.
the code below increases the chart area. I wasn't sure how to increase the
size of the series collection without more info. I used currentregion to
increase data size. the series collection need to be in R1C1 format which
can be done using convertformula.


Sub Macro1()

Dim rng As Range

Sheets("Sheet3").Activate '(THIS IS WHERE SOURCE DATA IS)

sRange = ActiveSheet.Range("C6").CurrentRegion.Address
Charts("Chart 2").Activate
'ActiveChart.ChartArea.Select
ActiveChart.SetSourceData Source:=Sheets("Sheet3").Range(sRange)

sRange = "='" & "Sheet3" & "'!" & sRange
sRange = Application.ConvertFormula( _
Formula:=sRange, _
fromReferenceStyle:=xlA1, _
toReferenceStyle:=xlR1C1)

Charts("Chart 2").Select
'not sure what you need to do from here


End Sub

 

[Carl]

No sure if time in your source data are rows or columns. You need to
increaes the range of the chart as well as to change the series collection.
the code below increases the chart area. I wasn't sure how to increase the
size of the series collection without more info. I used currentregion to
increase data size. the series collection need to be in R1C1 format which
can be done using convertformula.

Sub Macro1()

Dim rng As Range

Sheets("Sheet3").Activate '(THIS IS WHERE SOURCE DATA IS)

sRange = ActiveSheet.Range("C6").CurrentRegion.Address
Charts("Chart 2").Activate
'ActiveChart.ChartArea.Select
ActiveChart.SetSourceData Source:=Sheets("Sheet3").Range(sRange)

sRange = "='" & "Sheet3" & "'!" & sRange
sRange = Application.ConvertFormula( _
Formula:=sRange, _
fromReferenceStyle:=xlA1, _
toReferenceStyle:=xlR1C1)

Charts("Chart 2").Select
'not sure what you need to do from here

End Sub













- Show quoted text -

First off, thanks for your help Joel. You gave me another idea/method
of going about this. However, I was curious about a few of your
suggestions. First though let me clarify what I am doing.

On Sheet 3 I have a bunch of pivot tables containing the data I am
graphing from. Once I get one chart right though I can do the rest.
In column B is the month, and in column C next to it is the
corresponding total. Every new month we add data for the previous so
this chart needs to evolve with the date. (i.e.
B C
Nov 74

On the Charts SheetI have a line graph with the month on the x axis
and the line is charting the total for that month.

Ok now for my questions:
with your example:

1) sRange = ActiveSheet.Range("C6").CurrentRegion.Address - Would
this account for the title row or does it select the entire data rage?

2) sRange = "='" & "Sheet3" & "'!" & sRange -
Basically ... what does this mean?
sRange = Application.ConvertFormula( _
Formula:=sRange, _
fromReferenceStyle:=xlA1, _
toReferenceStyle:=xlR1C1)

* I hate to be a total newbie but its more important to understand the
code then for it to actually work. I think my logic is good, but I
don't have a good knowledge of the syntax to use.

Thanks again for your time!

 

[Joel]

1) Currentregion is the area starting at the present cell and moving out
until is finds a blank cell in the rows (in the first row) and columns (in
the first column) and setting a rectangular area (not including the blanks).

To include the title row use C5 instead of C6.

Adress returns the address of the currentregion

2) sRange = "='" & "Sheet3" & "'!" & sRange
Add the sheet name infront of the address

3) sRange = Application.ConvertFormula( _
Formula:=sRange, _
fromReferenceStyle:=xlA1, _
toReferenceStyle:=xlR1C1)

There are two types of addressing in excel (A1, or R1C1). The above formula
coverts from one addressing method to the other.

A1 is A1:C7
R1C1 (r - row, c - column) is R1C1:R7C3

 

[Carl]

1) Currentregion is the area starting at the present cell and moving out
until is finds a blank cell in the rows (in the first row) and columns (in
the first column) and setting a rectangular area (not including the blanks).

To include the title row use C5 instead of C6.

Adress returns the address of the currentregion

2) sRange = "='" & "Sheet3" & "'!" & sRange
Add the sheet name infront of the address

3) sRange = Application.ConvertFormula( _
Formula:=sRange, _
fromReferenceStyle:=xlA1, _
toReferenceStyle:=xlR1C1)

There are two types of addressing in excel (A1, or R1C1). The above formula
coverts from one addressing method to the other.

A1 is A1:C7
R1C1 (r - row, c - column) is R1C1:R7C3













- Show quoted text -

Ok, I got that to work, but it completely changed my charts from
normal size line charts to small pivot based line charts. I'm not
sure if that was a default configuration for the change but when I
manually right click I still cant resize or look at source data.

Is there no way to simply modify the x and values like so?
**ActiveChart.SeriesCollection(1).Values = sRange (after defining
sRange as the address and all?)

 

[Joel]

Did you change the magnification of the page (25%, 50%, 100%, 200%). I
believe the original code was based on a chart sheet, not a chart placed on a
worksheet. If you can't resize the chart, then it is on then Chart Sheet and
the only reason for the chart to be small is the scale on the page has
changed.

The sould be no reason that you can't right click on the white area outside
the chart and look at the source selection.

Do you have only One Seris collection or multiple Series collections? with
One Seris collection you should only have to change the source and not the
series collection. I ony included series collection because you had it on
your original posting and didn't know if you had multiple series collections.

 

[Carl]

Did you change the magnification of the page (25%, 50%, 100%, 200%). I
believe the original code was based on a chart sheet, not a chart placed on a
worksheet. If you can't resize the chart, then it is on then Chart Sheet and
the only reason for the chart to be small is the scale on the page has
changed.

The sould be no reason that you can't right click on the white area outside
the chart and look at the source selection.

Do you have only One Seris collection or multiple Series collections? with
One Seris collection you should only have to change the source and not the
series collection. I ony included series collection because you had it on
your original posting and didn't know if you had multiple series collections.







- Show quoted text -

Well I figured out one problem but still stumped by the other. The
font for the updated chart was small which was easily fixed, but the
format still changed from a standard line chart to a pivot chart with
the field buttons included (which they weren't before). So therefore
the chart is much smaller to make room for the pivot options. Any
idea why the format changed like that? I still can't manually change
source data for the chart likely because it is a pivot chart all of a
sudden.

As for the series idea some have more than one series, others do
not.
I'm not really sure whats causing the formatting problem now, but why
couldn't the following work? (besides the fact I'm getting a (unable
to set values property for series class error)
sRange = ActiveSheet.Range("C6").CurrentRegion.Address

Sheets("Sheet3").Activate '(THIS IS WHERE SOURCE DATA IS)
sRange = ActiveSheet.Range("C6").CurrentRegion.Address
Sheets("Charts").Select
ActiveSheet.ChartObjects("Chart 2").Activate
ActiveChart.SeriesCollection(1).Values = sRange

 

[Joel]

The code below works. You don't need to use "ADDRESS". Instead use the
"SET" to define the range. Also eliminate the ACTIVATE from the CHART,it
doesn't work. I combined the 1st two lines. I don't like using the activate
unless it is necessary.


Set sRange = Sheets("Sheet3").Range("C6").CurrentRegion
Sheets("Chart 2").Select
' this doesn't work ActiveSheet.ChartObjects("Chart 2").Activate
ActiveChart.SeriesCollection(1).Values = sRange

 

[Jon Peltier]

(a) Since most everyone else posts their replies on top of a thread, so
should you, to improve our ability to read the entire thread.

(b) You don't need vba for this. You can define Names in the sheet that
dynamically change to capture all of the relevant data:

http://peltiertech.com/Excel/Charts/DynamicColumnChart1.html
http://peltiertech.com/Excel/Charts/Dynamics.html

- Jon

 

[Carl]

Thanks for your help.