如何用VBA实现从G列最后含VLOOKUP公式单元格下拉至F列最后填充单元格？

Since your Excel file is dynamic, we need a precise way to locate the two key positions: the last cell in column G that contains a VLOOKUP formula, and the last non-empty cell in column F. Here’s a robust, tested solution that handles this reliably:

Step 1: Use a VBA Macro for Automated Formula Fill #

This macro dynamically finds the required range and extends your VLOOKUP formula correctly every time you run it. It’s perfect for dynamic files where row counts change regularly.

Sub ExtendVLOOKUPToLastF()
    Dim ws As Worksheet
    Dim lastGFormulaRow As Long
    Dim lastFRow As Long
    Dim formulaRange As Range
    
    ' Target your specific worksheet (update the name if needed)
    Set ws = ThisWorkbook.Sheets("YourSheetName")
    
    ' Find the last row in column G with a VLOOKUP formula (scans from bottom up)
    lastGFormulaRow = 0
    For i = ws.Cells(ws.Rows.Count, "G").End(xlUp).Row To 1 Step -1
        If InStr(1, ws.Cells(i, "G").Formula, "VLOOKUP", vbTextCompare) > 0 Then
            lastGFormulaRow = i
            Exit For
        End If
    Next i
    
    ' Handle case where no VLOOKUP exists in column G
    If lastGFormulaRow = 0 Then
        MsgBox "No VLOOKUP formula found in column G!", vbExclamation
        Exit Sub
    End If
    
    ' Get the last non-empty row in column F
    lastFRow = ws.Cells(ws.Rows.Count, "F").End(xlUp).Row
    
    ' Exit if there are no new rows to fill
    If lastFRow <= lastGFormulaRow Then
        MsgBox "Column F has no new rows to populate!", vbInformation
        Exit Sub
    End If
    
    ' Extend the formula using Excel's native AutoFill
    Set formulaRange = ws.Range(ws.Cells(lastGFormulaRow + 1, "G"), ws.Cells(lastFRow, "G"))
    ws.Cells(lastGFormulaRow, "G").AutoFill Destination:=formulaRange, Type:=xlFillDefault
    
    MsgBox "VLOOKUP formulas extended successfully to row " & lastFRow & "!", vbInformation
End Sub

Step 2: Key Macro Details #

• Precise formula start point: Scanning column G from bottom to top ensures we grab the most recent VLOOKUP cell, even if there are blank cells above it.
• Dynamic row detection: Uses End(xlUp) to find column F’s last filled cell—this works even if there are occasional blanks in F (though contiguous data is still ideal for reliability).
• Reference preservation: The AutoFill method carries over your VLOOKUP’s relative/absolute cell references exactly like manually dragging the fill handle.

Step 3: Optional - Auto-Trigger on Worksheet Changes #

If you want this to run automatically whenever new data is added to column F, add this worksheet event code to your sheet’s code module:

Private Sub Worksheet_Change(ByVal Target As Range)
    ' Only trigger when changes happen in column F
    If Not Intersect(Target, Me.Columns("F")) Is Nothing Then
        ' Run the macro we created
        ExtendVLOOKUPToLastF
    End If
End Sub

Quick Usage Tips #

• Enable macros when opening the file (VBA requires macro permissions).
• Test the macro on a copy of your file first to confirm it works with your specific VLOOKUP structure.
• Replace "YourSheetName" in the macro with your actual worksheet name to avoid targeting the wrong sheet.

内容的提问来源于stack exchange，提问作者Bálint Kása