Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro recorded... tabs & file names changed, macro hangs | Excel Worksheet Functions | |||
Macro Help Needed - Excel 2007 - Print Macro with Auto Sort | Excel Worksheet Functions | |||
how to count/sum by function/macro to get the number of record to do copy/paste in macro | Excel Programming | |||
macro to delete entire rows when column A is blank ...a quick macro | Excel Programming | |||
Start Macro / Stop Macro / Restart Macro | Excel Programming |