Vba Loop над несмежным диапазоном

У меня есть несмежный диапазон строк (пример адреса myRange: $2: $2, $4: $205, $214: $214), и я хотел бы получить доступ к определенной строке и столбцу в пределах диапазона. Я пробовал следующее:

'Получите значение второй строки, 1-й столбец в пределах диапазона

myRange.rows(2).Cells(, 1).Value

Тем не менее, это дает мне значение 2-й строки в WorkSheet, а НЕ в диапазоне - это означает, что он дает мне адрес $3 $1 - а не $4 $1

Может кто-нибудь объяснить, как я могу получить доступ к значениям внутри моего диапазона? (Возможно, это связано с разными областями)

Спасибо

Ответ 1

Вот мои записи - не обязательно лучше, чем Irwin's

Function GetValue(rInput As Range, Row As Long, Column As Long) As Variant

    Dim rArea As Range
    Dim lCumRows As Long
    Dim lActualRow As Long

    For Each rArea In rInput.Areas
        lCumRows = lCumRows + rArea.Rows.Count
        If Row <= lCumRows Then
            lActualRow = rArea.Rows(1).Row + (Row - (lCumRows - rArea.Rows.Count + 1))
            Exit For
        End If
    Next rArea

    If lActualRow > 0 Then
        GetValue = rInput.Parent.Cells(lActualRow, Column).Value
    End If

End Function

Function GetValue2(rInput As Range, Row As Long, Column As Long) As Variant

    Dim rRow As Range
    Dim lRowCnt As Long

    For Each rRow In rInput.Rows
        lRowCnt = lRowCnt + 1
        If lRowCnt = lrow Then
            GetValue2 = rRow.Cells(1, Column).Value
            Exit For
        End If
    Next rRow

End Function

И переходите читать http://www.dailydoseofexcel.com/archives/2004/07/07/the-strange-object/ для понимания того, почему Excel ведет себя таким образом.

И тестовый процесс, если вы заинтересованы

Sub test()

    Dim myRange As Range

    Set myRange = Union(Rows(2), Range("4:205"), Rows(214))

    Debug.Print GetValue(myRange, 1, 2), GetValue(myRange, 1, 2)
    Debug.Print GetValue(myRange, 2, 2), GetValue(myRange, 2, 2)
    Debug.Print GetValue(myRange, 3, 2), GetValue(myRange, 3, 2)
    Debug.Print GetValue(myRange, 200, 2), GetValue(myRange, 200, 2)

End Sub

Ответ 2

Я думаю, что вы хотите, чтобы VBA делал это, чтобы увидеть ваш несмежный диапазон как смежный. Я не думаю, что подход, который вы принимаете, будет работать. Вам придется относиться к этому, как к смешанным диапазонам multipe. Следующий код должен начать вас. Если rowSelection - это строка в интересующем вас диапазоне. Если вы введете 2, она будет выбирать строку 4 в книге, так как она является второй строкой в вашем диапазоне.

Sub Macro1()

    Dim rowCounter As Long
    Dim rowSelection As Long

    rowSelection = 2
    For Each Rng In Range("A2:A2,A4:A205,A214:A214").Areas
         If Rng.Rows.Count >= rowSelection Then
            Rng.Rows(rowSelection - rowCounter).Cells(1, 1).Select
            End
         Else
            rowCounter = rowCounter + Rng.Rows.Count
         End If
    Next Rng

End Sub

Ответ 3

Этот код повторяется через именованный диапазон:

Dim c As Range

x=0

For Each c In Range("MyNamedRange")

    'if x = pick a number and do something here

    MsgBox c.Address & vbTab & c.Value

x=x+1

Next c

Ответ 4

Спасибо всем за их ответы. Прежде чем я увидел эти ответы, я сам это понял и до сих пор работает. Я не скажу, что это самый эффективный метод, но, похоже, работает:

 Public Function NextRow(index As Integer, rows As Range) As Range
    Dim i As Integer, r As Range
    i = 1
    Set NextRow = Nothing
    For Each r In rows.rows
        If i = index Then
            Set NextRow = Range(r.Address)
            Debug.Print "NextRow: " & NextRow.Address
            Exit Function
        End If

        i = i + 1
    Next r

End Function

Похоже, что второй ответ - в основном я продвигаюсь к диапазону индексов, с которыми хочу работать, и чем я возвращаю диапазон, заданный по адресу (! important)

I, чем называть это следующим образом:

NextRow(2, myRange).Cells(,1).value