Update Chart

[mbachi]

I would like some help with creating a script to update a chart. This is my code below with the first series being the code I thought would work and then the rest being standard Excel macro code:

Sub Adjust_Sat()
Dim ChrtEnd As Integer
Dim ChrtStart As Integer
Application.ScreenUpdating = False
Sheets("Daily").Select
Range("C76").End(xlToRight).Select
ChrtEnd = ActiveCell.Column
ChrtStart = ChrtEnd - 20
Sheets("Saturday Elapsed").Select
ActiveChart.PlotArea.Select

ActiveChart.SeriesCollection(1).XValues = "=Daily!R75C3:R75C39"
ActiveChart.SeriesCollection(1).Values = "=Daily!R76C3" & CStr(ChrtStart) & ":R76C" & CStr(ChrtEnd)

ActiveChart.SeriesCollection(2).XValues = "=Daily!R75C3:R75C39"
ActiveChart.SeriesCollection(2).Values = "=Daily!R77C3:R77C39"
ActiveChart.SeriesCollection(3).XValues = "=Daily!R75C3:R75C39"
ActiveChart.SeriesCollection(3).Values = "=Daily!R78C3:R78C39"
ActiveChart.SeriesCollection(4).XValues = "=Daily!R75C3:R75C39"
ActiveChart.SeriesCollection(4).Values = "=Daily!R79C3:R79C39"
End Sub


When I try to run this I get "Run Time Error 1004, unable to set the properties value of the series class". What I am after is defining the range to only show 20 entries instead of the 30 that are there. Your help will be appreciated.

 

[Alan2005]

OK, so how about you use named ranges to define the chart series?
This works a treat and you have the bonus of the changes being dynamic.
i.e. Add an entry and it will automatically be included in your chart...

Try searching this message board for the code for this and you could also try JWALK.com, I'm certain there are instructions for this here.
Finally, Jon Peltier is a chart GOD. There are many links to his site from this forum.

If you really get stuck then let me know and I'll bash some code up.

In the meantime, enjoy xmas.

Alan

 

[mbachi]

Thanks

I have been looking at using offset and it works just as well. The problem I have encountered with offset is that all my data is in multiple columns and all the examples I can find are failing to work unless I transpose the data. Below is my OFFSET example for a named range that I then included in a chart. The data is in columns G2:AV5 and what I need to do is to only count the last 30 columns and I have failed to do it.

=OFFSET(Transpose!$G$2,0,0,COUNTA(Transpose!$G:$G)-1,COUNTA(Transpose!$2:$2))
I thought the basis of the offset formula is:
Transpose!$G$2 [reference cell],
0,0 [Rows from reference & columns from reference],
COUNTA(Transpose!$G:$G)-1 [count number of rows, I added -1 as I have header row]
COUNTA(Transpose!$2:$2) [Count of Columns]. I need to be able to change this last figure but can't do it. If anyone can help I will be very grateful.

Merry Xmas

 

[Alan2005]

mbachi,

Bearing in mind that I have had some xmas parties this week...
if i'm understanding this correctly you need to create a range that will only take the last 30 entries.

Suppose you have data in rows 2 to 5 and columns g to av
To set and name the range for the last 30 columns use;

dim nextrow as integer, nextcolumn as integer
next row = range("g2").end(xldown).row
nextcolumn = range("g2").end(xltoright).column
range(cells(1,nextcolumn-30),cells(nextrow,nextcolumn).name = "myrange"

I hope this is nearer to what you require.
I do recommend having a look at the suggested sites from earlier.
There are excellent examples of chart tricks / tips.

Good luck.

 

[tusharm]

Re: Thanks

OFFSET deals as easily with rows as it does with columns. The examples you find in various places, including my site, deal with data in columns because that is the more 'natural' format for relational database tools.

To include the cells in the last 30 columns in row 2 starting from G2 and assuming that A2:F2 are empty, one would use the untested
=OFFSET(G2,0,COUNTA(2;2)-30,0,1,30)
or, to cater to the possibility that there are less than 30 data points available,
=OFFSET(G2,0,MAX(0,COUNTA(2;2)-30),0,1,MIN(COUNTA(2;2),30))

I have been looking at using offset and it works just as well. The problem I have encountered with offset is that all my data is in multiple columns and all the examples I can find are failing to work unless I transpose the data. Below is my OFFSET example for a named range that I then included in a chart. The data is in columns G2:AV5 and what I need to do is to only count the last 30 columns and I have failed to do it.

=OFFSET(Transpose!$G$2,0,0,COUNTA(Transpose!$G:$G)-1,COUNTA(Transpose!$2:$2))
I thought the basis of the offset formula is:
Transpose!$G$2 [reference cell],
0,0 [Rows from reference & columns from reference],
COUNTA(Transpose!$G:$G)-1 [count number of rows, I added -1 as I have header row]
COUNTA(Transpose!$2:$2) [Count of Columns]. I need to be able to change this last figure but can't do it. If anyone can help I will be very grateful.

Merry Xmas

 

[tusharm]

Look at the code snippet I preserved. You have "...R76C3" & CStr(ChrtStart)...
So, if ChrtStart is any number greater than 9 you are trying to create a column reference of 300 + whatever. Obviously, XL throws a fit! {grin}

Maybe, you meant "...R76C " & CStr(ChrtStart)...

{snip}

    ActiveChart.SeriesCollection(1).Values = "=Daily!R76C3" & CStr(ChrtStart) & ":R76C" & CStr(ChrtEnd)

When I try to run this I get "Run Time Error 1004, unable to set the properties value of the series class". What I am after is defining the range to only show 20 entries instead of the 30 that are there. Your help will be appreciated.