求助：VBA实现VLOOKUP仅生效单个单元格，需批量应用至整列

The problem with your current code is that it only assigns the formula to cell C2 specifically. To extend it to all relevant rows below your header, you have a couple of straightforward, efficient options:

Option 1: Batch Assign the Formula to the Entire Range (Most Efficient) #

This method writes the formula to all target cells in one go, which is faster than filling row-by-row, especially with large datasets.

Sub ApplyVLOOKUPToColumn()
    Dim ESheet As Worksheet
    Dim lastRow As Long
    
    ' Replace "YourSheetName" with the actual name of your worksheet (e.g., "Sheet1")
    Set ESheet = ThisWorkbook.Worksheets("YourSheetName")
    
    ' Find the last row with data in column B (since VLOOKUP uses B column values)
    lastRow = ESheet.Cells(ESheet.Rows.Count, "B").End(xlUp).Row
    
    ' Assign the formula to all cells from C2 down to the last data row
    ESheet.Range("C2:C" & lastRow).Formula = "=VLOOKUP(B2, Roles!$A:$B, 2, FALSE)"
End Sub

How it works: #

• We first find the last row with data in column B using End(xlUp)—this ensures we only fill the formula for rows that actually have a lookup value.
• When we assign the formula to the range C2:C[lastRow], Excel automatically adjusts the relative reference B2 to B3, B4, etc., for each row—no extra work needed!

Option 2: Use AutoFill (Mimics Manual Drag-and-Drop) #

If you prefer to replicate the manual "fill handle" behavior, this method sets the formula in C2 first, then fills it down to the last row.

Sub AutoFillVLOOKUP()
    Dim ESheet As Worksheet
    Dim lastRow As Long
    
    Set ESheet = ThisWorkbook.Worksheets("YourSheetName")
    lastRow = ESheet.Cells(ESheet.Rows.Count, "B").End(xlUp).Row
    
    ' Set the base formula in C2
    ESheet.Range("C2").Formula = "=VLOOKUP(B2, Roles!$A:$B, 2, FALSE)"
    ' AutoFill from C2 down to the last data row
    ESheet.Range("C2").AutoFill Destination:=ESheet.Range("C2:C" & lastRow), Type:=xlFillDefault
End Sub

Key Notes: #

• Always use specific worksheet references (like ThisWorkbook.Worksheets("YourSheetName")) instead of ActiveSheet to avoid errors if the wrong sheet is active.
• The Roles!$A:$B reference uses absolute column references ($), which is correct because your lookup range shouldn't shift as we fill the formula down.
• If your column B has blank rows in the middle, End(xlUp) will stop at the last non-blank cell above the bottom of the sheet. If you need to include all rows even with blanks, you can adjust how you calculate lastRow (e.g., using UsedRange.Rows.Count, but be cautious as UsedRange can include empty cells that were previously edited).

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