Excel宏遍历行需求：能否通过偏移行+1执行代码并实现指定功能？

Absolutely, this is totally feasible! Adjusting the row parameter in your offset by +1 is a straightforward way to iterate through rows and hit your two goals. Let’s break down how to make this work step by step:

1. Locate the "Wage QRE Exp" Column & Target Values Below #

First, we’ll build logic to spot the right column by checking if the cell above a target cell has "Ref." in its left neighbor. Here’s how to code that:

Sub LocateWageQREExp()
    Dim ws As Worksheet
    Dim cell As Range
    Dim targetCol As Range
    
    Set ws = ActiveSheet ' Or specify your sheet, e.g., Sheets("QRE_Data")
    
    ' Loop through used cells to find the target column
    For Each cell In ws.UsedRange
        ' Check if the cell above has "Ref." in its left adjacent cell
        If cell.Row > 1 And cell.Offset(-1, -1).Value = "Ref." Then
            Set targetCol = cell.EntireColumn
            MsgBox "Found 'Wage QRE Exp' column at: " & targetCol.Address
            
            ' Now search for values below this cell using +1 row offset
            Dim searchCell As Range
            Set searchCell = cell.Offset(1, 0)
            
            ' Keep moving down until we hit an empty cell
            Do While searchCell.Value <> ""
                Debug.Print "Found value: " & searchCell.Value
                Set searchCell = searchCell.Offset(1, 0) ' Increment row offset by 1 each time
            Loop
            Exit For ' Exit once we locate the target column
        End If
    Next cell
End Sub

The core trick here is Offset(1, 0)—this lets you jump down one row automatically, exactly the "+1 row parameter" you wanted.

2. Apply Macro to Rows Between Gray Rows in 'QRE' Column #

Next, we’ll target rows between gray rows in the 'QRE' column, filling page references in the left adjacent cell until we hit a termination condition (like the next gray row or empty cell):

Sub FillPageReferencesBetweenGrayRows()
    Dim ws As Worksheet
    Dim qreCol As Range
    Dim currentCell As Range
    Dim grayColorIndex As Integer
    Dim pageRef As String
    
    Set ws = ActiveSheet
    grayColorIndex = 15 ' Adjust this to match your gray's ColorIndex (15 = standard light gray)
    pageRef = "Page 45" ' Replace with dynamic page logic if needed
    
    ' First, find the 'QRE' column (adjust header text if yours differs)
    Set qreCol = ws.Rows(1).Find(What:="QRE", LookIn:=xlValues, LookAt:=xlWhole).EntireColumn
    
    ' Start from the first row below the header
    Set currentCell = qreCol.Cells(2, 1)
    
    ' Loop through all used rows
    Do While currentCell.Row <= ws.UsedRange.Rows.Count
        ' Skip gray rows, start filling when we hit a non-gray row
        If currentCell.Interior.ColorIndex <> grayColorIndex Then
            Dim fillCell As Range
            Set fillCell = currentCell.Offset(0, -1) ' Target left adjacent cell
            
            ' Fill page refs until we hit a gray row or empty cell
            Do While currentCell.Interior.ColorIndex <> grayColorIndex And currentCell.Value <> ""
                fillCell.Value = pageRef
                ' Move down one row with +1 offset
                Set currentCell = currentCell.Offset(1, 0)
                Set fillCell = fillCell.Offset(1, 0)
            Loop
        Else
            ' Move past the gray row to the next one
            Set currentCell = currentCell.Offset(1, 0)
        End If
    Loop
End Sub

Here, we use Offset(1, 0) repeatedly to iterate through rows, stopping as soon as we hit a gray row (your termination condition). You can tweak the pageRef variable to pull dynamic page numbers if you need them.

Quick Adjustment Tips: #

• To find your exact gray ColorIndex, select a gray cell and run Debug.Print Selection.Interior.ColorIndex in the VBA Immediate Window.
• If your headers are in a row other than row 1, adjust the starting row (e.g., change Cells(2,1) to Cells(3,1)).
• Add error handling (like On Error Resume Next) to avoid crashes if headers aren’t found.

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