如何用VBA的.Find方法返回目标行的后续全部值

我需要提取包含“LAB NO.”的整行中，该单元格之后的所有值。目前用.Find方法找到了“LAB NO.”所在的行号，但不知道怎么获取该行中这个单元格后面的全部内容。

现有代码：

Sub SearchMe()

Dim sheet As Worksheet
Set sheet = ActiveWorkbook.Sheets(1)
Dim findme As String

findme = "LAB NO."

Dim search As Range
Dim found As Range

Dim lastrow As Long
lastrow = sheet.UsedRange.Rows.Count
Dim i As Long

For i = 3 To lastrow
    Set search = sheet.Cells(i, 1).EntireRow
    
    If Not search.Find(what:=findme, lookat:=xlWhole) Is Nothing Then
    
    Set found = search.Find(what:=findme, lookat:=xlWhole)
    
    Debug.Print found.Row
       
    End If

Next i

End Sub

我试过用.Row属性创建数组，比如Dim r(found.row) as Variant，但这种方法行不通。我是VBA新手，还没学到数组和列表的相关内容。

方法1：逐列遍历输出（适合新手理解） #

找到“LAB NO.”的单元格后，从它的下一列开始，逐列读取内容直到遇到空单元格，逻辑简单易上手：

修改后的代码：

Sub SearchMe()
    Dim sheet As Worksheet
    Set sheet = ActiveWorkbook.Sheets(1)
    Dim findme As String
    findme = "LAB NO."
    
    Dim found As Range
    Dim lastrow As Long
    Dim currentCol As Long
    lastrow = sheet.UsedRange.Rows.Count
    
    For i = 3 To lastrow
        ' 查找当前行中的LAB NO.单元格
        Set found = sheet.Rows(i).Find(what:=findme, lookat:=xlWhole)
        
        If Not found Is Nothing Then
            Debug.Print "行" & found.Row & "的后续值："
            currentCol = found.Column + 1 ' 从目标单元格的下一列开始
            
            ' 循环读取直到单元格为空
            Do While sheet.Cells(found.Row, currentCol).Value <> ""
                Debug.Print sheet.Cells(found.Row, currentCol).Value
                currentCol = currentCol + 1
            Loop
        End If
    Next i
End Sub

方法2：一次性获取后续所有非空单元格（效率更高） #

用End(xlToRight)快速定位该行最后一个非空单元格，直接获取后续所有值的范围，适合后续复制或批量操作：

Sub SearchMe()
    Dim sheet As Worksheet
    Set sheet = ActiveWorkbook.Sheets(1)
    Dim findme As String
    findme = "LAB NO."
    
    Dim found As Range
    Dim lastrow As Long
    Dim targetRange As Range
    lastrow = sheet.UsedRange.Rows.Count
    
    For i = 3 To lastrow
        Set found = sheet.Rows(i).Find(what:=findme, lookat:=xlWhole)
        
        If Not found Is Nothing Then
            ' 获取从目标单元格下一列到该行最后一个非空单元格的范围
            Set targetRange = sheet.Range(found.Offset(0, 1), sheet.Cells(found.Row, sheet.Columns.Count).End(xlToLeft))
            
            Debug.Print "行" & found.Row & "的后续值："
            ' 遍历范围输出所有值
            For Each cell In targetRange
                Debug.Print cell.Value
            Next cell
        End If
    Next i
End Sub

关键提示 #

1. 原代码重复执行了两次Find操作，合并成一次能减少冗余，提升效率
2. 新手不用急于学习数组，先掌握Column属性、Offset方法和范围操作这些基础内容
3. Offset(0,1)表示当前单元格向右偏移1列，是获取相邻右侧单元格的常用方法
4. End(xlToLeft)从行的最后一列往左查找，能快速定位该行的有效数据末尾

内容的提问来源于stack exchange，提问作者Monika