VBA实现指定区域首个空单元格定位与填充需求问询

Let’s update your code to dynamically locate the first empty cell in each of the three revision sections instead of using hardcoded ranges. We’ll keep your existing date-comparison loop intact while adding the dynamic positioning logic you need.

Full Updated Code #

Sub UpdateRevisionLog()
    Dim ws As Worksheet
    Dim lastRevRow As Long
    Dim revLvlRange As Range, revDateRange As Range, revNotesRange As Range
    Dim i As Integer, j As Integer, cRow As Integer
    Dim revNotesInput As String
    Dim currentRevLvl As Variant
    
    ' Set reference to the target worksheet for cleaner code
    Set ws = ThisWorkbook.Sheets("Monthly Status")
    
    ' --- Dynamically find the first empty row in the revision sections ---
    ' Get last used row in the Revision Level column (column C)
    lastRevRow = ws.Range("C" & ws.Rows.Count).End(xlUp).Row
    ' Adjust to start at row 82 if no revisions exist yet
    If lastRevRow < 82 Then
        lastRevRow = 82
    Else
        lastRevRow = lastRevRow + 1
    End If
    ' Prevent going beyond the max allowed row (91)
    If lastRevRow > 91 Then
        MsgBox "No more empty rows left in the revision log!", vbExclamation
        Exit Sub
    End If
    
    ' Assign dynamic ranges for each revision field
    Set revLvlRange = ws.Range("C" & lastRevRow & ":F" & lastRevRow)
    Set revDateRange = ws.Range("G" & lastRevRow)
    Set revNotesRange = ws.Range("H" & lastRevRow & ":R" & lastRevRow)
    
    ' --- Your original date comparison loop (unchanged core logic) ---
    For i = 0 To 49
        cRow = i + 23
        For j = 0 To 2
            If ws.Cells(cRow, j + 11) <> ws.Cells(cRow, j + 37) Then
                ' Populate revision date with today's date
                revDateRange.Value = Date
                ' Get revision notes via input box, with validation
                revNotesInput = InputBox("You changed dates, you must input notes for this revision!", "Revision Notes")
                If revNotesInput = "" Then
                    MsgBox "Revision notes cannot be empty. Operation cancelled.", vbCritical
                    Exit Sub
                End If
                revNotesRange.Value = revNotesInput
                
                ' Calculate and set revision level
                currentRevLvl = ws.Range("C" & lastRevRow - 1).Value
                If IsEmpty(currentRevLvl) Then
                    ' Start at 1 if it's the first revision
                    revLvlRange.Value = 1
                Else
                    ' Increment from the last existing revision level
                    revLvlRange.Value = currentRevLvl + 1
                End If
                
                ' Perform copy/paste with screen updating disabled for speed
                Application.ScreenUpdating = False
                ws.Range("K23:M72").Copy
                ws.Range("AK23:AM72").PasteSpecial xlPasteValues
                Application.CutCopyMode = False ' Clear clipboard after paste
                Application.ScreenUpdating = True
                
                ' Exit after first mismatch is found (matches original logic)
                Exit Sub
            End If
        Next j
    Next i
End Sub

Key Changes Explained #

• Dynamic Empty Row Detection: We use Range("C" & ws.Rows.Count).End(xlUp).Row to find the last used row in the Revision Level column, then add 1 to get the first empty row. We also add a check to stay within the 82-91 row limit you specified.
• Revision Level Handling: If it's the first revision (C82 is empty), we start at 1. For subsequent revisions, we grab the previous revision level and increment by 1 automatically.
• InputBox Validation: We added a check to ensure the user doesn't cancel or leave the notes empty, which prevents incomplete revision entries.
• Cleaner Worksheet Reference: A single ws variable replaces repeated Sheets("Monthly Status") calls, making the code easier to maintain.
• Copy/Paste Optimization: Added Application.CutCopyMode = False to clear the clipboard after pasting, which is a best practice to avoid lingering copy states.

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