Range Selection & XValues in Chart

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Trevor Williams - 26 Jan 2007 11:57 GMT

I have two questions that I'm sure you can help me with

QUESTION 1.
I'm trying to select the whole range below E9 that contains data using the
following code:

Set myXValues = Worksheets("Model").Range("E9").End(xlDown)
This selects the LAST cell under cell E9 that contains data, not the whole
range

but, if I use this code:
Set myXValues = Worksheets("Model").Range("E9", Selection.End(xlDown))
It selects every cell from E9 to E65536

So, what's the correct way of doing it, without select E9 first, and then
using Selection.End(xldown)?

QUESTION 2
Once I've successfully selected the range, I need to set it as the XValues
in a chart.
I've been dabbling, but seems that the SeriesCollection needs to use the
R1C1 range style - is this right?

My 'non-functioning' code is below - Any help in these queries would be great.

Thanks

Trevor Williams

With Sheets("Sheet1").ChartObjects("Chart 1")
.SeriesCollection(1).XValues = myXValues
.SeriesCollection(1).Values = "=Model!R9C6:R11C6"
.SeriesCollection(2).XValues = myXValues
.SeriesCollection(2).Values = "=Model!R9C7:R11C7"
End With

Reply to this Message

Jon Peltier - 26 Jan 2007 13:31 GMT

1. Almost there:

With Worksheets("Model")
Set myXValues = .Range(.Range("E9"), .Range("E9").End(xlDown))
End With

2. Doesn't this work with a proper range definition?

.SeriesCollection(1).XValues = myXValues

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______

>I have two questions that I'm sure you can help me with
>
[quoted text clipped - 32 lines]
> .SeriesCollection(2).Values = "=Model!R9C7:R11C7"
> End With

Reply to this Message

Trevor Williams - 26 Jan 2007 14:01 GMT

Hi Jon

1 - Thanks for that - its been bugging me for months!...
2 - No, it doesn't work, or rather, I can't get it to work... Here's my
code. I'm running it from "Sheet1" which contains the chart (stacked bar).

Sub UpdateFutureCatSize()

Dim myValues, myXValues, myStackValue As Range

With Worksheets("Model")
Set myXValues = .Range(.Range("E9"), .Range("E9").End(xlDown))
Set myValues = .Range(.Range("F9"), .Range("F9").End(xlDown))
Set myStackValue = .Range(.Range("G9"), .Range("G9").End(xlDown))
End With

With Sheets("Sheet1").ChartObjects("Chart 1")
.SeriesCollection(1).XValues = myXValues
.SeriesCollection(1).Values = myValues
.SeriesCollection(2).XValues = myXValues
.SeriesCollection(2).Values = myStackValues
End With

End Sub

> 1. Almost there:
>
[quoted text clipped - 49 lines]
> > .SeriesCollection(2).Values = "=Model!R9C7:R11C7"
> > End With

Reply to this Message

Jon Peltier - 27 Jan 2007 03:00 GMT

You left an important item out of the string of objects, "Chart":

With Sheets("Sheet1").ChartObjects("Chart 1").Chart
.SeriesCollection(1).XValues = myXValues
etc.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______

> Hi Jon
>
[quoted text clipped - 81 lines]
>> > .SeriesCollection(2).Values = "=Model!R9C7:R11C7"
>> > End With

Reply to this Message

Trevor Williams - 29 Jan 2007 08:29 GMT

Ah ha! - Thanks Jon, works a treat!

Trevor

> You left an important item out of the string of objects, "Chart":
>
[quoted text clipped - 94 lines]
> >> > .SeriesCollection(2).Values = "=Model!R9C7:R11C7"
> >> > End With

Reply to this Message