Adapting VBA script for multiple series

[VBAkgb]

Hello,

Should be an easy one.

Struggling to adapt this macro for multiple vba scripts:

https://support.microsoft.com/en-gb...-labels-to-data-points-in-an-xy-scatter-chart

Any ideas and what data form it should be in?

Cheers in advance!

 

[VBAkgb]

Still struggling, any ideas?

 

[John_w]

To continue the MS example, put more y value column(s) of data in columns D, E, etc. and add the multiple series chart. Then run this macro:

Public Sub AttachLabelsToPoints2()

    'Dimension variables.
    Dim Counter As Integer, ChartName As String, xVals As String
    Dim n As Integer
    
    ' Disable screen updating while the subroutine is run.
    Application.ScreenUpdating = False
    
    'For each series in the chart
    
    For n = 1 To ActiveChart.SeriesCollection.Count
    
        'Store the formula for the nth series in "xVals".
        xVals = ActiveChart.SeriesCollection(n).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 this series
        For Counter = 1 To Range(xVals).Cells.Count
            ActiveChart.SeriesCollection(n).Points(Counter).HasDataLabel = True
            ActiveChart.SeriesCollection(n).Points(Counter).DataLabel.Text = Range(xVals).Cells(Counter, 1).Offset(0, -1).Value
        Next Counter
    
    Next

End Sub

 

[VBAkgb]

Appreciate it. Good way to do it and better than the one I came up with today. I just added multiple series and loops.

One other issue for anyone who can think of a way round my problem. The labelling macro works perfectly however I have a huge data set and sort and filter the points to suit the application. Unfortunately the labelling macro isn't as dynamic and doesn't change with the filtering process. Maybe this simply isn't fixable. Applying the macro after the filter is applied causes an error.

Any help would be much appreciated -final piece of the puzzle hopefully!!

To further explain. It labels from the data in the first row in the data series everytime even though it will be hidden and row 3000 may be only one showing.

 

[VBAkgb]

I've tried using the special cells formula but with no luck.

SpecialCells(xlCellTypeVisible)

 

[VBAkgb]

Sub AttachLabelsToPoints()

    

     'Dimension variables.

    Dim Counter As Integer, ChartName As String, xVals As String

    Dim lngChtCounter As Long

   

     '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.

     With ActiveChart.SeriesCollection(1)

        For Counter = 1 To Range(xVals).Cells.Count

            If Not Range(xVals).Cells(Counter, 1).EntireRow.Hidden Then

                lngChtCounter = lngChtCounter + 1

                .Points(lngChtCounter).HasDataLabel = True

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

            End If

        Next Counter

    End With

End Sub

Tried this that was posted on another forum but did not work for me. Might be a good starting point though. This should test if row is visible?

 

[VBAkgb]

This may be because I have #N/A cells and it stops carrying out code once it reaches that first cell.