Guru do Excel e Power BI

Boa tarde a todos!
A Macro que utilizo foi criada por um membro do fórum em um outro tópico. Ela serve para "limpar" as informações em demasia antes de eu enviar o relatório ao meu cliente. Acontece que o meu sistema contábil alterou a forma como o relatório é exportado, eu já fiz algumas modificações, mas ainda preciso de ajuda.
Com o relatório anexo, após executar a macro, preciso:
1. Que sejam deletadas as linhas que tenham na Coluna "A" o termo "Continuação..."
2. Sempre ao final de todos os relatórios ficam duas linhas: "31/12/2018 | Saldo Atual..." Preciso que essas linhas com o termo "Saldo Atual" também sejam eliminadas. http://prntscr.com/m2am0e

Obs.: O relatório emitido é em csv, mas como fiz algumas modificações salvei em xlsx.

Obrigado desde já!


Macro:

Olá Luiz

A macro apresentada não está de acordo com a descrição da necessidade. Isso é poque a macro é antiga, anterior à alteração do layout do relatório?

E se esquecermos a macro, e fizermos outra que atende ao solicitado:
1. Que sejam deletadas as linhas que tenham na Coluna "A" o termo "Continuação..."
2. Sempre ao final de todos os relatórios ficam duas linhas: "31/12/2018 | Saldo Atual..." Preciso que essas linhas com o termo "Saldo Atual" também sejam eliminadas.

Notei que o termo CONTINUAÇÃO... não consta na coluna A e sim na C! O correto então é A ou C?

Notei que a macro apaga algumas colunas (A e F) e linhas (1 e 2). Deve continuar a deletar isso?

A macro apresentada leva em conta as palavras "Centro", "Histórico", "Conta"... Não sei se era assim e agora não é mais, ou se isso ainda deve ser considerado

Esclareça essas questões. A macro é fácil de fazer.

Jimmy San Juan

Bom dia Jimmy, tudo bem?

Bom, vamos lá...

A criação dessa macro foi assim:
Parte 1: http://gurudoexcel.com/forum/viewtopic.php?f=9&t=1009
Parte 2: http://gurudoexcel.com/forum/viewtopic.php?f=7&t=1068

E ela funcionou perfeitamente até esses tempos sem deixar a desejar. Recentemente o meu sistema foi atualizado e o relatório passou a ser emitido com algumas colunas a menos e acho que foi isso que fez com que a macro não tivesse mais 100% de eficiência. Eu dei uma pesquisada sobre VBA e fiz umas incrementações por conta própria, mexendo nas colunas que devem ser excluídas.

Respondendo a sua pergunta: Sim! "Continuação..." começa na coluna "C", mas após executar a macro ela fica na coluna "A".

"Centro", "Histórico", "Conta" são passos para que a macro possa "limpar" o relatório e deixar mais bonito

Apagar as colunas (A e F) e linhas (1 e 2) fazem parte do processo, que quando a macro foi criada ele excluía mais colunas A, C, D, E e H se não me engano (mas com a alteração do layout do relatório eu adaptei.

Acho que é mais fácil incrementar apenas para "excluir" as linhas com critério "continuação" e "saldo atual", mas, de toda forma, estou anexando uma pasta com as planilhas, passo a passo desde o início até o final caso eu tivesse que formatá-la manualmente.

Esse é um exemplo de relatório pequeno, tenho relatórios com 600, 700, 800 linhas, mas os critérios permanecem os mesmos.

Obrigado desde já

Olá Luiz,

Ficou meio longa, trabalhosa, mas o que importa é o trabalho que ela vai passar a poupar.

Gostaria que você a executasse com um relatório grande, já gerado manualmente, e comparasse os resultados, pois sempre há algo que pode ter falhado.

Sub AcertaRel()

'Fase 1
Ate = Cells(Rows.Count, "C").End(xlUp).Row

For Lin = 1 To Ate
'Limpa celulas que só tem espaços em branco
Cells(Lin, "A").Value = Trim(Cells(Lin, "A").Value)
Cells(Lin, "B").Value = Trim(Cells(Lin, "B").Value)

'Apaga cabeçalhos
If Mid(Cells(Lin, "B").Value, 18, 4) = "Pág:" Then
Rows(Lin & ":" & Lin + 5).Delete: GoTo Pula
End If

'Apaga Continuação e Saldo Atual
If Left(Cells(Lin, "B").Value, 11) = "Continuação" Or _
Left(Cells(Lin, "C").Value, 11) = "Saldo Atual" Then
Rows(Lin).Delete: GoTo Pula
End If

'Incorpora linhas que foram "quebradas"
If Application.WorksheetFunction.CountA(Rows(Lin)) = 1 And _
Left(Cells(Lin, "C").Value, 6) <> "Conta:" And _
Left(Cells(Lin, "C").Value, 6) <> "Centro" Then
Cells(Lin - 1, "C").Value = Cells(Lin - 1, "C").Value & Cells(Lin, "C").Value
Rows(Lin).Delete: GoTo Pula
End If
Lin = Lin + 1
Pula:
Lin = Lin - 1
Next

'Fase 2
'Apaga linhas em branco
Ate = Cells(Rows.Count, "C").End(xlUp).Row
Lin = 0
For Aux1 = 1 To Ate
Lin = Lin + 1
If Application.WorksheetFunction.CountA(Rows(Lin)) = 0 And _
Left(Cells(Lin + 1, "C").Value, 6) <> "Conta:" And _
Left(Cells(Lin + 1, "C").Value, 6) <> "Centro" Then
Rows(Lin).Delete: Lin = Lin - 1: Ate = Ate - 1
End If
Next

'Apaga colunas e formata
Columns("F").Delete Shift:=xlToLeft
Columns("A").Delete Shift:=xlToLeft

'Formata
Columns("A").HorizontalAlignment = xlCenter
Columns("A").ColumnWidth = 11
Columns("B").ColumnWidth = 66
Columns("C:D").NumberFormat = "#,##0.00"
Columns("C:D").ColumnWidth = 12

End Sub

Jimmy San Juan

Opa, vamos lá Jimmy...

No relatório que passei aqui no fórum realmente não teve nenhum problema, mas testando em um relatório maior algumas quebras de linhas não foram arrumadas.

Você programou a Macro para jogar a continuação do histórico para a linha de cima, correto? Pois então, preciso que essa continuação seja excluída mesmo, senão em cada relatório a coluna B vai ficar com um tamanho diferente =/ E esteticamente isso não vai ficar legal...

O problema que encontrei está associado com a continuação do histórico mesmo (a quebra de linha), segue uma imagem do que deu errado:

http://prntscr.com/m2muvu


Ps.: Gostei da formatação

Muito obrigado pela ajuda Jimmy,
Abraço!

Luiz, em vez da imagem, é melhor mandar a planilha e apontar onde foi o problema.

Assim posso alterar e simular.

Compreendo Jimmy,

Segue anexo. Na plan1 está o original e na plan2 está após executar a macro.

Luiz,

Tá parecendo que o problema está na importação de dados. Note que há 2 colunas de valores (as 2 últimas), crédito e saldo.
Há linhas em que o crédito está na coluna C (de descrição, que é o caso dos -330), na D (que é o correto), e até na E (que deveria ser de saldo)
Apesar do cabeçalho SALDO estar na coluna E, todos os valores estão na coluna F.
Me diga se minha conclusão está correta, ou se há realmente 4 colunas de valores, embora apenas 2 delas tenham cabeçalho.

Outro problema que notei, e fácil da macro contornar, é os valores de saldo tem uma letra no final, geralmente D, que impede que seja interpretado como valor pelo Excel. Como você está pedindo pra apagar essa coluna, ok.

Se apagar a "continuação" ficar bom pra você, ou seja, se o -330 é um valor irrelevante na planilha, então é só colocar a linha
'Cells(Lin - 1, "C").Value = Cells(Lin - 1, "C").Value & Cells(Lin, "C").Value
como comentário. Dessa forma apenas será apagada.

Por favor, dê retorno se funcionou fazer isso ou não.

Jimmy,

O padrão do relatório seria esse:


A......................B...................................C................D...........................E........
Data.................Histórico.......................Débito........Crédito................Saldo
01/01/1900.......Ref. Alguma coisa......... 100,00..................................100,00D
02/01/1900.......Ref. Alguma coisa 2.......................... 150,00 ............... 50,00C



Uma coluna para Data, Histórico, Débito, Crédito e Saldo

O relatório está saindo desconfigurado quando exporta para excel, parece que foi inserido uma coluna antes da "Coluna A" na planilha inteira, mas nos cabeçalhos "Data, Histórico, Débito, Crédito e Saldo" não, então eles ficam deslocados para a esquerda enquanto os textos ficam para a direita...
Não sei se consegui explicar, então estou enviando uma imagem: http://prntscr.com/m32giw

Quem sabe deslocando as linhas que começa com "Data" uma célula para a direita antes de começar a macro resolva o problema... O que você diz?

Sim, podemos incluir uma célula nas linhas que iniciam com DATA, como sugeriu, mas isso não explica o que um valor (-330) está fazendo na coluna C (Histórico). Será que ele fazia parte do nome da empresa "Vlr ref. CTRC n. 6813 de CLIENTE INDUSTRIA E COMERCIO DE RESINAS LTDA "? Se for isso, a solução que dei acima de inibir a linha (em verde) resolve.

Opssss...
Não adianta deslocar os títulos das colunas para a posição certa (linha que inicia com DATA) porque essa é uma das linhas que serão apagadas. Valeria a pena fazer se fosse uma linha que iria permanecer no relatório final. Você quer que ela seja mantida?

Sim, o "-330" é parte do nome da empresa (nesse caso), mas em geral se trata da quebra de linha. Vou verificar sua orientação em verde.

Fazendo a correção em verde (transformando em comentário) resolve o problema da quebra de linha, mas permanecem "sujando" a planilha: Centro de Custo: 7 - 1.07 São João Continuação...

Luiz, devagar vamos acertando.

Eu não entendi o que quis dizer com "sujando"!! Procurei pelo texto "Continuação" e não achei nada. Rodei a macro apenas sem a aquela linha em verde. Acho que agora uma daquelas figuras cairia bem

Bom dia,

Então, nesse relatório que anexei em outro post (que troquei o nome dos clientes) deu certo... mas no oficial que sai do sistema não está dando certo =/

"Sujeira" são essas quebras de linha, rsrs Aqui no escritório a gente costuma dizer que a planilha está "suja" quando ainda falta formatar...

Aqui está a imagem de como ficou com a formatação quando a planilha está sem substituir o nome dos clientes (do jeito que sai do sistema)...
http://prntscr.com/m40ky9

E outra coisa curiosa é que ele alterou algumas datas... de 03/12/2018 alterou para 12/03/2018 (como se fosse no modelo americano)... de 01/12 até 11/12 ele inverteu... 12/01 e 12/11
Veja as imagens:

Depois da macro: http://prntscr.com/m40l7u
Antes da macro: http://prntscr.com/m40lec

Ele está alterando o formato das datas?

Vou ver se consigo uma planilha de modelo para te enviar e você testar aí...

Opa Jimmy,

Veja o relatório anexo... estou executando a macro nela e está dando os erros que descrevi anteriormente (data invertida e linhas de "continuação"

PS.: O relatório que sai do sistema começa com 3 linhas em branco... e depois vem o conteúdo. Esse que te mandei, sempre que salvo em CSV (que é o formato que sai do sistema), ele elimina as 3 primeiras linhas brancas (não sei se isso influencia, mas de todo modo estou informando).

Abraços.

E segue o modelo em XLSX também, caso seja melhor. (Mas pra mim é melhor que a macro funcione no csv, pois é nativo do sistema)

Olá Luiz,

O problema das datas resolvi. O que ocorreu é que para identificar a linha com a CONTA ou CENTRO DE CUSTO, o critério era procurar linha que tivesse APENAS a coluna C preenchida. As linhas de CENTRO DE CUSTO podem ou não ter CONTINUAÇÃO, texto que aparece na célula B. Quando não há CONTINUAÇÃO, ou seja, quando B está vazia, a linha fica, caso contrário a linha é apagada. Agora o problema: Quando não há continuação, a célula NÃO está vazia, está aparentemente vazia, mas contém dezenas de espaços em branco. Isso atrapalhava a identificação. Então, utilizando o TRIM, eliminei esse problema, mas causei problema na data. Já mudei isso e agora está funcionando. Isso é assim para o modelo XLS mas não sei se vale para o CSV.

Quando às 3 linhas em branco antes do cabeçalho, como são excluídas antes da execução da macro, e a macro foi feita sem considerá-las, não interferem em nada.

O problema da CONTINUAÇÂO está ocorrendo porque o modelo de dados de entrada que nos baseamos tinha a palavra CONTINUACAO (quando fosse o caso) na coluna B, e o CENTRO DE CUSTO na C.

No dados que você está executando agora esses dados estão trocados de lugar, e a macro não esperava por isso.

Obs.: No formato CSV há diversas células (datas e valores) que não tem formatação, enquanto que no formato XLS têm.

A macro pode fazer qualquer coisa, desde que seja bem definida. Se você disser que hora os campos CONTINUAÇÃO e CENTRO DE CUSTO estarão trocados, e hora não, posso fazer a macro considerar isso, mas o importante pra que funcione é prever as possibilidades.

Por isso, peço que neste momento invista tempo na análise dos dados e formatos dos relatórios e entrada e na definição do que a macro receberá, e se certifique se o modelo CSV é realmente o melhor. Após isso adequamos a macro para que atenda essa definição.

Aguardo seu OK.

Opss...

Esqueci a correção da inversão de data.

Troque a linha
Cells(Lin, "B").Value = Trim(Cells(Lin, "B").Value)
por
If Trim(Cells(Lin, "B").Value) = "" Then Cells(Lin, "B").ClearContents

Ela fica no início da macro.

Jimmy,

Não sei se mosquei em algum procedimento aqui, mas com calma emiti os relatórios de todas as formas, fiz a correção da Macro e está rodando 100%

Muito obrigado pela ajuda!! Isso vai poupar muito tempo aqui =D

Muito obrigado mesmo!!