How to change position values for a given resource?

0

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

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

    
asked by anonymous 15.09.2018 / 20:41

1 answer

0

With these test data:

Youcandothiswiththefollowingcode,wheretheexplanationisinthecomment:

Subteste()DimwsAsWorksheetDimUltimaLinhaAsLong,iAsLongDimrngAsRangeDimmatriz()AsVariantSetws=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

Result:

    
18.09.2018 / 20:14