Userform code to send values to next available column of next available row

GranSupt 

Here's the improved code for your userform, incorporating the best aspects of both responses and addressing potential issues:

Explanation:

1. Declare variables:

   • ws: Worksheet object for "Fleet Maint Records".
   • lastRow: Last row with data in the first column (assuming column A).
   • colOffset: Offset from column 9 to find the next empty column.

2. Check checkbox value:

   • If PMCheckBox1 is checked (Value = True), proceed.

3. Find last row:

   • Use Range("A" & Rows.Count).End(xlUp).Row to find the last row with data in column A.
   • Add 1 to get the next available row.

4. Find next empty column:

   • Initialize colOffset to 9 (starting column for checkbox data).
   • Use a Do While loop to iterate through columns:
     • Check if the cell value in the current row (lastRow) and current column (colOffset) is empty ("") and if the column offset is still within the table boundaries (colOffset <= ws.ListObjects("FleetMaintTable").ListColumns.Count).
     • If not empty or outside the table, increment colOffset to move to the next column.
   • The loop stops when an empty cell within the table is found.

5. Write checkbox text (with error handling):

   • Check if a suitable column was found (colOffset <= ws.ListObjects("FleetMaintTable").ListColumns.Count).
   • If yes, write the text "ENGINE OIL CHANGED" to the cell.
   • If no, handle the case where no empty column is found within the table boundaries. You can add an optional MsgBox to display an error message or take other appropriate actions.

Improvements:

• Combines the clarity and error handling from both responses.
• Uses ListObjects("FleetMaintTable").ListColumns.Count to ensure the search stays within the table boundaries.
• Provides optional error handling for the case where no empty column is found.
• You can adapt the error handling logic to suit your specific needs.

Repeating for other checkboxes:

• Copy and modify this code for each checkbox, replacing "PMCheckBox1" with the corresponding checkbox name and "ENGINE OIL CHANGED" with the appropriate text for each checkbox.

This refined code addresses the potential issues raised in the ratings and provides a more robust and user-friendly solution for populating your table with selected checkbox values.