基于F列日期判断将D列内容剪切至E列的VBA实现需求

Perfect, I’ve got a straightforward VBA macro that does exactly what you need—no formulas involved, so it’ll play nicely with your other VBA tasks. Here’s how it works:

The Macro Code #

Sub MoveDToEWhenFHasDate()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long
    
    ' Set the worksheet (change "Sheet1" to your actual sheet name)
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    
    ' Find the last row with data in column D (since D2+ has data)
    lastRow = ws.Cells(ws.Rows.Count, "D").End(xlUp).Row
    
    ' Loop through each row starting from row 2
    For i = 2 To lastRow
        ' Check if column F has a valid date (ignores blanks/non-dates)
        If IsDate(ws.Cells(i, "F").Value) Then
            ' Cut D column value to E column, clears original D cell automatically
            ws.Cells(i, "D").Cut Destination:=ws.Cells(i, "E")
        End If
    Next i
    
    MsgBox "Task completed successfully!", vbInformation
End Sub

How It Works #

• Worksheet Targeting: We explicitly define the worksheet to avoid issues with the active sheet—just replace "Sheet1" with your actual sheet name.
• Efficient Row Range: We find the last row with data in column D so we don’t loop through empty rows unnecessarily.
• Date Check: The IsDate() function ensures we only act on cells in column F that contain valid dates (blanks and non-date values are skipped).
• Cut Operation: Using .Cut directly moves the value from D to E and clears the original D cell, which matches your "cut" requirement perfectly.

How to Use This #

1. Press Alt + F11 to open the VBA Editor.
2. Right-click your workbook in the Project Explorer > Insert > Module.
3. Paste the code into the new module.
4. To run it, either press F5 while in the module, or call it from another macro using MoveDToEWhenFHasDate.

Example Breakdown #

Original Data

C   D       E       F
MFR Cat#    IVC#    Date
MMM 1       MMM     721490  10/19/17
MMM 2       MMM     723355  10/20/17
MMM         702107          10/20/17
MMM         702645          10/21/17
MMM 3       
MMM 4       MMM     745824  10/12/17

After Running the Macro

C   D       E       F
MFR Cat#    IVC#    Date
MMM         1       MMM     721490  10/19/17
MMM         2       MMM     723355  10/20/17
MMM         702107          10/20/17
MMM         702645          10/21/17
MMM 3       
MMM         4       MMM     745824  10/12/17

Note: If you need to handle cases where column E already has data, we can adjust the code to overwrite or handle that—just let me know!

内容的提问来源于stack exchange，提问作者T.Smith