VBA批量添加公式求助：匹配YES的单元格引用无法自动变更

Got it, let's sort this out. The problem with your original code is that you're hardcoding A1 directly into the formula string—so every cell that gets the formula will always reference A1, no matter which row it's in. To make the reference adjust to the current row (like A2 for B2, A3 for B3, etc.), we need to dynamically generate the correct cell reference based on the row of the cell we're working with.

Here's the modified code that fixes this:

Sub AddFormula()
    Dim SrchRng As Range, cel As Range
    Set SrchRng = Range("B1:B20")
    For Each cel In SrchRng
        ' Use UCase to make the match case-insensitive (catches Yes/YES/yes)
        If InStr(1, UCase(cel.Value), "YES") > 0 Then
            ' Dynamically reference the A column cell in the same row
            cel.Formula = "=VLOOKUP(A" & cel.Row & ",H:I,2,0)"
        End If
    Next cel
End Sub

What changed? #

• We replaced the static A1 with A" & cel.Row": cel.Row grabs the row number of the current cell we're looping through. So when the code runs on cell B5, it will insert A5 into the formula, creating =VLOOKUP(A5,H:I,2,0).
• Added UCase(cel.Value) to make the "YES" match case-insensitive—this way it works regardless of how the text is capitalized in your cells.

If you prefer a cleaner way to handle relative references in VBA, you can use the FormulaR1C1 property instead. This syntax uses relative row/column notation, so you don't have to concatenate row numbers manually:

Sub AddFormulaWithR1C1()
    Dim SrchRng As Range, cel As Range
    Set SrchRng = Range("B1:B20")
    For Each cel In SrchRng
        If InStr(1, UCase(cel.Value), "YES") > 0 Then
            ' RC[-1] = current row, column 1 to the left (same row in column A)
            ' C8:C9 = columns H to I (since H is column 8, I is column 9)
            cel.FormulaR1C1 = "=VLOOKUP(RC[-1],C8:C9,2,0)"
        End If
    Next cel
End Sub

This FormulaR1C1 approach is great if you ever need to adjust the column positions later—you won't have to update row number logic, just tweak the column offsets.

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