Excel fórmulas e formatação condicional

[Kadu Du]

Boa tarde galera!

 

Estou com o seguinte problema para concluir minha planilha.

1- Nas células N9, O9, S9 e T9 possuem fórmulas para realizar os cálculos que preciso.

2- Como essa planilha sempre sofre alterações, o procedimento padrão é recortar e colar.  Quando recorto uma linha Ex: A15:M15 e vou colocar em A12:M12 as fórmulas estão acompanhando esse recorte e perde sua funcionalidade. Para resolver essa questão, criei algumas Macros (AJUSTAR) E (SOMAR). Nesse momento, resolvo o ajuste de fórmulas novamente.

3-Na célula Q10 e U10, possui formatação condicional para preenchimento de cores. Ao realizar esse procedimento de Recortar e Colar, elas perdem sua funcionalidade.

 

Minha solicitação se possível é ajustar essas fórmulas para que não sofressem alterações após o Recorte e Cole ou alguma Macro que fizesse voltar ao normal como nos Botões que eu fiz.

 

Muito obrigado!

NOVA PROGRAMAÇÃO.7z

[Visitante]

Veja se esta solução atende.

O código abaixo destina-se a copiar o intervalo de origem, colar valores no intervalo destino e limpar o intervalo de origem.

Essa operação equivale à operação padrão que você faz de Recortar / Colar porém ela não alterará as referências de fórmulas e FC existentes na planilha.

 

funcionamento do código abaixo, instalado no módulo da planilha:

1. aplique duplo clique em qualquer célula do intervalo de origem dos dados (ex. A15 ou B15 ou C15, ... , M15)

2. insira na Caixa de Entrada o número da linha de destino dos dados copiados (ex. 12) e clique em OK ou aperte Enter

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
 Dim LD As String
  If Target.Column > 13 Then Exit Sub
  Cancel = True
  LD = Application.InputBox("INSIRA O NÚMERO DA LINHA DESTINO DOS DADOS", "LINHA DESTINO", Type:=2)
  If LD = "Falso" Or LD = "" Then Exit Sub
  Cells(Target.Row, 1).Resize(, 13).Copy
  On Error Resume Next
  Cells(LD, 1).PasteSpecial xlValues
  Cells(Target.Row, 1).Resize(, 13).ClearContents
End Sub

obs. para acessar o módulo da planilha clique com o direito na guia da planilha e escolha Exibir Código

[Kadu Du]

@osvaldomp Bom dia! Está quase no que eu preciso! Ao realizar essa sugestão, ela está deixando a linha em branco. Eu preciso que ao recortar, ela não apague a linha a qual será a origem do colar e não deixar em branco a linha após o recorte.

 

Grato

1 hora atrás, osvaldomp disse:

Veja se esta solução atende.

O código abaixo destina-se a copiar o intervalo de origem, colar valores no intervalo destino e limpar o intervalo de origem.

Essa operação equivale à operação padrão que você faz de Recortar / Colar porém ela não alterará as referências de fórmulas e FC existentes na planilha.

 

funcionamento do código abaixo, instalado no módulo da planilha:

1. aplique duplo clique em qualquer célula do intervalo de origem dos dados (ex. A15 ou B15 ou C15, ... , M15)

2. insira na Caixa de Entrada o número da linha de destino dos dados copiados (ex. 12) e clique em OK ou aperte Enter

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
 Dim LD As String
  If Target.Column > 13 Then Exit Sub
  Cancel = True
  LD = Application.InputBox("INSIRA O NÚMERO DA LINHA DESTINO DOS DADOS", "LINHA DESTINO", Type:=2)
  If LD = "Falso" Or LD = "" Then Exit Sub
  Cells(Target.Row, 1).Resize(, 13).Copy
  On Error Resume Next
  Cells(LD, 1).PasteSpecial xlValues
  Cells(Target.Row, 1).Resize(, 13).ClearContents
End Sub

obs. para acessar o módulo da planilha clique com o direito na guia da planilha e escolha Exibir Código

 

[Visitante]

19 horas atrás, Kadu Du disse:

 ...o procedimento padrão é recortar e colar.

 

49 minutos atrás, Kadu Du disse:

Eu preciso que ao recortar, ela não apague a linha a qual será a origem do colar e não deixar em branco a linha após o recorte.

 

Me parece que há uma contradição entre o primeiro e o segundo comentário acima.

 

primeiro comentário:

Recortar / Colar ~~~> implica que a origem será apagada e o destino será substituído, é assim que o Excel funciona; o código que eu sugeri foi com base nesse seu comentário

 

segundo comentário:

Se entendi corretamente, isso que você escreveu não é Recortar e também não é Colar.

Se você não quer apagar a origem e quer manter o destino então talvez você queira é Copiar / Inserir células copiadas

 

Informe com exatidão o que você deseja. Forneça um exemplo do antes e do depois de efetuar a operação.

 

dica - para responder clique em Responder, localizada abaixo da última postagem, só clique em Citar se necessário

[Kadu Du]

@osvaldomp Osvaldo, desculpa se não consegui explicar de forma correta. Essa operação não sou eu que faço! Estou apenas auxiliando na reestruturação da planilha.

A operação que ele executa é Ctrl X e apertar botão direito e clicar em inserir células recortadas. Ele sempre vai recortar a linha e movimentar seja para baixo ou para cima. Ele não quer apagar nenhuma linha.

Faça um teste como deixei na planilha em anexo (Selecionar A17:M18) e estou recortando e inserindo as células recortadas em A10. Ela está adicionando esses dados e não apaga nenhum outro.

 

Grato

NOVA PROGRAMAÇÃO_1.7z

[Kadu Du]

Em ‎19‎/‎06‎/‎2019 às 16:48, Kadu Du disse:

@osvaldomp Consegue me ajudar?

 

Em ‎19‎/‎06‎/‎2019 às 16:48, Kadu Du disse:

Grato

NOVA PROGRAMAÇÃO_1.7z 71 kB · 1 download

 

[Visitante]

Em 19/06/2019 às 16:48, Kadu Du disse:

Faça um teste como deixei na planilha em anexo (Selecionar A17:M18) e estou recortando e inserindo as células recortadas em A10. Ela está adicionando esses dados e não apaga nenhum outro.

 

18 minutos atrás, Kadu Du disse:

Consegue me ajudar?

 

 

A dificuldade é que o meu Excel funciona de forma diferente do seu.

O que você descreveu aí acima, no meu Excel corresponde a Copiar / Inserir células copiadas, e não corresponde a Recortar, como é no seu.

No meu Excel, Recortar remove o conteúdo das células de origem (acredito que se não removesse a MS não teria colocado o nome Recortar...), já no seu, você diz que não remove (). Um dos dois Excel tem defeito de fabricação.

 

Eu vou preparar uma solução via macro e coloco aqui pra você avaliar.

 

[Kadu Du]

5 horas atrás, osvaldomp disse:

 

 

A dificuldade é que o meu Excel funciona de forma diferente do seu.

O que você descreveu aí acima, no meu Excel corresponde a Copiar / Inserir células copiadas, e não corresponde a Recortar, como é no seu.

No meu Excel, Recortar remove o conteúdo das células de origem (acredito que se não removesse a MS não teria colocado o nome Recortar...), já no seu, você diz que não remove (). Um dos dois Excel tem defeito de fabricação.

 

Eu vou preparar uma solução via macro e coloco aqui pra você avaliar.

 

Se eu fizer esse comando de copiar e inserir células copiadas, irá aparecer a janela pedindo se eu quero deslocar as células para baixo ou para direita. Isso é como se eu quisesse duplicar os dados. Ao recortar / inserir células recortadas, ela não duplica esses dados. Quanto a questão de remover você entendeu...

 

[Visitante]

Em 19/06/2019 às 12:24, osvaldomp disse:

Forneça um exemplo do antes e do depois de efetuar a operação.

 

Você poderia preparar e colocar aqui no fórum o que eu pedi no post #4, conforme acima ?

[Kadu Du]

1 hora atrás, osvaldomp disse:

 

Você poderia preparar e colocar aqui no fórum o que eu pedi no post #4, conforme acima ?

Coloquei duas planilhas. A com o final recorte, observe que selecionei A12:M14 e inseri células recortadas em A17

Desktop.rar

[Visitante]

Em 21/06/2019 às 22:12, Kadu Du disse:

... observe que selecionei A12:M14 e inseri células recortadas em A17 

 

No seu exemplo é possível observar o que comentei antes. Na célula B12 estava relógio e agora está formiga. Ao recortar, o Excel limpou as células de origem, entre elas a B12, e em seguida excluiu as células vazias, em consequência as células de baixo subiram.

 

No arquivo anexo há duas cópias da sua planilha.

Em uma delas mantive a operação de Recortar para ser executada manualmente e coloquei uma macro para atualizar/refazer as Formatações Condicionais e fórmulas.

Na outra coloquei macro para efetuar automaticamente o Recortar e em seguida atualizar/refazer as Formatações Condicionais e fórmulas.

Em ambas coloquei macro para formatar células (inclusive a FC) e inserir fórmulas na primeira linha abaixo da tabela.

 

Coloquei os comentários nas próprias planilhas. Veja se aproveita.

 

 

 

 

NOVA PROGRAMAÇÃO_1 V2.zip

[Kadu Du]

Em ‎23‎/‎06‎/‎2019 às 20:10, osvaldomp disse:

 

No seu exemplo é possível observar o que comentei antes. Na célula B12 estava relógio e agora está formiga. Ao recortar, o Excel limpou as células de origem, entre elas a B12, e em seguida excluiu as células vazias, em consequência as células de baixo subiram.

 

No arquivo anexo há duas cópias da sua planilha.

Em uma delas mantive a operação de Recortar para ser executada manualmente e coloquei uma macro para atualizar/refazer as Formatações Condicionais e fórmulas.

Na outra coloquei macro para efetuar automaticamente o Recortar e em seguida atualizar/refazer as Formatações Condicionais e fórmulas.

Em ambas coloquei macro para formatar células (inclusive a FC) e inserir fórmulas na primeira linha abaixo da tabela.

 

Coloquei os comentários nas próprias planilhas. Veja se aproveita.

 

 

 

 

NOVA PROGRAMAÇÃO_1 V2.zip 49 kB · 3 downloads

Osvaldo, bom dia! Ficou incrível as soluções apresentadas! Gostaria de utilizar a versão automática, porém, estou encontrando algumas dificuldades. Ex: Ao selecionar A15:M17, e selecionar o destino para A22 ela não move os dados. Ao selecionar A15:M17 para o destino A10 foi normalmente. Com isso as células ficam selecionadas em A15:M17 e se eu quiser destinar para A19 elas também não estão se movendo. Poderia verificar?

 

Agradeço muito mesmo as soluções!

[Visitante]

Cara, não consegui reproduzir aqui os problemas que você apontou. Funciona tudo de boa!

 

Fiz pela opção automática as manobras que você relatou e comparei com as manobras iguais feitas manualmente na outra planilha e os resultados foram iguais.

 

dica - para responder clique em Responder, abaixo da última postagem, só clique em Citar se necessário.

[Kadu Du]

Osvaldo, boa tarde! Realizei outro teste selecionando apenas A15 e destinei para a célula A11 (tudo OK). Agora eu selecionei A10 para A13, ela foi para A12. Selecionei A12 para A22, ela foi para A21. Agora selecionei A18:21 para A22 e não se moveu.

[Visitante]

Conforme eu comentei antes por várias vezes, o Recortar do Excel funciona dessa forma que você relatou. O código que Recorta/Insere está funcionando corretamente.

 

Tomando por base o primeiro teste que você relatou acima: Recortou da linha 15 e Inseriu na linha 11 (nesse caso a linha que recebeu o recorte está acima da linha recortada). Ao se "manifestar o desejo de Recortar da 15 e Inserir na 11" então o Excel desloca para baixo todas as linhas a partir da linha 11. Isto é, a linha 11 passa para a posição 12, a linha que estava na 12 passa para a 13, e assim até a última linha da tabela, isso implica que a linha 11 ficou vazia, aguardando ser preenchida pela 15, e implica também que a tabela "cresceu uma linha".

Por outro lado, a linha 15 também ficou vazia por causa do Recortar.

Em seguida a antiga linha 15 é Inserida na 11, concluindo assim o Inserir, e a 15 (lembrando que agora está vazia) é excluída, o que provoca que todas as linhas abaixo dela "subam" e a tabela volta ao tamanho original. Como nessa última etapa a linha 11, que recebeu o Inserir, não mais fez parte do pacote que desce/sobe, então ela permanece na mesma posição.

 

Tomando agora o seu segundo teste: Recortou da linha 10 e Inseriu na linha 13 (nesse caso a linha que recebeu o recorte está abaixo da linha recortada). Analogamente ao descrito acima, as linhas a partir da linha 13 foram deslocadas para baixo, assim a linha 13 ficou vazia (pois irá receber o Inserir), a tabela "cresceu uma linha" e a linha 10 ficou vazia (pois foi Recortada). Aí a linha 13 recebeu a linha 10.

Ao final desse rolo a linha 10 ficou vazia, então o Excel excluiu essa linha e as linhas abaixo dela subiram, inclusive a linha 13, que recebeu a inserção e que faz parte do pacote que desce/sobe, e é por isso que ao inserir na linha 13 ela passa a ser a linha 12, nesses casos, a linha que recebeu a inserção é afetada pela "subida" das demais linhas e por isso ela não permanece na mesma posição.

 

Sugestão: repita esses testes na sua planilha original, sem macros, e compare os resultados.

[Kadu Du]

Osvaldo, muito obrigado! Realmente agora entendi a lógica de tudo isso! Está 100% ela mesma! Tenho apenas mais uma dúvida: Imagine que o responsável pela operação faça algum erro na hora de deslocar as células. Utilizando as macros, não funciona o "Desfazer". Seria possível criar algum botão para essa situação? 

 

[Visitante]

Possível é, porém extremamente trabalhoso. É preciso armazenar os dados antes de executar a macro.

 

Uma alternativa é salvar o arquivo sempre antes de executar a macro, assim se o usuário fizer escolhas por engano, poderá fechar o arquivo sem salvar logo que perceber o erro.

 

Outra forma, seria colocar uma Caixa de Mensagem informando as linhas origem e destino para que o usuário confirme as escolhas.

 

[Kadu Du]

Osvaldo, bom dia! Essa outra forma seria muito interessante, poderia me ajudar com essa solução (Caixa de mensagem) ?

Mais que agradecido por todo o seu empenho!

[Visitante]

Acrescente a parte em vermelho conforme abaixo ao código que está no módulo da Plan1.

 

  If LD = "Falso" Or LD = "" Then Exit Sub
  If MsgBox("CONFIRMA A OPERAÇÃO INDICADA ABAIXO?" & vbLf & vbLf & _
   "O INTERVALO " & Cells(Selection.Row, 1).Resize(Selection.Rows.Count, 13).Address(0, 0) _
   & " SERÁ RECORTADO " & vbLf & "E SERÁ INSERIDO NO INTERVALO " & Cells(LD, 1).Resize(Selection.Rows.Count, 13).Address(0, 0), _
    vbYesNo + vbQuestion) = vbNo Then Cancel = True: Exit Sub
  On Error GoTo fim

[Kadu Du]

Osvaldo, bom dia! Muito obrigado! Não sei o que acontece que quando eu copio o código e colo, ele traz junto um ? e não funciona. Mesmo apagando esse ?, não funciona. Como sei que o _ significa outra linha, deixei todo o código na mesma linha e funcionou.

Realmente muito obrigado por todo o empenho em me ajudar.

Que Deus abençoe e continue te capacitando mais e mais para ajudar novas pessoas!

 

Pode fechar o tópico.

[Patropi]

@Kadu Du

 

 

Na minha assinatura tem um link para uma instrução de como marcar o tópico como Resolvido.

 

[]s