VBA宏开发：向表格最后空列有效行批量插入公式的问题

Hey there! I see you're working through getting your VBA macro to target only the valid rows in the last empty column—let's break down what went wrong with your previous attempts and fix this properly.

First, Let's Diagnose Your Earlier Code Issues #

1. The single-cell formula problem: When you used With .Columns(lastcol) and lrow = .range("A" & .Rows.Count).End(xlUp).Row, you were actually looking for the last row within the last column (not the entire dataset). If that column was empty, lrow would default to 1, which is why only K1 got the formula.
2. The runtime error 1004: Your attempt to use .range(lastcol & .range("A" & .Rows.Count).End(xlUp).Rows) failed because lastcol is a numeric column index (like 11 for column K), not a letter string. You can't directly concatenate numbers with row references like that—you need to use Cells() to define ranges numerically.

The Correct Approach: Target Valid Rows Explicitly #

Here's a revised code snippet that properly targets only the valid rows in the last empty column, no hardcoding required:

Sub AddVLookupToLastColumn()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim lastCol As Long
    Dim fvlookup As String
    Dim targetRange As Range
    
    ' Use a specific worksheet instead of ActiveSheet to avoid errors
    Set ws = ThisWorkbook.Sheets("YourDataSheetName") ' Replace with your sheet name
    
    With ws
        ' Get the last row with data (using column A as the anchor—adjust if needed)
        lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        
        ' Get the first empty column after the last used column
        lastCol = .Cells(1, .Columns.Count).End(xlToLeft).Offset(0, 1).Column
        
        ' Build your VLOOKUP formula (adjust @1 reference to match your lookup value range)
        fvlookup = "=VLOOKUP(@1,@2,@3,FALSE)"
        fvlookup = Replace(fvlookup, "@1", .Range("A2:A" & lastRow).Address) ' Assume lookup values start at A2 (skip header)
        fvlookup = Replace(fvlookup, "@2", "[LookupFile.csv]LookupFile!$B:$I")
        fvlookup = Replace(fvlookup, "@3", "5")
        
        ' Define the target range: last empty column, from row 2 to last valid row (skip header)
        Set targetRange = .Range(.Cells(2, lastCol), .Cells(lastRow, lastCol))
        
        ' Apply the formula to the target range
        targetRange.Formula = fvlookup
    End With
End Sub

Key Notes About This Code: #

• Specific worksheet reference: Using ThisWorkbook.Sheets("YourDataSheetName") instead of ActiveSheet prevents unexpected behavior if the wrong sheet is active when running the macro.
• Anchor lastRow to a data-filled column: We use column A to find the last valid row because it's likely your primary column with no gaps. Adjust the column letter here if your anchor column is different.
• Skip headers (optional): If your data has a header row, we start the formula at row 2. If you don't have headers, change .Cells(2, lastCol) to .Cells(1, lastCol).

Bonus: Handling #N/A Values and Moving to "JEs" Sheet #

Once the formula is applied, you can filter and copy only the rows with #N/A values to your JEs sheet without copying empty rows:

' Add this inside the With ws block after applying the formula
With .Columns(lastCol)
    ' Turn on autofilter for the formula column
    .AutoFilter Field:=1, Criteria1:="#N/A"
    
    ' Copy visible rows (skip header) to JEs sheet
    .Offset(1).SpecialCells(xlCellTypeVisible).EntireRow.Copy _
        Destination:=ThisWorkbook.Sheets("JEs").Cells(Rows.Count, 1).End(xlUp).Offset(1)
    
    ' Turn off autofilter
    .AutoFilter
End With

This way, only the rows with actual #N/A errors get copied over—no extra empty rows cluttering your JEs sheet.

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