Range Selection & XValues in Chart

[Trevor Williams]

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

 

[Jon Peltier]

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

 

[Trevor Williams]

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

 

[Jon Peltier]

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

 

[Trevor Williams]

Ah ha! - Thanks Jon, works a treat!

Trevor

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
_______