Setting Chart Range
 

I'm getting an error when trying to set the chart range of a chart.
I've narrowed the problem down and figured out that it occurs when
there is no data in the data range i'm trying to set the chart to.
Any workaround for this?

This is the code i'm using. Chart is activated in a previous set of
code. Columns 1 and 2 (ie x value and series 1 values) always have
data in them. The series 2 column may or may not have data in it, and
may or may not have data in every cell of the range, but I need to set
it anyway. Any ideas on how to fix this problem?

Sub Update_Chart_Ranges(ChartDataRange)
Dim LastRow As Integer
Dim myColumn As Integer, myRow As Integer

LastRow = ChartDataRange.End(xlDown).Row
myColumn = ChartDataRange.Column
myRow = ChartDataRange.Row + 1

ActiveChart.SeriesCollection(1).XValues = "='Actual Chart Data'!R"
& myRow & "C" & myColumn & ":R" & LastRow & "C" & myColumn
ActiveChart.SeriesCollection(1).Values = "='Actual Chart Data'!R"
& myRow & "C" & myColumn + 1 & ":R" & LastRow & "C" & myColumn + 1
ActiveChart.SeriesCollection(2).Values = "='Actual Chart Data'!R"
& myRow & "C" & myColumn + 2 & ":R" & LastRow & "C" & myColumn + 2
End Sub

Setting Chart Range
 

On Jun 20, 3:37 pm, "Jon Peltier"
wrote:
What's the chart type? Line and XY charts are subject to errors in cases of
missing data. Change to area or column chart type, run your code, then
change back.

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

"John" wrote in message

oups.com...



I'm getting an error when trying to set the chart range of a chart.
I've narrowed the problem down and figured out that it occurs when
there is no data in the data range i'm trying to set the chart to.
Any workaround for this?

This is the code i'm using. Chart is activated in a previous set of
code. Columns 1 and 2 (ie x value and series 1 values) always have
data in them. The series 2 column may or may not have data in it, and
may or may not have data in every cell of the range, but I need to set
it anyway. Any ideas on how to fix this problem?

Sub Update_Chart_Ranges(ChartDataRange)
Dim LastRow As Integer
Dim myColumn As Integer, myRow As Integer

LastRow = ChartDataRange.End(xlDown).Row
myColumn = ChartDataRange.Column
myRow = ChartDataRange.Row + 1

ActiveChart.SeriesCollection(1).XValues = "='Actual Chart Data'!R"
& myRow & "C" & myColumn & ":R" & LastRow & "C" & myColumn
ActiveChart.SeriesCollection(1).Values = "='Actual Chart Data'!R"
& myRow & "C" & myColumn + 1 & ":R" & LastRow & "C" & myColumn + 1
ActiveChart.SeriesCollection(2).Values = "='Actual Chart Data'!R"
& myRow & "C" & myColumn + 2 & ":R" & LastRow & "C" & myColumn + 2
End Sub- Hide quoted text -

- Show quoted text -

Ok, I'll try that. It's actually a chart with 1 bar set and 1 line
set. And of course the line set is the one that may or may not have
data.