ravinggenius
10-15-2008, 11:45 AM
For reference I have a chart on Sheet1 (Overview) that is supposed to reference data on Sheet2 (Sheet2).
Dim months As Range
Dim first_month_num As Integer
Dim last_month_num As Integer
Set months = ThisWorkbook.Sheets("Sheet2").Range( _
ThisWorkbook.Sheets("Sheet2").Cells(last_month_num + 2, 1), _
ThisWorkbook.Sheets("Sheet2").Cells(first_month_num + 2, 1) _
)
With ThisWorkbook.Sheets("Overview").ChartObjects("Chart 10").Chart
With .SeriesCollection(1)
.Name = "Program Views (Month)"
.values = ThisWorkbook.Sheets("Sheet2").Range( _
ThisWorkbook.Sheets("Sheet2").Cells(last_month_num + 2, 2), _
ThisWorkbook.Sheets("Sheet2").Cells(first_month_num + 2, 2) _
).Value
.XValues = months
End With
With .SeriesCollection(2)
.Name = "Program Views (PTD)"
.values = ThisWorkbook.Sheets("Sheet2").Range( _
ThisWorkbook.Sheets("Sheet2").Cells(last_month_num + 2, 3), _
ThisWorkbook.Sheets("Sheet2").Cells(first_month_num + 2, 3) _
).Value
.XValues = months
End With
End With
When I run this, I get "A formula in this worksheet contains one or more invalid references", and the script stops. In Excel 2003 I can step through this line-by-line and everything is fine. In Excel 2007 I get an Automation error and an Unspecified error when I try to step through. I can tell that it doesn't like the way I am referencing my ranges, but I don't know how to fix it. I am using this style of reference in a couple other places, and they seem to be the only sticking points in my code.
Dim months As Range
Dim first_month_num As Integer
Dim last_month_num As Integer
Set months = ThisWorkbook.Sheets("Sheet2").Range( _
ThisWorkbook.Sheets("Sheet2").Cells(last_month_num + 2, 1), _
ThisWorkbook.Sheets("Sheet2").Cells(first_month_num + 2, 1) _
)
With ThisWorkbook.Sheets("Overview").ChartObjects("Chart 10").Chart
With .SeriesCollection(1)
.Name = "Program Views (Month)"
.values = ThisWorkbook.Sheets("Sheet2").Range( _
ThisWorkbook.Sheets("Sheet2").Cells(last_month_num + 2, 2), _
ThisWorkbook.Sheets("Sheet2").Cells(first_month_num + 2, 2) _
).Value
.XValues = months
End With
With .SeriesCollection(2)
.Name = "Program Views (PTD)"
.values = ThisWorkbook.Sheets("Sheet2").Range( _
ThisWorkbook.Sheets("Sheet2").Cells(last_month_num + 2, 3), _
ThisWorkbook.Sheets("Sheet2").Cells(first_month_num + 2, 3) _
).Value
.XValues = months
End With
End With
When I run this, I get "A formula in this worksheet contains one or more invalid references", and the script stops. In Excel 2003 I can step through this line-by-line and everything is fine. In Excel 2007 I get an Automation error and an Unspecified error when I try to step through. I can tell that it doesn't like the way I am referencing my ranges, but I don't know how to fix it. I am using this style of reference in a couple other places, and they seem to be the only sticking points in my code.