求助：修改Excel VBA代码实现输入数据自动保存至下一行

Got it, let's tweak your VBA code so each new training entry gets saved to the next empty row in columns J, K, and L—no more overwriting the same cells!

Modified Code #

Private Sub NewTraining_Click()
    Dim nextRow As Long
    Dim trainedContent As String
    Dim trainingDate As String
    Dim trainingLocation As String
    
    ' Capture all user inputs first (avoids partial entries if user cancels mid-way)
    trainedContent = InputBox("What Did You Train?")
    If trainedContent = "" Then Exit Sub ' Exit if user cancels or enters nothing
    
    trainingDate = InputBox("Date?")
    If trainingDate = "" Then Exit Sub
    
    trainingLocation = InputBox("Location?")
    If trainingLocation = "" Then Exit Sub
    
    ' Find the next empty row in column J (starts right after the last used row)
    nextRow = Cells(Rows.Count, "J").End(xlUp).Row + 1
    
    ' Write the collected inputs to the new row
    Cells(nextRow, "J").Value = trainedContent
    Cells(nextRow, "K").Value = trainingDate
    Cells(nextRow, "L").Value = trainingLocation
End Sub

Key Changes Explained #

• Dynamic row targeting: Cells(Rows.Count, "J").End(xlUp).Row finds the last row with data in column J. Adding +1 gives us the first empty row below it—this ensures we always write to the next available spot, no matter how many entries you already have.
• Input safeguards: Added checks to exit the sub if the user cancels an InputBox or leaves it blank. This prevents writing incomplete/empty values to your sheet.
• Clearer variable names: Renamed the generic UserValue to specific names like trainedContent to make the code easier to read and debug later.

Bonus Adjustment (If You Have a Header Row) #

If row 17 is a header (e.g., "Training", "Date", "Location"), you can make sure the first entry always starts at row 18 even if column J is empty:

nextRow = WorksheetFunction.Max(Cells(Rows.Count, "J").End(xlUp).Row + 1, 18)

This guarantees you won't accidentally write above your header.

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