CODEWITHSUNDEEP
excelvba
Dragonchicken
Dragonchicken

Reputation: 3

Table skipping rows when using a userform for data entry

I am trying to create an on call schedule where employees are scheduled for 7 consecutive 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 for that portion. I am trying to make it dynamic, where employees can be added/ removed and the formula updates to reflect changes.

My plan is to use a table to track employees. I am using a userform 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 issue is, my add to table function skips cells.

My program fills 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 to enter a 4th or 5th value, the third value is replaced.

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
                table.ListRows.Add
                Exit For
            End If
        Next col
    Else
        table.ListRows.Add
    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 the userform entry, generally a string, but I left it as a variant in case I get something else.

Upvotes: 0

Views: 319

Answers (1)

Scott Holtzman
Scott Holtzman

Reputation: 27249

The code above mixes up cell ranges and listobject ranges. Work directly with the listobject and it should work.

Sub AddDataRow(tableName As String, value As Variant)

    Dim lastRow As ListRow
    Dim sheet As Worksheet
    Dim table As ListObject
    Dim col As Integer

    Set sheet = ActiveWorkbook.Worksheets("Jobs and Shifts")
    Set table = sheet.ListObjects(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)
        If worksheetFunction.CountBlank(lastRow.Range) <> lastRow.Columns.Count

            Dim useRow as ListRow
            Set useRow = table.ListRows.Add

        Else

            set useRow = lastRow

        End If
        
    Else

        Set useRow = table.ListRows.Add

    End If

    useRow.Range(1,1).Value = value    
    useRow.Range(1,2).Value = useRow.Range(1,2).Offset(-1).Value +1 
    'apologies if I didn't interpret this correctly. hopefully you can adjust easily
    
End Sub

Upvotes: 1

Related Questions