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 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 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!