PDA

View Full Version : Run-time error and invalid reference (related)



ravinggenius
10-16-2008, 10:32 AM
I have data in a separate sheet, and I'm looping through each column one, I'm adding a new series to my chart. The relevant code is below.

Dim current_column As Integer
Dim the_labels As Range
Dim the_range As Range
Dim new_series As Series
Dim current_chart As Chart

current_column = current_column + 1

'ActiveSheet = ThisWorkbook.Sheets("Presentation Views")
Set current_chart = ThisWorkbook.Sheets("Presentation Views").ChartObjects("monthly_program_views").Chart
Set new_series = current_chart.SeriesCollection.NewSeries
Set the_range = ThisWorkbook.Sheets("data").Range( _
ThisWorkbook.Sheets("data").Cells(last_month_num + 2, current_column), _
ThisWorkbook.Sheets("data").Cells(first_month_num + 2, current_column) _
)

new_series.Name = "Series Name"
new_series.Values = the_range 'error on this line
new_series.XValues = the_labels
new_series.MarkerStyle = xlNone
new_series.Border.Weight = xlMedium
I am fast approaching my wit's end with this. Please can anybody help me with this? Thank you.

CreganTur
10-16-2008, 10:34 AM
What error are you getting and which line of your code is highlighted when you debug?

ravinggenius
10-16-2008, 10:45 AM
When stepping through the code I get:

A formula in this worksheet contains one or more invalid references.

Verify that your formulas contain a valid path, workbook, range name, and cell reference. and this is highlighted new_series.XValues = the_labels
When I just let the code run I get:

Run-time error '5':

Invalid procedure call or argument Then when I hit 'OK', another alert shows up:

A formula in this worksheet contains one or more invalid references.

Verify that your formulas contain a valid path, workbook, range name, and cell reference. When I look at the chart, the first series is there with the correct values and xvalues.

CreganTur
10-16-2008, 11:00 AM
Well at first blush I think the reason for your error is that you never assign a value to the_labels variable. You're getting an error because you're evaluating a null value.

give it a value and see if that fixes your issue.

ravinggenius
10-16-2008, 11:24 AM
My apologies; I forgot to put that in the original post. I have this line just under the current_column = 3 line:Set the_labels = ThisWorkbook.Sheets("data").Range("A" & last_month_num + 2 & ":A" & first_month_num + 2)

CreganTur
10-16-2008, 11:44 AM
My apologies; I forgot to put that in the original post. I have this line just under the current_column = 3 line:

We're starting to see recursion here...

In the code you provide above you're using 2 variables (last_month_num & first_month_num) that are not declared in your original post, and are not given a value in your first post.

Can you repost the code you're having problems with in its entirety so we can see exactly what's going on?

ravinggenius
10-16-2008, 12:23 PM
Can you repost the code you're having problems with in its entirety so we can see exactly what's going on?
This sub is called in the context of Sheet3 (Presentation Views):
Sub graph_monthly_views()
Dim current_column As Integer
Dim section_type As String
Dim chart_name As String
Dim data_range As Range
Dim point_labels As Range

Dim the_labels As Range
Dim the_range As Range
Dim new_series As Series
Dim current_chart As Chart

current_column = 3
Set the_labels = ThisWorkbook.Sheets("data").Range("A" & last_month_num + 2 & ":A" & first_month_num + 2)
For Each section In all_sections
section_type = section.Item("type")
If (section_type = "pres" Or section_type = "case") And section.Item("is_active") = True Then
current_column = current_column + 1
chart_name = "monthly_" & section_type & "_views"

Set current_chart = ThisWorkbook.Sheets("Presentation Views").ChartObjects(chart_name).Chart
Set new_series = current_chart.SeriesCollection.NewSeries
Set the_range = ThisWorkbook.Sheets("data").Range( _
ThisWorkbook.Sheets("data").Cells(last_month_num + 2, current_column), _
ThisWorkbook.Sheets("data").Cells(first_month_num + 2, current_column) _
)

new_series.Name = section.Item("name")
new_series.Values = the_range
new_series.XValues = the_labels
new_series.MarkerStyle = xlNone
new_series.Border.Weight = xlMedium

'excel 2007 was inserting an unwanted chart title
'Call current_chart.ChartTitle.Delete
End If
Next section

Rows(current_row & ":" & current_row).RowHeight = 250

current_row = current_row + 1
End Sub
Anything not declared here is declared globally in a module as follows:
Global current_row As Integer
Global source_data As Workbook

'each item contains a bunch of section details (name, seconds_total etc)
Global all_sections As New Collection

'these specify the month range the report is to cover
'they are integers of the number of months ago (from this month) the earliest and latest months in the range
Global first_month_num As Integer
Global last_month_num As Integer
Thanks for your help. I'm really not trying to make this difficult.