How to change position values for a given resource?

Question

Another Hardcore challenge, I don’t know if I can explain it well, but here it comes. In the table below, I have the resource "X", where he has already made 8 contacts( 5 completed and 3 not completed) and I would like to count automatically which is the next contact that he will perform removing the 8 cases in which he has already made the contact. In the table there is also the position for contact that would be the contact sequence that should follow but this column always comes with error.

Thank you very much for any help or exchange of knowledge.

Answer 1

With this test data:

You can accomplish this with the following code, where the explanation is in the comment:

Sub teste()
    Dim ws As Worksheet
    Dim UltimaLinha As Long, i As Long
    Dim rng As Range
    Dim matriz() As Variant
    Set ws = ThisWorkbook.Sheets("Planilha1")
    i = 1
    With ws
        'Mostra dados se estiverem filtrados
        If .FilterMode Then
            .ShowAllData
        End If
        'Última linha da coluna A
        UltimaLinha = .Cells(.Rows.Count, "A").End(xlUp).Row
        'Filtra o campo 3 do intervalo [A1:D & ultimalinha], ou seja, filtra a coluna C para valores diferentes de Concluída e Não Concluída
        .Range(.Cells(1, "A"), .Cells(UltimaLinha, "D")).AutoFilter Field:=3, Criteria1:="<>Concluída", Operator:=xlAnd, Criteria2:="<>Não Concluída"
        'Ordena os valores filtrados
        .Range("D1").CurrentRegion.Sort Key1:=.Range("D1"), Order1:=xlAscending, _
                                        Header:=xlYes, OrderCustom:=1, DataOption1:=xlSortNormal
        'Se houver após o filto, msgbox do primeiro valor
        If .Range("A2:A" & UltimaLinha).SpecialCells(xlCellTypeVisible).Count > 0 Then
            For Each c In .Range("A2:A" & UltimaLinha).SpecialCells(xlCellTypeVisible)
                If i = 1 Then
                    MsgBox c
                End If
                i = i + 1
            Next c
        End If
    'Mostra dados se estiverem filtrados
    If .FilterMode Then
        .ShowAllData
    End If
    End With
End Sub

Upshot: