Baffled by calling a private function

[travis]

I've written a function called RemoveZeroSeries(inputrange As Range) As
Range

Its purpose is to take a range, weed out all the rows with nothing but
zeros in them and then return a new range which is the input range
minus the rows which only contain zeros.

I get "Runtime error '91': Object variable or With Block variable not
set"

And this happens on the very last line of the function, on this line:

RemoveZeroSeries = chartable.Address

Full code follows:

Private Sub RemoveEmptyChartSeries_Click()

Range("IncomeChartNominalNoZeros").Name =
RemoveZeroSeries(Range("IncomeChartNominalData"))

' the chart didn't work either, but I've got to fix the function before
I can call it!

'ActiveSheet.ChartObjects("Chart 5").Activate
'ActiveChart.SetSourceData Source:=Sheets("Projection
1").RemoveZeroSeries(IncomeChartNominal), _
' PlotBy:=xlRows
'ActiveChart.SeriesCollection(1).XValues = Range("ProjectionYears")

End Sub

Private Function RemoveZeroSeries(inputrange As Range) As Range

Dim DownCounter, AcrossCounter, NumberOfRows As Integer

Dim temprange, chartable, upperleft As Range

' delete the next two lines for the function
'Dim inputrange As Range
'Set inputrange = Range("IncomeChartNominal")


MsgBox "There are " & inputrange.Rows.Count & " rows in the inputrange"

NumberOfRows = inputrange.Rows.Count

Set upperleft = inputrange.Resize(1, 1)

MsgBox "The Upper Left Cell is at " & upperleft.Address


' Find the first series which isn't all zeros, and name its range
"chartable"

For DownCounter = 0 To NumberOfRows

For AcrossCounter = 1 To (1 + Range("YearsProjection").Value)

If Not upperleft.Offset(DownCounter, AcrossCounter).Value = 0 Then

Set chartable = Range(upperleft.Offset(DownCounter, 0).Address &
":" & upperleft.Offset(DownCounter, 1 +
Range("YearsProjection").Value).Address)


GoTo GotFirstChartRangeSoBreakOutOfLoop

End If

Next AcrossCounter

Next DownCounter

GotFirstChartRangeSoBreakOutOfLoop:

' Now build up the rest of the range by adding additional ranges which
also have non zeros

For DownCounter = 0 To NumberOfRows

For AcrossCounter = 1 To (1 + Range("YearsProjection").Value)

If Not upperleft.Offset(DownCounter, AcrossCounter).Value = 0 Then

Set temprange = Range(upperleft.Offset(DownCounter, 0).Address &
":" & upperleft.Offset(DownCounter, 1 +
Range("YearsProjection").Value).Address)



Set chartable = Union(chartable, temprange)

AcrossCounter = 1
GoTo ThisSeriesHasAtLeastOneNonZeroSoMoveOnToTheNextOne

End If

Next AcrossCounter

ThisSeriesHasAtLeastOneNonZeroSoMoveOnToTheNextOne:

Next DownCounter

MsgBox "Chartable: " & chartable.Address
RemoveZeroSeries = chartable.Address

End Function

 

[Bob Phillips]

You have declared your function as type Range, so you can't return a string
range address. So change

RemoveZeroSeries = chartable.Address

to

Set RemoveZeroSeries = chartable

or set the function type as string

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

 

[travis]

You have declared your function as type Range, so you can't return a string
range address. So change

RemoveZeroSeries = chartable.Address

to

Set RemoveZeroSeries = chartable

or set the function type as string

Thanks Bob, the function appears to have completed without a problem,
but now I get:

Run-time Error '1004' Method 'Range" of object '_Worksheet' failed

...on the first line of my sub. I've tried a few things to get rid of
that, but none worked. Now what am I doing wrong?


Private Sub RemoveEmptyChartSeries_Click()

Range("IncomeChartNominalNoZeros").Name =
RemoveZeroSeries(Range("IncomeChartNominalData"))

ActiveSheet.ChartObjects("Chart 5").Activate
ActiveChart.SetSourceData Source:=Sheets("Projection
1").Range("IncomeChartNominalNoZeros"), _
PlotBy:=xlRows
ActiveChart.SeriesCollection(1).XValues = Range("ProjectionYears")

End Sub

 

[Bob Phillips]

What value does RemoveZeroSeries(Range("IncomeChartNominalData")) return?

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

 

[travis]

What value does RemoveZeroSeries(Range("IncomeChartNominalData")) return?

At the moment, MsgBox RemoveZeroSeries(Range("IncomeChartNominalData"))

Returns a compile error, type mismatch. That's with or without a
..Address in there, i.e MsgBox
RemoveZeroSeries(Range("IncomeChartNominalData").Address)

Also, the first message box which states the number of rows has a
blank, i.e. "The number of data series is [empty space]". When I run
this as a sub it returns "The number of data series is 24".

 

[travis]

Thanks for your help Bob, I've figured it out.

This did the trick...

With RemoveZeroSeries(Range("IncomeChartNominalData"))
..Name = "IncomeChartNominalDataNoZeros"
End With

ActiveSheet.ChartObjects("Chart 5").Activate
ActiveChart.SetSourceData Source:=Sheets("Projection
1").Range("IncomeChartNominalDataNoZeros"), _
PlotBy:=xlRows
ActiveChart.SeriesCollection(1).XValues = Range("ProjectionYears")