Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range Selection & XValues in Chart
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range Selection & XValues in Chart
1. Almost the
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 _______ "Trevor Williams" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range Selection & XValues in Chart
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" wrote: 1. Almost the 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 _______ "Trevor Williams" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range Selection & XValues in Chart
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 _______ "Trevor Williams" wrote in message ... 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" wrote: 1. Almost the 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 _______ "Trevor Williams" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range Selection & XValues in Chart
Ah ha! - Thanks Jon, works a treat!
Trevor "Jon Peltier" wrote: 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 _______ "Trevor Williams" wrote in message ... 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" wrote: 1. Almost the 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 _______ "Trevor Williams" wrote in message ... 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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
error when setting XValues for chart | Excel Programming | |||
Displaying XValues and Values for chart using VBA | Charts and Charting in Excel | |||
Changing XValues and Values in a chart | Excel Programming | |||
Can't Set XValues for Surface Chart | Charts and Charting in Excel | |||
Chart Setting Xvalues | Excel Programming |