Macro VBA help please

[Guest]

I have the macro pasted below in a spreadsheet used for quartiling. The
marco plots data labels onto points a custom chart. It is hanging up with
the following run-time error '91' on the line that begins "xVals =
ActiveChart..."

"Object variable or with block variable not set."

I am no VBA ace so I haven't a clue where to go at this point. I believe
this was working at some point, so I have to wonder if this is a excel
version issue.

Any help would be appreciated. Thanks so much!

Sub AttachLabelsToPoints()

'Dimension variables.
Dim Counter As Integer, ChartName As String, xVals As String

' Disable screen updating while the subroutine is run.
Application.ScreenUpdating = False

'Store the formula for the first series in "xVals".
xVals = ActiveChart.SeriesCollection(1).Formula

'Extract the range for the data from xVals.
xVals = Mid(xVals, InStr(InStr(xVals, ","), xVals, _
Mid(Left(xVals, InStr(xVals, "!") - 1), 9)))
xVals = Left(xVals, InStr(InStr(xVals, "!"), xVals, ",") - 1)
Do While Left(xVals, 1) = ","
xVals = Mid(xVals, 2)
Loop

'Attach a label to each data point in the chart.
For Counter = 1 To Range(xVals).Cells.Count
ActiveChart.SeriesCollection(1).Points(Counter).HasDataLabel = _
True
ActiveChart.SeriesCollection(1).Points(Counter).DataLabel.Text = _
Range(xVals).Cells(Counter, 1).Offset(0, -1).Value
Next Counter

End Sub

 

[MisterEd]

It looks like your chart was not selected. Here is an example of
selecting a chart embedded in "Sheet1":

Worksheets("Sheet1").ChartObjects(1).Select

 

[Guest]

How do I use that in this macro? where do I need to place it, and do I need
any other code to make it work?

Thanks,

Scott

 

[MisterEd]

How do I use that in this macro? where do I need to place it, and do I need
any other code to make it work?

Thanks,

Scott

You place it right before first ActiveChart statement. You did not say
if the chart is embedded or in it's own sheet

---------------------------------------------------------------------------------
For embedded chart:

' Select chart that is embedded in Sheet1
Worksheets("Sheet1").ChartObjects(1).Select

'Store the formula for the first series in "xVals".
xVals = ActiveChart.SeriesCollection(1).Formula
---------------------------------------------------------------------------------
Or for standalone chart in it's own sheet

' Select chart that is in new sheet called Chart2
Charts("Chart2").Select

'Store the formula for the first series in "xVals".
xVals = ActiveChart.SeriesCollection(1).Formula
---------------------------------------------------------------------------------

BTW, the following statement was not working for me. Since I do
not have all you code I can't tell what's up with it. I commented
it out to test the rest of the code

ActiveChart.SeriesCollection(1).Points(Counter).DataLabel.Text = _
Range(xVals).Cells(Counter, 1).Offset(0, -1).Value