VBA – Excel table skipping rows when using a user form for data entry

Good Morning,

I am working on an excel sheet, on call schedule and have run into a bit of a problem. Please see the below paragraph for my end goal, and the second paragraph for my current issue.

I am trying to create an on call schedule where employees are scheduled for 7 consecutives days on rotation. For example if we have 4 staff, staff member one would be on call for 7 days, then be off for 21 days while staff 2, 3 and 4 are on call, before being back on call for 7 days. I have the formula figured out for that portion, but what I am now trying to do is make it dynamic, where employees can be added/ removed and the formula updates to reflect changes.

My current plan is to use a table to track employees. I am using a user form to add employees to a table. When an employee is added, they also get a number to be associated with. In the above example of four employees, I would number them 1 to 4 in a separate column. My current issue is, my add to table function keeps skipping cells. I am by no means a good programmer, and am not sure why this is happening. Would some one be able to explain what is going on.

From the couple of tests I did, my program seems to correctly fill the first row of the table, then skips a cell for the second entry. It then skips 4 cells for the third value, and when I try and enter a 4 or 5th value, the third value is replaced. If some one could assist me in figuring out what is wrong, it would be greatly appreciated.

Sub AddDataRow(tableName As String, value As Variant)
    Dim lastRow As Range
    Dim sheet As Worksheet
    Dim table As ListObject
    Dim col As Integer

    Set sheet = ActiveWorkbook.Worksheets("Jobs and Shifts")
    Set table = sheet.ListObjects.Item(tableName)

    'First check if the last row is empty; if not, add a row
    If table.ListRows.Count > 0 Then
        Set lastRow = table.ListRows(table.ListRows.Count).Range
        For col = 1 To lastRow.Columns.Count
            If Trim(CStr(lastRow.Cells(1, col).value)) <> "" Then
                Exit For
            End If
        Next col
    End If
    'Adds values to the table.  Column 2 should add a number 1 greater then previous
    Set lastRow = table.ListRows(table.ListRows.Count).Range
    lastRow.Cells(table.ListRows.Count, 1).value = value
    lastRow.Cells(table.ListRows.Count, 2).value = lastRow.Cells((lastRow.Count - 1), 2).value + 1
End Sub

The attached code is my add to table sub. value, is what ever the user entered into the userform, generally a string, but I left it as a variant in case I get something else.

Thanks for the help!

Read more here: https://stackoverflow.com/questions/64897817/vba-excel-table-skipping-rows-when-using-a-user-form-for-data-entry

Content Attribution

This content was originally published by Dragonchicken at Recent Questions - Stack Overflow, and is syndicated here via their RSS feed. You can read the original post over there.

%d bloggers like this: