"Run-Time Error '91': Object variable or with block variable not set"

(0) Share

Report

Posted on by Hashir

Hi,
I'm using this code in Exel 2007

Sub GetChartValues()
   Dim NumberOfRows As Integer
   Dim X As Object
   Counter = 2

   ' Calculate the number of rows of data.
   NumberOfRows = UBound(ActiveChart.SeriesCollection(1).Values)

   Worksheets("ChartData").Cells(1, 1) = "X Values"

   ' Write x-axis values to worksheet.
   With Worksheets("ChartData")
      .Range(.Cells(2, 1), _
      .Cells(NumberOfRows + 1, 1)) = _
      Application.Transpose(ActiveChart.SeriesCollection(1).XValues)
   End With

   ' Loop through all series in the chart and write their values to
   ' the worksheet.
   For Each X In ActiveChart.SeriesCollection
      Worksheets("ChartData").Cells(1, Counter) = X.Name

      With Worksheets("ChartData")
         .Range(.Cells(2, Counter), _
         .Cells(NumberOfRows + 1, Counter)) = _
         Application.Transpose(X.Values)
      End With

      Counter = Counter + 1
   Next

End Sub

and i'm receiving error in this line

NumberOfRows = UBound(ActiveChart.SeriesCollection(1).Values)

plz help
thanks

*This post is locked for comments

All responses (1)

Answers (0)

Suggested answer

Community Member Microsoft Employee on at

Like (0)

Report

RE: "Run-Time Error '91': Object variable or with block variable not set"

Have you tried NumberOfRows = UBound(ActiveChart.SeriesCollection(0).Values)

changed you 1 to a 0 . The collection might need to start at 0 instead of 1.

I also assume you have a chart selected prior to running this. If not you will need to make sure the chart is selected prior to trying to use ActiveChart or you have a null reference.

Also, this really isnt the place for Excel VBA code. You might want to post somewhere like www.excelforum.com/excel-programming-vba-macros

Thanks