Excel VBA：根据D列数据范围自动复制E1单元格公式至对应E列区域

Hey there! Since you're new to VBA, let's walk through a straightforward solution that does exactly what you need—no confusing jargon, just clear steps and code you can use right away.

The Core Idea #

We need two key things for this task:

• Find the last row in column D that has data (so we know how far down to fill the formula)
• Copy the formula from E1 and paste it into all rows from E2 down to that last row

Full VBA Code #

Here's the code you can use—just copy and paste it into a VBA module:

Sub FillFormulaToMatchDRows()
    ' Declare a variable to store the last row number in column D
    Dim lastRowInD As Long
    
    ' Find the last non-empty row in column D
    lastRowInD = Cells(Rows.Count, "D").End(xlUp).Row
    
    ' Only fill the formula if there are rows below E1 (i.e., D has more than just D1)
    If lastRowInD > 1 Then
        ' Apply E1's formula to the range E2 through E[lastRowInD]
        Range("E2:E" & lastRowInD).Formula = Range("E1").Formula
    End If
End Sub

How This Works (Line by Line) #

• Dim lastRowInD As Long: Creates a variable to hold the row number of the last cell with data in column D.
• Cells(Rows.Count, "D").End(xlUp).Row: This is like manually clicking the last cell in column D and pressing Ctrl + Up—it jumps straight to the last cell with data, so we don't have to guess how many rows there are.
• The If statement: Makes sure we don't try to fill E2 if there's only D1 with data (avoids errors).
• Range("E2:E" & lastRowInD).Formula = Range("E1").Formula: This copies the formula (not just the value) from E1 and applies it to all the rows below. It's better than copying/pasting because it's faster and preserves the formula's relative references (like if E1 uses D1, E2 will automatically use D2, etc.).

How to Use This Code #

1. Open your Excel workbook.
2. Press Alt + F11 to open the VBA Editor.
3. Right-click your workbook name in the Project Explorer (left side) > Insert > Module.
4. Paste the code into the blank module that opens.
5. Press F5 to run the macro, or close the editor and run it from the Developer tab (if you don't see the Developer tab, go to File > Options > Customize Ribbon and check the box for Developer).

Quick Notes #

• If your data in column D has blank cells in between, this will still work—it finds the very last cell with data in column D.
• This preserves relative cell references in your formula. For example, if E1 has =D1*2, E2 will become =D2*2, E3 =D3*2, etc.—exactly what you want!

Let me know if you hit any snags while setting this up—I’m happy to help troubleshoot.

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