Este fórum está sendo desativado

[CAMILOALVES01]

Boa Tarde!
Na Coluna S da planilha “Registro Pendencia PR”, existe uma fórmula matricial e que está impactando de forma significativa o desempenho da planilha na hora da inclusão dos dados na planilha "Inserir Pendencia PR", essa fórmula tem por função consolidar o ano contábil da coluna P da planilha “Registro Pendencia PR”, visto que os anos se repetem a cada inclusão (parte normal do processo).
O pedido de ajuda: Se for possível, substituir a fórmula matricial existente na coluna S da planilha “Registro Pendencia PR”, por uma macro e/ou outra alternativa de forma que o processo se torne mais ágil.
OBS: A fórmula matricial da coluna S da planilha “Registro Pendencia PR” é copiada automaticamente em conjunto com os demais dados todas as vezes que se aciona o botão “INSERIR” na planilha “Inserir Pendencia PR” e assim deve permanecer, ou seja, sendo copiada automaticamete.
Desde já agradeço

[osvaldomp]

Salve, por CAMILOALVES01 » 13 Set 2021 às 13:46.

A sua fórmula faz referência à coluna P inteira e isso provavelmente é causa da lentidão.

No lugar de P$7:P$1048576 coloque por exemplo P$7:P$1000

[CAMILOALVES01]

Boa Tarde Caríssimo osvaldomp - 13 Set 2021 às 14:17
Primeiramente Obrigado por Dispor de Seu Tempo Para Ajudar-me.
Então..
O processo é esse mesmo, fazer menção a toda coluna P (1048576), porém para minimizar o impacto da fórmula matricial, a ideia foi de copiar a fórmula a cada evento de inclusão na planilha "Inserir Pendencia PR" (isso tá funcionando) , sem ter que arrastar e se preocupar com limites de dados, pois essa planilha serve como concentrador de dados (observe que temos dados de 2017 em diante).
Então pensamos na possibilidade de se substituir essa fórmula matricial por uma macro ou alguma outra solução que tornasse a planilha mais "leve" mantendo o critério atuais, ou seja, consolidado o ano na coluna S da planilha "Registro Pendencia PR" em função dos dados da coluna P.
Atenciosamente

[osvaldomp]

Então pensamos na possibilidade de se substituir essa fórmula matricial por uma macro ou alguma outra solução que tornasse a planilha mais "leve" ...
Uma ideia seria substituir o comando atual da sua macro que copia a fórmula de S7 e cola na linha recém preenchida por um comando que coloque o ano na primeira célula vazia da coluna S se ele ainda não existir naquela coluna. Assim, não haveria fórmulas na coluna S.

... mantendo o critério atuais, ou seja, consolidado o ano na coluna S da planilha "Registro Pendencia PR" em função dos dados da coluna P.
Eu não pequisei se a coluna P é preenchida via macro, mas me parece que ela recebe o ano da data inserida em C9 da planilha Inserir Pendencia PR. É isso? Se sim, então ficaria fácil implementar o que eu sugeri no comentário logo acima. Como exemplo, considerando o preenchimento atual da coluna S, quando for efetuado o primeiro lançamento em C9 com data do ano 2022 então esse ano será inserido em S12.
Acha viável essa solução?

Curiosidade: eu não entendi qual é a utilidade da lista de anos na coluna S, pois não vi relação com os demais dados daquela ou de outra planilha. O que você quer dizer com "consolidar o ano"? Para que serve?

[CAMILOALVES01]

Caríssimo osvaldomp - 13 Set 2021 às 16:41
Primeiramente Obrigado por Dispor de Seu Tempo e Conhecimentos Para Ajudar-me.
Quanto a coluna P, sim! Ele recebe o ano que é extraído da data digitada em C10 da planilha “Inserir Pendencia PR”, e sua fração, no caso o ano, é contabilizado na célula C20, e quando clica no botão “INSERIR”, o ano (célula C20) migra para coluna P da planilha “Registro Pendencia PR”.
Quanto a coluna S da planilha “Registro Pendencia PR”, Foi criada única e exclusivamente para consolidar a variável ano (coluna P), pois o ano se repetem a cada inclusão na planilha “Inserir Pendencia PR” por estarem vinculados a data da não conformidade.
Portanto, Caso sua ideia de substituir o comando atual da macro que copia a fórmula de S7 e cola na linha recém preenchida, por um comando, desde que esse comando consolide o ano, por exemplo o ano 2017 constará somente uma vez na coluna S, embora tenham vários registros 2017 na coluna P e assim sucessivamente com os demais anos, na primeira célula vazia da coluna S se ele (ano) ainda não existir naquela coluna Abolindo com isso a fórmulas na coluna S ficaria SHOW, muito viável, caso deseje pode dar continuidade.
Atenciosamente

[osvaldomp]

Olá, @CAMILOALVES01 .

Eu continuo sem entender qual a utilidade dos dados lançados na coluna S, tendo em vista que não percebi qualquer relação deles com os demais dados da planilha. Mas ... vamos em frente.

Ok, faremos alterações para o código inserir o ano de C10 na coluna S e assim eliminamos a "necessidade" de fórmulas naquela coluna.

Aproveitando, antes de incrementar essa alteração, eu sugiro efetuarmos um saneamento no seu código (considerando o código que acompanha o arquivo que você postou neste tópico):
1. há no código um comando para desproteger a planilha Registro Pendencia PR, porém não há um comando para voltar a protegê-la. É necessário proteger aquela planilha? Podemos excluir aquele comando?
2. eu sugiro acrescentar também comandos para verificar o preenchimento obrigatório dos campos na planilha Inserir Pendencia PR. Se você concorda, então informe quais os campos de preenchimento obrigatório.
3. por exemplo, se o campo C10, data, for obrigatório, então a sua fórmula atual em Q7 de Registro Pendencia PR ~~~> =SE(B7="";"";SE(E(L7="";N7="");"SIM";SE(M7<>"";"SIM";"NÃO"))) poderá ser simplificada ~~~> =SE(OU(E(L7="";N7="");M7<>"");"SIM";"NÃO"). A propósito, se você quiser, podemos inserir na coluna Q o resultado via código e assim eliminar fórmulas também na coluna Q. Me parece que você utiliza a planilha Registro Pendencia PR como Banco de Dados e não é recomendável a utilização de fórmulas para se montar um Banco de Dados.
4. o código também copia/cola fórmula na coluna T, no entanto TODAS aquelas fórmulas retornam vazio. São necessárias essa fórmulas? Qual a sua utilidade? Podemos excluir essa operação?

Proponho também racionalizarmos o código lsLimpaMovimento. Se não me engano já fiz isso no passado em um outro tópico seu.

[CAMILOALVES01]

Caríssimo osvaldomp - 14 Set 2021 às 12:07
Muito Obrigado Pela Paciência e Disponibilizar seus Conhecimentos Para Ajudar-me.
Explicando a Coluna S: Existe uma planilha de consulta não anexada anteriormente na qual anexo agora (“Sumario Pendencia PR”) e que as vezes há necessidades de filtrar nesta planilha (“Sumario Pendencia PR”) o ano, sendo assim, se Eu mandasse buscar na coluna P na planilha (“Registro Pendencia PR”), iriam aparecer repetidamente por exemplo vários 2017, 2018,...então por essa razão criei a coluna S na planilha (“Registro Pendencia PR”) que sumariza os anos da coluna P, dessa forma ao filtrar o campo ano, na planilha “Sumario Pendencia PR” (célula C7) aparecem somente um registro de cada ano;
1) Isso mesmo há no código um comando para desproteger a planilha Registro Pendencia PR, porém o outro comando para voltar a protegê-la,: 'Sheets("Registro Pendencia PR").Protect "modular2017", eu retirei provisoriamente para evitar transtornos na hora da análise, portanto devemos sim manter a proteção;
2) Sim, seria excelente a obrigatoriedade dos dados, então seguem os campos (células) para torna-los de digitação obrigatória: C10, C11, C12, C13, C14, C15;
3) Sim, C10 é de digitação obrigatória e concordo com Você na simplificação da fórmula atual pela fórmula sugerida por Você.
O objetivo da coluna Q, serve como artifício (“SIM”, “NÃO”) para serem utilizados na planilha “Sumario Pendencia PR” e tem por função filtrar as Não Conformidades pendentes e as resolvidas, sendo assim não vejo problemas em viabilizar isso via código conforme sugerido por Você;
4) Quanto a coluna T, Você tem razão está sem utilidades e pode ser excluída do projeto.
Atenciosamente

[osvaldomp]

Existe uma planilha de consulta não anexada anteriormente na qual anexo agora (“Sumario Pendencia PR”)
Ok, obrigado por explicar. Vi que nessa nova planilha os dados da coluna S são utilizados na Validação de C7.

Preparação da planilha Registro Pendencia PR antes de testar o código abaixo.

Exclua as fórmulas da coluna S (na ordem abaixo):
1. selecione S12 | End | Seta para Baixo | Delete
2. selecione S7 | End | Seta para Baixo | Copiar | Colar Valores

Exclua as fórmulas da coluna Q:
3. selecione Q7 | End | Seta para Baixo | Copiar | Colar Valores

Exclua as fórmulas da coluna T:
4. selecione T7 | End | Seta para Baixo | Delete

Cole uma cópia do código abaixo no lugar do atual.

Código: Selecionar todos

Sub verificar_campos_branco_PONTE()
 Dim k As Long, c As Range, LR As Long
  If Application.CountA(Range("C10:C15")) < 6 Then MsgBox "PREENCHA O INTERVALO C10:C15": Exit Sub
  With Sheets("Registro Pendencia PR")
   .Unprotect "modular2017"
   LR = .Cells(Rows.Count, 1).End(3).Row + 1
   For Each c In Range("C9:C13,D13,C14,C16:C17,C15,C18,W1:Y1,C19:C20")
    .Cells(LR, k + 1) = c.Value: k = k + 1
   Next c
   .Cells(LR, 17) = Evaluate("IF(OR(AND(L" & LR & "="""",N" & LR & "=""""),M" & LR & "<>""""),""SIM"",""NÃO"")")
   If Application.CountIf(.[S:S], [C20]) = 0 Then .Cells(Rows.Count, 19).End(3)(2) = [C20]
    .Protect "modular2017"
   End With
   [C10:C18] = ""
End Sub

#
obs. os códigos atuais lsLimpaMovimento e Posicionar_Registro_Pendencia_PR não são necessários, podem ser disponibilizados para doação.

[CAMILOALVES01]

Caríssimo osvaldomp - 14 Set 2021 às 19:28, Boa Tarde!
O único detalhe que falta é o seguinte: na versão anterior, a célula C9 da planilha “Inserir Pendencia PR” atualizava automaticamente a numeração sempre que houvesse um evento de inclusão através do botão “inserir”, na nova versão, essa atualização está ocorrendo de forma semi-automática, ou seja, o numeral é atualizado automaticamente, porém tem-se que clicar em outra aba de quaisquer planilha. Do jeito semi-automático é perigoso, pois está permitindo a inclusão com numeração repetido na planilha “Registro Pendencia PR”.
Se não for incomodá-lo, seria possível que Você possa verificar o motivo da não atualização automática conforme versão anterior?
No mais, a solução ficou excelente com a racionalização do código, agilizando demais a entrada de dados e tornando muito mais agradável a tarefa.
Desde Já Agradeço.

[osvaldomp]

Olá, @CAMILOALVES01 .

Por favor, no código que passei acrescente a linha em vermelho, conforme abaixo.

.Protect "modular2017"
[C9] = Format(CLng(.Cells(LR, 1)) + 1, "0000000")
End With

#
obs. com a alteração acima o código Private Sub Worksheet_Activate() existente no módulo da planilha Inserir Pendencia PR também pode ser encaminhado para doação.

[CAMILOALVES01]

Caríssimo osvaldomp - 15 Set 2021 às 15:15, Boa Tarde
Grato pela paciência e compartilhamento do seu conhecimento.
Funcionou perfeitamente, ou seja, esse assunto já está resolvido, porém pegando “carona” em vosso conhecimento, repliquei sua macro com as devidas adaptações para boa parte de minhas planilhas otimizando as mesmas com sucesso, neste sentido, se possível, peço que analise onde foi que Eu errei em uma delas.
Possível Macro envolvida: ReplicaDados(), É o seguinte, a replicação de dados das colunas: B, C, D, E, F ao clicar no botão “Atualizar” da planilha “Registro de OS” deveriam ser replicados em função do número da OS na coluna “A” da planilha “Registro de OS”, ou seja, se repetirem quantas vezes necessários somente em função do Nº OS correspondente, e está ocorrendo uma espécie de update para todas as colunas independente do número da OS, ou seja, atualmente Resumo da ópera: a planilha está mantendo corretamente o número da OS, porém na ação descrita acima atualizam todos os dados das colunas B, C, D, E, F da planilha “Registro de OS” sem levar em consideração o N da OS (coluna A da planilha “Registro de OS”)
OBS: Anexo planilha, se possível peço que avalie.
Desde já agrdeço

[osvaldomp]

Possível Macro envolvida: ReplicaDados(), ...

Verdade, me parece também que a macro ReplicaDados foi convidada para a festa, mas não deveria estar lá.

O botão Atualizar, existente na planilha Inserir OS, aciona a macro VerificarNovaOS, que no final aciona Botao_Alterar, que no final aciona lsAlterarOS, que no final aciona lsLimpaMovimento, que no final aciona tgr e que, por último, no final aciona ReplicaDados.

Experimente desativar a macro ReplicaDados no final da macro tgr e veja se o resultado atende.

Outra forma é você preencher os dados relevantes na planilha Inserir OS , abra o editor de VBA, ajuste o seu tamanho e a sua posição na tela (sobre a planilha) de forma que você consiga acompanhar as operações que os códigos executam nas planilhas, e execute a macro VerificarNovaOS via F8, assim você poderá verificar o que é necessário ou não para o resultado que você deseja obter.

[CAMILOALVES01]

Caríssimo osvaldomp - 21 Set 2021 às 19:21, Boa Tarde
Primeiramente desculpe o transtorno e obrigado pela análise e paciência.
Então.....Como tenho pouco conhecimento em lógica de programação, comandos e funções do VB, as vezes utilizo etapas mais longas para alcançar o objetivo final do trabalho, e parte específica desse trabalho, seria replicar os dados das colunas: B, C, D, E, F na planilha “Registro de OS” de acordo com o número da OS existente na coluna A (Nº OS ) todas as vezes que houvesse um evento de atualização desse Nº OS através da célula “F5” e acionamento do botão “Atualizar”, o motivo disso (replicação de dados das colunas: B, C, D, E, F na planilha “Registro de OS” ) é que necessitamos de rastreabilidade através da planilha “CONSULTA OS”, caso não haja a replicação de dados na planilha “Registro de OS” que fornece a base de dados , esses campos ficam em branco ao se fazer a consulta na planilha “CONSULTA OS”.
Em função de minhas limitações, faço um sumário de cada macro envolvida no projeto:
1) Sub VerificarNovaOS() = Colocar regras para que haja menos erro possível na entrada de dados;
2) Sub Botao_Alterar() = Controlar data/hora de alterações de evento;
3) Sub lsAlterarOS() = migrar para planilha “Registro de OS” os dados digitados nas colunas: F5, F6, F7, F8 da planilha "Inserir OS" respectivamente para colunas 1, 13, 8, 7 ;
OBS:cabe aqui ressaltar que ainda não consegui aplicar nesse ponto os seus ensinamentos anteriores, porém note que no Botão “Inserir” na planilha “Inserir OS” existe uma macro toda construída de acordo com suas soluções/ensinamentos anteriores;
4) Sub tgr() = organizar a base de dados da planilha “Registro de OS”, ou seja, a ordem neste caso não é por data/hora de entrada e sim ordenar por número de OS;
5) Sub ReplicaDados() = Caberia a essa macro a função de replicar e não fazer update dos dados das colunas: B, C, D, E, F na planilha “Registro de OS” para que não fiquem vazio ao se fazer a pesquisa (rastreabilidade) na planilha “CONSULTA OS”.
OBS: Do jeito que está concebida atualmente, essa macro está fazendo update de todos os campos existentes nas colunas: B, C, D, E, F na planilha “Registro de OS”, o correto preencher as colunas em função do (Nº OS ).
Caso julgue necessário, faça a seguinte simulação: desabilite a macro Sub ReplicaDados() e na célula F5 da planilha "Inserir OS" escolha a número 1, na célula F7 escolha: Em Análise, na célula F8 digite um texto qualquer e por fim clique no botão "Atualizar". Verifica que as colunas B, C, D, E, F na planilha “Registro de OS” ficam em branco.
Caso seja possível, por gentileza avalie uma solução para questão.
Desde já agradeço.

[osvaldomp]

Salve, @CAMILOALVES01 .

Eu não me proponho a fazer uma revisão completa no seu projeto VBA pois demandaria muito tempo.

Este tópico foi aberto para tratar de lentidão no recálculo da planilha, que foi resolvido, então eu sugiro que você abra um novo tópico para tratar dos outros pontos para os quais você deseja ajuda.

[CAMILOALVES01]

Caríssimo osvaldomp - 22 Set 2021 às 14:43
Obrigado Pela Sugestão, Vou Abrir Um Tópico Específico.
Fique com DEUS e que DEUS continue dando-lhe sabedoria.
Atenciosamente