Excel Referência dinâmica entre colunas

[Martti]

Salve amigos! Mais uma vez recorro ao vasto conhecimento dos integrantes desse forum.

Em anexo uma planilha de agendamento de calibração de instrumentos.

O código é adaptado de uma outra planilha, mas com um range de 2 colunas

Preciso que localize a última célula preenchida da linha e execute o código.

Os dados da coluna 1ª Calibração até 10ª Calibração são importados de outra planilha,

Só preciso atualizar o status à cada importação, porém o período de calibração de

cada máquina é diferente e são incrementais.

Consegui resolver para a primeira coluna, mas meu conhecimento me limita a este ponto.

Quem puder ajudar, agradeço desde já.

Abs

calibracao.zip

[Midori]

1 hora atrás, Martti disse:

Preciso que localize a última célula preenchida da linha e execute o código.

Essa localização pode ser feita com macro ou fórmula. Mas que código você se refere? A planilha só tem uma tabela com dados em três linhas. Sobre atualizar o status não há nada na planilha que indique como isso deve ser atualizado.

 

[Martti]

@Midori

Obrigado pela atenção.

No VBE tem um módulo com o código que eu utilizava anteriormente.

Em virtude de mudanças administrativas, a planilha teve que ser alterada.

O código compara a coluna J com a coluna K e atualiza o status na coluna I.

Na medida que as calibrações são efetuadas, os registros avançam nas colunas,

então, no exemplo, a proxima calibração(colunaL) deverá ser comparada com

a coluna anterior(coluna K) e assim por diante.

Sim, poderia usar formulas e até formatação condicional por cores, mas obrigatoriamente

tem que ser em VBA.

Grato pelo seu tempo.

[Midori]

@Martti A planilha está no formato xlsx e sem o código, salve como xlsm.

[Martti]

@Midori

Desculpe a falha

calibracao.zip

[Midori]

@Martti O código está atualizando o status com base na calibração 1 e 2.

 

Se tivesse todas as datas na calibração 2 e outros na 3, a macro já deve atualizar o status com base nessas colunas? Se entendi o que deve ser feito, é para identificar a penúltima coluna de calibração com data para atualizar o status com a última. É isso?

[Martti]

@Midori Isso mesmo, excelente entendimento!

Sempre que as datas forem atualizadas(importadas), a mudança de status deve ser feita pela ultima celula preenchida na linha + 1( penultima e ultima).

Se a ultima celula preenchida for <>"", então:

 If ultima<Hoje = Status =Vencida à & Hoje-ultima & dias

If ultima=Hoje = Status = Vence Hoje

If ultima>Hoje = Status = Vencerá em & ultima-Hoje & dias

Else

OK (preciso melhorar isso, aceito sugestões)

Um detalhe é que as maquinas tem períodos de calibração diferentes, não sendo possível fixar que calibração 2 será calibração 1 + 30 (ou 60, 90) dias.

O status mudará sempre que houver acréscimo de calibração.

Agradeço pelo seu tempo.

 

[Midori]

Última celula preenchida pode ter dois sentidos: 1) Simplesmente a última coluna com data, independente das outras linhas da tabela; 2) Última calibração com data. Parece que deve ser considerado o segundo, então pode acontecer o caso como no print abaixo,

 

 

Aí a última calibração com data é a 3 na primeira linha. Então nesse caso o status está finalizado, correto? Já que é a última coluna de calibração com data.

 

As outras duas linhas, como ainda não tem data na última calibração (3) vão ficar com status vencido ou à vencer dependendo da data. É isso?

 

[Martti]

@MidoriIsso mesmo, o status se modifica ou não a partir da atualização da linha.

Na verdade, acredito que preciso eliminar o status FINALIZADO.

A proxima data de calibração será estipulada no momento da realização da mesma***,

onde cada chefe de setor saberá se será em até 30, 60 ou 90 dias.

Como no exemplo acima, quando a importação dos dados atualizar a 3ª calibração, preencherá tb

a 4ª calibração com uma data estipulada***, o status passará a "vencerá em xx dias"

Por isso penso em tirar o status FINALIZADO, ficando só VENCIDO HÁ XX DIAS, VENCE HOJE, VENCERÁ EM XX DIAS,

porque quando a calibração é feita, a proxima calibração é preenchida***, reiniciando a contagem.

Como no exemplo, quando a 3ª calibração foi feita, o status anterior era "VENCIDO HÁ 1 DIAS" (HOJE() - 22/03/2022).

Quando a planilha foi atualizada e a data da 3ª calibração foi inserida, a data da 4ª calibração tb foi inserida(23/04/2022), então, hoje, 24/03, executando o código,

este buscará a última célula preenchida da linha, o status mudará  "VENCERÁ EM 29 DIAS".

Como no exemplo, se não houve a 3ª calibração para a linha 2, o status permanecerá "VENCIDO HÁ XX DIAS" até que o código identifique alguma data na 3ª calibração, que passará a ser a última célula com data na linha 2.

Assim penso em somente 3 status, à medida que as calibrações forem sendo atualizadas.

Coloco-me à disposição para esclarecimentos.

Obrigado pelo seu tempo.

 

***defini essa mudança com a chefia por me parecer mais objetiva, visto a volatilidade das datas.

No meu analfabetismo em variáveis, pensei neste conceito:

 

Sub Status()
Dim Linha As Double
Linha = 1

Dim ColunaStatus    As Double
Dim ColunaData      As Double
Dim ultimalinha     As Long
Dim lin             As Long

ultimalinha = Range("A" & Rows.count).End(xlUp).Row
For lin = 2 To ultimalinha

ColunaStatus = 9
ColunaData = Range("J" & lin).End(xlToRight).Column

With Plan1

    Do
    
        Linha = Linha + 1
.
.
.
.
.
.
End Sub

 

[Midori]

@Martti Veja se é assim,

 

Sub AtualizaStatus()
    Dim Tabela      As ListObject
    Dim Calibracao  As Range
    Dim CampoStatus As Range
    Dim Status      As Range
    Dim Ultima      As Integer
    
    Set Tabela = [Tabela1].ListObject
    Set CampoStatus = Tabela.ListColumns("STATUS").DataBodyRange
    
    For Each Status In CampoStatus
        Dim Conta   As Integer
        Conta = WorksheetFunction.CountA(Status.Offset(0, 1).Resize(1, 12))
        If Conta > Ultima Then
            Ultima = Conta
        End If
    Next Status
    Ultima = Ultima + CampoStatus.Column
    
    For Each Status In CampoStatus
        If Status.Offset(0, 1) <> "" Then
            Dim Coluna      As Integer
            
            Coluna = Status.Offset(0, 1).End(xlToRight).Column
        
            If Coluna = Tabela.DataBodyRange.Columns.Count Then
                Coluna = Status.Offset(0, 1).Column
            End If
        
            Set Calibracao = Tabela.ListColumns(Coluna).Range.Cells(Status.Row)
            
            If Calibracao.Column <> Ultima Then
                If Calibracao.Value < Date Then
                    Status = "VENCIDO A " & Date - Calibracao.Value & " DIA(S)"
                ElseIf Calibracao.Value > Date Then
                    Status = "FALTA(M) " & Calibracao.Value - Date & " DIA(S) PARA VENCER"
                Else
                    Status = "VENCE HOJE"
                End If
            Else
                Status = "FINALIZADO"
            End If
        End If
    Next Status
End Sub

 

[Martti]

@Midori Parabéns pela solução do problema, melhor impossível.

Vou me debruçar sobre o código para adquirir mais conhecimento.

Muito obrigado por compartilhar seu tempo e seu conhecimento.

Sua ajuda foi por demais importante...