1

Этот макрос отображает текстовые метки из исходного листа, если дважды щелкнуть точку на диаграмме рассеяния. Диаграмма обновляется, когда исходный лист фильтруется по любому из нескольких столбцов. Макрос должен обнаруживать эту фильтрацию и обновлять значения меток соответственно.

Это работает, когда данные сортируются по отфильтрованному столбцу, но не когда они также сортируются по другому столбцу, по которому данные не сортируются, что вызывает дополнительные скрытые строки.

Проблема в том, что, несмотря на то, что некоторые строки скрыты, макрос считает как видимые, так и скрытые строки, начиная с первой видимой строки (как если бы xlCellTypeVisible не работал).

Для пояснения: до тех пор, пока в отфильтрованном подмножестве нет скрытых строк, метки отображаются правильно, начиная с первой видимой строки в отфильтрованном подмножестве. Однако, когда к столбцу применяется дополнительный фильтр, который не отсортирован по отфильтрованному значению, маркировка искажается из-за подсчета скрытых вкрапленных строк в дополнение к видимым строкам.

Детали: - Начальная строка рассчитывается правильно, какой бы ни была первая видимая строка. - Значение Arg2 также правильно установлено для соответствующей видимой строки в серии, а значения xData и yData на метке являются правильными даже при фильтрации по несортированным строкам! (Таким образом, Arg2 пропускает любые скрытые строки в соответствии с сериями, отображаемыми на графике.)

Но метки из других столбцов неверны.

По сути, мне нужно извлечь текст метки из номера строки Arg2 из диапазона видимых строк на отфильтрованном листе.

Я предполагаю, что проблема в разделе sid = .cells где счетчик использует все строки, а не только видимые строки. Опять же, действительные значения (при отображении с помощью Msgbox) указывают на правильную видимую строку, если я визуально считаю строки на исходном листе. Но фактический текст в метке основан на применении этого счетчика как к скрытым, так и к видимым строкам, поэтому получается неправильная строка, которая находится выше в данных.

Я попытался изменить sid = .cells на sid = r.cells но не повезло, фактически он начинает считать с самой первой строки в данных, а не с первой видимой строки. Похоже, что SpecialCells(xlCellTypeVisible) работает только так, как и ожидалось, при идентификации первой видимой строки, но запутывается в любых последующих скрытых строках.

Любая помощь будет оценена. Я новичок в VBA, поэтому, пожалуйста, будьте ясны / конкретны!

Public WithEvents myChartClass As Chart

Private Sub myChartClass_BeforeDoubleClick(ByVal ElementID As Long, ByVal Arg1 As Long, ByVal Arg2 As Long, Cancel As Boolean)
Dim ser As Series
Dim pt As Point
Dim xData As Double, yData As Double
Dim sid As String

'declare vars used for calculating row number for filtered data
Dim r As Range
Dim StartRow As Long

Cancel = True
For Each ser In Me.SeriesCollection
    ser.HasDataLabels = False
Next

If ElementID = xlSeries Then
    If Arg2 > 0 Then
        With Worksheets("MySheetName")
            Set ser = Me.SeriesCollection(Arg1)
             xData = ser.XValues(Arg2)
             yData = ser.Values(Arg2)
            Set pt = ser.Points(Arg2)

'calculate starting row when table is filtered on any variable
Set r = Worksheets("MySheetName").Range("A:A").Rows.SpecialCells(xlCellTypeVisible)
StartRow = r.Row - 1 'starting row is the first visible row minus the table header

            'grab label from the row associated with the clicked point on chart
            'the case number signifies the series of the chart in the order visible in Select Data chart properties window
            Select Case Arg1
            Case 1  'series 1
                sid = .Cells(Arg2 + StartRow, "D") & vbLf & "label1: " & .Cells(Arg2 + StartRow, "C") & vbLf & "label2: " & .Cells(Arg2 + StartRow, "L") & vbLf & "label3: " & .Cells(Arg2 + StartRow, "U")
            Case 2  'series 2
                sid = .Cells(Arg2 + StartRow, "D") & vbLf & "label1: " & .Cells(Arg2 + StartRow, "C") & vbLf & "label2: " & .Cells(Arg2 + StartRow, "L") & vbLf & "label3: " & .Cells(Arg2 + StartRow, "U")
            End Select

            pt.HasDataLabel = True
            pt.DataLabel.Characters.Font.Size = 11
            pt.DataLabel.Characters.Font.Bold = True
            pt.DataLabel.Text = sid & vbLf & "(" & xData & " , " & yData & ")"

    'MsgBox "r: " & r.Count
    'MsgBox "StartRow: " & StartRow
    'MsgBox "Arg1: " & Arg1
    'MsgBox "Arg2: " & Arg2

        End With
    End If
End If
End Sub
|источник

2 ответа2

1

@ Брайан, еще раз спасибо - ваш ответ и ссылка на Areas привели меня к поиску способов получения номера видимой строки, которая соответствует значению Arg2 в серии.

Провел еще несколько исследований и нашел решение, которое в принципе похоже на другое, но считает количество строк, а не ячеек (http://www.ozgrid.com/forum/showthread.php?t=23611). Это похоже на работу:

Public WithEvents myChartClass As Chart

Private Sub myChartClass_BeforeDoubleClick(ByVal ElementID As Long, ByVal Arg1 As Long, ByVal Arg2 As Long, Cancel As Boolean)
Dim ser As Series
Dim pt As Point
Dim xData As Double, yData As Double
Dim sid As String

'declare vars used for calculating row number for filtered data
Dim rng As Range, rngArea As Range, lRows As Long, lRow2 As Long

Cancel = True
For Each ser In Me.SeriesCollection
    ser.HasDataLabels = False
Next

If ElementID = xlSeries Then
    If Arg2 > 0 Then
        With Worksheets("MySheetName")
            Set ser = Me.SeriesCollection(Arg1)
             xData = ser.XValues(Arg2)
             yData = ser.Values(Arg2)
            Set pt = ser.Points(Arg2)

'check autofilter;
    If Not .AutoFilterMode Then
        MsgBox "Please enable autofilter on source worksheet."
        Exit Sub
    End If

'set a range = to visible cells (excluding the header)
Set rng = .AutoFilter.Range.Offset(1, 0).Resize(.AutoFilter.Range.Rows.Count - 1, 1).SpecialCells(xlCellTypeVisible)

'calculate starting row when table is filtered on any variable;
'loop through areas until row is found;
    lRows = 0
    For Each rngArea In rng.Areas
        lRows = lRows + rngArea.Rows.Count
        If lRows >= Arg2 Then
            lRow2 = rngArea.Item(Arg2 - (lRows - rngArea.Rows.Count)).Row
            Exit For
        End If
    Next rngArea

            'grab label from the row associated with the clicked point on chart
            'the case number signifies the series of the chart in the order visible in Select Data chart properties window
            Select Case Arg1
            Case 1  'series 1
            sid = .Cells(lRow2, "D") & vbLf & "label1: " & .Cells(lRow2, "C") & vbLf & "label2: " & .Cells(lRow2, "L") & vbLf & "label3: " & .Cells(lRow2, "U")
            Case 2  'series 2
            sid = .Cells(lRow2, "D") & vbLf & "label1: " & .Cells(lRow2, "C") & vbLf & "label2: " & .Cells(lRow2, "L") & vbLf & "label3: " & .Cells(lRow2, "U")
            End Select

            pt.HasDataLabel = True
            pt.DataLabel.Characters.Font.Size = 11
            pt.DataLabel.Characters.Font.Bold = True
            pt.DataLabel.Text = sid & vbLf & "(" & xData & " , " & yData & ")"


    'MsgBox "lRow2: " & lRow2

        End With
    End If
End If
End Sub
|источник
1

Ваш диагноз правильный - VBA игнорирует скрытый / скрытый статус при анализе ссылок на ячейки с помощью .Cells Единственное, что я нашел для работы - это метод подсчета грубых сил:

Dim iter As Long, findCount As Long, workCel as Range

' This is okay as long as you are guaranteed only to have one header row.
Set workCel = Worksheets("MySheetName").Cells(2, 1)

' No cells found yet
findCount = 0

' Start iterator at zero
iter = 0

Do  
    ' Check row for hidden status
    If Not workCel.Offset(iter, 0).EntireRow.Hidden Then
        ' Row is visible; increment number of visible rows found
        findCount = findCount + 1
    End If

    ' Increment iterator
    iter = iter + 1

' Stop looping once the number of found rows reaches the desired count
Loop Until findCount >= Arg2

Необходимый индекс должен выпадать из приведенного выше кода в качестве значения iter:

sid = .Cells(iter + StartRow, "D") & vbLf & ...

Никакого пост-декремента не нужно, потому что .Offset(n, 0) относится к n+1 й строке диапазона, начиная с данной ячейки.

Отметим, что .SpecialCells(xlCellTypeVisible) вероятно, работает правильно. Проблема заключается в том, что, поскольку Range «прерывается» различными скрытыми строками, он состоит из нескольких Areas (см. Здесь: http://msdn.microsoft.com/en-us/library/office/ff196243(v=office). 15).aspx). Это полностью взрывается нормально .Cells(...) типу индексации. Он начинается с первой строки данных, потому что строка заголовка не скрыта и, таким образом, привязывает ваш диапазон r .

|источник

Всё ещё ищете ответ? Посмотрите другие вопросы с метками .