Round Robin Considering Days Off

[Nizick1979]

I would like to create a button in Excel that will pull from a list telling me who would be next to assign a lead to and if possible making sure that they are assigned evenly over the course of time. So for example, We have 3 names Nick, Scott, Tony work days are Monday through Saturday and Scott is off Tuesday, Nick is off Wednesday and Tony is off Thursday. I want the formula to exclude the individuals on their days off and when someone clicks the button it will pull whomever is next assigning in round robin fashion. Is this possible?

 

[rakesh seebaruth]

Sub NextAssignee()
    Dim names As Range, daysOff As Range, currentDay As String, i As Integer
    Dim nextAssignee As String
    
    Set names = Worksheets("Sheet1").Range("A1:A3") 'change this to the range that contains the names
    Set daysOff = Worksheets("Sheet1").Range("B1:B3") 'change this to the range that contains the days off
    
    currentDay = Weekday(Date)
    
    For i = 1 To names.Count
        If Not Application.Match(currentDay, daysOff(i, 1), 0) Then
            nextAssignee = names(i, 1)
            Exit For
        End If
    Next i
    
    'change this to the range where you want the next assignee to appear
    Worksheets("Sheet1").Range("C1").Value = nextAssignee
End Sub