Excel VBA如何实现右侧单元格有文本时左侧自动插入VLOOKUP公式

修正后可直接使用的代码 #

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim SrchRng As Range, cel As Range
    ' 限定检测范围为G21:G27
    Set SrchRng = Me.Range("G21:G27")
    ' 仅当修改的单元格在检测范围内才执行后续逻辑，减少性能消耗
    If Not Intersect(Target, SrchRng) Is Nothing Then
        ' 关闭事件触发，防止写入公式时再次触发Change事件造成死循环
        Application.EnableEvents = False
        For Each cel In SrchRng
            If cel.Value <> "" Then
                ' 直接拼接公式字符串，自动匹配当前行的G列单元格，同时锁定查询区域
                cel.Offset(0, -1).Formula = "=VLOOKUP(G" & cel.Row & ",Data!$P$2:$Q$110,2,FALSE)"
            Else
                ' 右侧单元格为空时清空左侧公式，不需要可直接删除这行
                cel.Offset(0, -1).ClearContents
            End If
        Next cel
        ' 恢复事件触发
        Application.EnableEvents = True
    End If
End Sub

核心修改点说明 #

• 事件名修正：原来的Worksheet_Caps是无效事件，更换为Worksheet_Change，修改工作表内容时会自动触发逻辑实现自动填充
• 范围修正：把原代码的Range("G31:G27")调整为Me.Range("G21:G27")，匹配你需要检测的G21到G27范围，Me指代当前代码所在的工作表，避免范围引用错乱
• 公式写入逻辑修正：
  1. 直接拼接为Excel可识别的公式字符串赋值给Formula属性，自动取当前行的G列单元格作为查询值，不需要手动指定单元格引用
  2. 给查询区域Data!$P$2:$Q$110加上绝对引用符号$，避免公式偏移
• 补全了遗漏的Next cel循环结束语句，同时增加事件开关逻辑，避免写入公式时重复触发事件造成死循环

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