Macro VBA help please
 

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).Ha sDataLabel = _
True
ActiveChart.SeriesCollection(1).Points(Counter).Da taLabel.Text = _
Range(xVals).Cells(Counter, 1).Offset(0, -1).Value
Next Counter

End Sub

Macro VBA help please
 

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

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

Macro VBA help please
 

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" wrote:

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

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

Macro VBA help please
 

Scott Wagner wrote:
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" wrote:

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

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



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).Da taLabel.Text = _
Range(xVals).Cells(Counter, 1).Offset(0, -1).Value