将ranged对象用于循环

Question

我正在搜索某个标头，并将其另存为范围对象(FindEQ4)。现在我想使用这个头作为一个动态范围对象的起点。我的问题是，如何才能像Set TestR = .Range("C" & 5 + x)一样使用标头1在C5中的信息

 Sub FindCopyPasteV3()

    With Worksheets("Sheet1").Range("A:FF")

        Dim FindEQ4 As Range
        Dim TestR As Range
        Dim x As Long

        Set FindEQ4 = .Find(What:="Header 1", LookAt:=xlWhole, MatchCase:=True, SearchFormat:=False)

        'I'm looking for something like TestR = .Range("FindEQ4" + x)  
        'which works like Set TestR = .Range("C" & 5 + x) 

    End With

End Sub

​

​

Answer 1

使用Range对象的.Column或.Row属性：

Option Explicit
Sub test()

    Dim FindEQ4  As Range
    Dim StartRow As Long
    Dim StartColumn As Long
    Dim TestR As Range

    With ThisWorkbook.Sheets("Sheet1")
        Set FindEQ4 = .Range("A1")
        StartRow = FindEQ4.Row + 1
        StartColumn = FindEQ4.Column
        Set TestR = .Cells(StartRow, StartColumn) 'A2
    End With

End Sub

Answer 2

使用Range对象的Offset属性。

https://docs.microsoft.com/en-us/office/vba/api/excel.range.offset

这将返回一个从原始数据偏移指定数量的范围。第一个参数是偏移的行数(向下为正，向上为负)，第二个参数为偏移的列数(向右为正，向左为负)。

举个例子

.Range("D7").offset(-3,2)将表示"F4"，向上3行，向右2列。

Sub FindCopyPasteV3()

    With Worksheets("Sheet1").Range("A:FF")

        Dim FindEQ4 As Range
        Dim TestR As Range
        Dim X As Long

        Set FindEQ4 = .Find(What:="Header 1", LookAt:=xlWhole, MatchCase:=True, SearchFormat:=False)


        Set TestR = FindEQ4.Offset(X, 0) 'X rows down, same column



    End With

End Sub