Guru do Excel e Power BI

Boa noite, usuários e colaboradores do fórum.

Usando como banco de dados a tabela da aba "ATIVIDADES DIÁRIAS" preciso gerar um relatório na aba "FLUXO DE CAIXA - RESUMIDO" que consta de 12 tabelas (referentes aos meses do ano).
Esse relatório será enviado nesse formatação ao escritório de contabilidade.

Pretendo fazer isso com o auxílio de matrizes VBA.

A idéia da macro funcionou parcialmente. Não consegui fazer com que a macro selecione a tabela de destino conforme o Mês correspondente à data.

Tentei com a seguinte trecho do código, mas não funcionou:

Código: Selecionar todos

If Dados(NumLinha, colMes) & Dados(NumLinha, colAno) = _
        "Janeiro" & wshFCAnual.Range("AnoReferencia_FC_Anual").Value Then
        Set TabelaDestino = Tabela_JAN
    ElseIf Dados(NumLinha, colMes) & Dados(NumLinha, colAno) = _
        "Fevereiro" & wshFCAnual.Range("AnoReferencia_FC_Anual").Value Then
        Set TabelaDestino = Tabela_FEV
    ElseIf Dados(NumLinha, colMes) & Dados(NumLinha, colAno) = _
        "Março" & wshFCAnual.Range("AnoReferencia_FC_Anual").Value Then
        Set TabelaDestino = Tabela_MAR
    ElseIf Dados(NumLinha, colMes) & Dados(NumLinha, colAno) = _
        "Abril" & wshFCAnual.Range("AnoReferencia_FC_Anual").Value Then
        Set TabelaDestino = Tabela_ABR
    ElseIf Dados(NumLinha, colMes) & Dados(NumLinha, colAno) = _
        "Maio" & wshFCAnual.Range("AnoReferencia_FC_Anual").Value Then
        Set TabelaDestino = Tabela_MAI
    ElseIf Dados(NumLinha, colMes) & Dados(NumLinha, colAno) = _
        "Junho" & wshFCAnual.Range("AnoReferencia_FC_Anual").Value Then
        Set TabelaDestino = Tabela_JUN
    ElseIf Dados(NumLinha, colMes) & Dados(NumLinha, colAno) = _
        "Julho" & wshFCAnual.Range("AnoReferencia_RelResumido").Value Then
        Set TabelaDestino = Tabela_JUL
    ElseIf Dados(NumLinha, colMes) & Dados(NumLinha, colAno) = _
        "Agosto" & wshFCAnual.Range("AnoReferencia_FC_Anual").Value Then
        Set TabelaDestino = Tabela_AGO
    ElseIf Dados(NumLinha, colMes) & Dados(NumLinha, colAno) = _
        "Setembro" & wshFCAnual.Range("AnoReferencia_FC_Anual").Value Then
        Set TabelaDestino = Tabela_SET
    ElseIf Dados(NumLinha, colMes) & Dados(NumLinha, colAno) = _
        "Outubro" & wshFCAnual.Range("AnoReferencia_FC_Anual").Value Then
        Set TabelaDestino = Tabela_OUT
    ElseIf Dados(NumLinha, colMes) & Dados(NumLinha, colAno) = _
        "Novembro" & wshFCAnual.Range("AnoReferencia_FC_Anual").Value Then
        Set TabelaDestino = Tabela_NOV
    ElseIf Dados(NumLinha, colMes) & Dados(NumLinha, colAno) = _
        "Dezembro" & wshFCAnual.Range("AnoReferencia_FC_Anual").Value Then
        Set TabelaDestino = Tabela_DEZ
    End If

    With TabelaDestino
        With .ListRows.Add
            .Range(1, 1).Resize(LinCriterio) = Registro
            .Range(1, 5).Resize(LinCriterio) = Data
        End With
    End With

Gostaria de pedir a ajuda de vocês que são feras em Excel e que puder me ajudar.

Para efeito de teste peço que selecionem o ano de 2018 na região superior da aba "FLUXO DE CAIXA - RESUMIDO".

Podem me ajudar??? Preciso disso urgentemente.

Segue planilha em anexo.

Olá Sandro,

Aqui há um problema de lógica, e não sintaxe, logo, pra corrigir é preciso entender a planilha, o que levaria horas pra quem não tem ideia do que se trata, mas pra você não deve ser tão difícil assim.

Vou te apontar um erro que percebi, mas pra corrigir teria que ser você mesmo, que sabe o que a planilha faz.

Pra mim o problema está aqui:
If Dados(NumLinha, colMes) & Dados(NumLinha, colAno) = _
"Janeiro" & wshFCAnual.Range("AnoReferencia_FC_Anual").Value Then

Essa variável em vermelho, antes dessa linha, foi usada em um FOR. Quando faz um FOR e não o aborta com EXIT FOR, ao encerrar naturalmente, a variável assume o último valor , somado de um.
Ex: For A = 1 to 10
após encerrar o FOR, A terá o valor de 11.

O seu FOR é For NumLinha = 1 To TotalLinhas, logo, NumLinha terá valor TotalLinhas+1, que está fora da faixa da matriz DADOS (ela vai até TotalLinhas, e não TotalLinhas+1).

Posso dar um chute: tente acrescentar - 1 após cada uma NumLinha em ver vermelho.
Verifique e corrija isso e veja o que acontece.

Apenas pra simplificar o extenso IF ELSEIF, proponho usar uma variável auxiliar, como segue:

Código: Selecionar todos

    If Dados(NumLinha, colAno) = wshFCAnual.Range("AnoReferencia_FC_Anual").Value Then
        Aux1 = Dados(NumLinha, colMes)
        If Aux1 = "Janeiro" & Aux2 Then
                                         Set TabelaDestino = Tabela_JAN
        ElseIf Aux1 = "Fevereiro" Then:  Set TabelaDestino = Tabela_FEV
        ElseIf Aux1 = "Março" Then:      Set TabelaDestino = Tabela_MAR
        ElseIf Aux1 = "Abril" Then:      Set TabelaDestino = Tabela_ABR
        ElseIf Aux1 = "Maio" Then:       Set TabelaDestino = Tabela_MAI
        ElseIf Aux1 = "Junho" Then:      Set TabelaDestino = Tabela_JUN
        ElseIf Aux1 = "Julho" Then:      Set TabelaDestino = Tabela_JUL
        ElseIf Aux1 = "Agosto" Then:     Set TabelaDestino = Tabela_AGO
        ElseIf Aux1 = "Setembro" Then:   Set TabelaDestino = Tabela_SET
        ElseIf Aux1 = "Outubro" Then:    Set TabelaDestino = Tabela_OUT
        ElseIf Aux1 = "Novembro" Then:   Set TabelaDestino = Tabela_NOV
        ElseIf Aux1 = "Dezembro" Then:   Set TabelaDestino = Tabela_DEZ
        End If
    End If

Jimmy San Juan

Boa tarde, Jimmy

Obrigado pela intenção de ajudar. Vou tentar explicar melhor para ver se vc ou alguém mais pode continuar me ajudando.

Em tempo:
Incorporei as sugestões que vc mencionou.
A simplificação que vc sugeriu para o bloco IF ELSEIF foi implementada.

Declarei a variável Aux1 como string.... está correto ou não há necessidade?
E a variável Aux2... essa eu não entendi. Tenho que declará-la também?

Fiz essa macro baseada em outra macro que filtra os resultados baseados em uma condição.

Originalmente, a macro gera duas matrizes (Coluna "Registro" e Coluna "Pgto / Vencimento") com os dados que atendem uma condição e copia os dados das matrizes para uma ÚNICA tabela de outra aba da mesma planilha.
Aqui nessa situação a condição seria o ano selecionado.
A macro então copia os dados das matrizes para uma nova tabela em outra aba da mesma planilha.

O que preciso como adaptação:
- Que a macro separe e copie os dados em doze tabelas diferentes (referentes aos meses do ano) da aba "FLUXO DE CAIXA - RESUMIDO". Essa aba segue o formato que preciso enviar para o escritório de contabilidade.

Não sei se está correto o raciocínio:
Devo manter somente as matrizes "Registro" e "Pgto / Vencimento" e criar condições para selecionar a tabela de destino?
Ou deveriam ser geradas matrizes diferentes para cada tabela de destino?

Pode me ajudar com isso ou alguém mais do fórum?

Segue a planilha com as modificações sugeridas.

Sandro,
Após postar a planilha, por acaso você editou o post, e alterou a planilha? Eu a estava baixando, e ela estava vazia. De repente começou a vir com mais dados !?!?!!

Boa tarde, Jimmy.

No meu tópico de resposta eu postei a planilha com as alterações que você sugeriu somente.

Na aba "FLUXO DE CAIXA - RESUMIDO" a coluna registro está com uma fórmula que retorna os valores da coluna.

Ao rodar o código a fórmula é substituída pelos valores da matriz de dados.

Segue a planilha novamente APENAS com a alteração sugerida no código.

As matrizes REGISTRO e DATA são jogadas na planilha.

Pergunta:
- Elas são separadas por meses?
- Em que lugar da planilha elas devem ser jogadas? Qual o critério? O local depende do mês?

Pergunta:
- Elas são separadas por meses?

Na aba "ATIVIDADES DIÁRIAS" as duas últimas colunas são referentes ao Mês e Ano da coluna "Pgto / Vencimento" da mesma tabela.
As matrizes "Registro" (com os dados da coluna Registro) e "Data" (com os dados da coluna "Pgto / Vencimento") são geradas com os dados das respectivas colunas dessa tabela.

- Em que lugar da planilha elas devem ser jogadas? Qual o critério? O local depende do mês?

Os dados das matrizes geradas devem ser copiados para as tabelas do Mês correspondente.
sim... A aba "FLUXO DE CAIXA - RESUMIDO" possui 12 tabelas referentes aos meses:
- TB_FluxoAnual_JAN
- TB_FluxoAnual_FEV
- TB_FluxoAnual_MAR
- e assim sucessivamente... até TB_FluxoAnual_DEZ

Estou à disposição para novas dúvidas. Muito obrigado.

Mais perguntas.

Se FLUXO DE CAIXA RESUMIDO recebe dados da macro, que os cola a partir de uma matriz, porque esses valores na planilha que você postou tem fórmulas e não valores?

A cada execução, a planilha FLUXO DE CAIXA RESUMIDO deve ser limpa, e novos dados colocados, ou os novos dados devem ser acrescentados aos já existentes?

Se FLUXO DE CAIXA RESUMIDO recebe dados da macro, que os cola a partir de uma matriz, porque esses valores na planilha que você postou tem fórmulas e não valores?

A fórmula teve dois motivos:
1) Estava tentando fazer o processo através de fórmulas (essa que está na tabela) e uma macro que conta os itens e adiciona ou deleta linhas na tabela conforme a quantidade de itens (porém o processo ficou demasiadamente demorado).
2) O outro motivo é pq estava conferindo se todos os valores estavam sendo colados nas tabelas de destino.

A cada execução, a planilha FLUXO DE CAIXA RESUMIDO deve ser limpa, e novos dados colocados, ou os novos dados devem ser acrescentados aos já existentes?

O ideal é que a planilha seja limpa a cada execução pois alguns campos da tabela de origem podem ser alterados.
Mas gostaria de testar a alternativa com a segunda opção também... se possível, claro.

Obrigado pela atenção e boa tarde.

Sandro,

Teste a planilha anexa.

Tirei a tabela auxiliar da planilha do RESUMIDO porque atrapalhava a inserção de linhas nos meses de jan a março.

Não sei por qual motivo a performance do Excel cai à medida que se incluem e deletam linhas de uma tabela. Se executar várias vezes seguidas vai ver que o tempo de execução aumenta um pouco cada vez que a macro é executada. Se salvar e sair da planilha, a performance volta ao normal.

Dê um retorno depois. Pode ser que faltem ajustes.

Jimmy San Juan

Boa noite, Jimmy.

Muito obrigado pela atenção e pela grande ajuda.

Tirei a tabela auxiliar da planilha do RESUMIDO porque atrapalhava a inserção de linhas nos meses de jan a março.

Ok... não tem problema... só acrescentei a tabela para ajudar na macro.

Dê um retorno depois. Pode ser que faltem ajustes.

Vou aplicar na minha planilha de trabalho e lhe darei retorno.

Desde já se não for pedir muito e eu sei que pode ser trabalhoso pois o código é extenso... mas como pode perceber eu comento ou peço comentários nos códigos para facilitar o meu aprendizado (e demais usuários do fórum) e permitir entender caso necessite fazer alguma alteração por exemplo na posição de alguma coluna.

Se pudesse fazer mais essa gentileza... mas claro, se não for pedir demais.

Seu código está cheio de coisas novas para mim.

Mas apenas se puder mesmo... sei que nem todos tem tempo disponível.

Farei os devidos testes e darei o retorno.

Mais uma vez muito obrigado.

Sandro,

Comentei muitas das linhas que inseri.

Durante esse processo, acabei retirando mais algumas coisas inúteis, e mudei um pouco a macro. Assim, desconsidere a anterior. Retirei a inibição dos eventos porque não achei necessário, e evita problemas caso a macro cancele e não tenha oportunidade de reativar os eventos.

As coisas novas pra você, pergunte que eu te explico.

Jimmy San Juan

Boa tarde, Jimmy.

Obrigado por atender ao pedido e esforço em melhorar a macro.

Assim, desconsidere a anterior.

Ok... farei isso.

As coisas novas pra você, pergunte que eu te explico.

Muito obrigado por isso também.

Vou estudar o código e testar... qualquer coisa peço ajuda.

Por enquanto vou manter o tópico aberto.

Boa noite, Jimmy

Ao testar o código aqui na minha planilha de trabalho está dando um "bug". Os dados estão ficando fora da tabela.

O que estou fazendo de errado?

Segue a planilha em anexo.

Oi Sandro,

Jimmy escreveu: Tirei a tabela auxiliar da planilha do RESUMIDO porque atrapalhava a inserção de linhas nos meses de jan a março.

É isso que está atrapalhando. A tab auxiliar voltou pra planilha de Resumido. Se você precisa mesmo que ela fique ali, posso trocar a inserção de linhas por inserção de células, mas isso vai dificultar a manutençao da macro, por quem não conhece bem VBA, no caso de aumentarem ou diminuirem a quantidade de colunas na Resumido.

Jimmy San Juan

Grande, Jimmy.

É isso que está atrapalhando. A tab auxiliar voltou pra planilha de Resumido.

Muito obrigado pelo apontamento... não me atentei para isso que você já havia mencionado antes. E de fato era só isso.

Se você precisa mesmo que ela fique ali, posso trocar a inserção de linhas por inserção de células

Na verdade, não.
Posso suprimi-la ou mudá-la de local perfeitamente.
Ela serve simplesmente para a conferência da quantidade de resultados da macro e também estou testando uma outra opção de resultados em que utilizo os valores da tabela auxiliar para a contagem de linhas.

Muito obrigado pela presteza.

Agora vou testar a macro de fato. Manterei o tópico em aberto por enquanto.

Tenha um bom dia.

Boa tarde, Jimmy.

Ao continuar os testes aqui observei mais um detalhe.

Observe que em alguns registros... ao copiar os valores para as tabelas de destino está havendo a inversão entre dia e mês da data.

Formato dd/mm/aaaa indo para mm/dd/aaaa.

Pode verificar, por favor?

Olá Sandro,

O problema está sendo causado pelo Transpose. Ele Deveria apenas inverter a planilha, mas está também alterando a data.
Resolvi fazendo um Transpose manual, usando FOR/NEXT, o que deixou a planilha mais lenta. Alterei a parte de inserção de linhas, que ficou mais simples e rápida, o que compensou em parte a lentidão causada pelo FOR.

Coloco abaixo uma rotina simples que simula o erro causado pelo Transpose, para o caso de alguém se animar e tentar resolver.

Código: Selecionar todos

Sub Pau()
    Dim AA(1 To 3)                 'Monto uma matriz de 1 dimensão, e 2 elementos
    AA(1) = 15                     'Ponho conteúdo numérico
    AA(2) = "Excel"                'Ponho conteúdo alfabético
    AA(3) = DateSerial(2019, 4, 5) 'Ponho conteúdo de data
    
    Range("H5:J10").Clear           'Limpo a área
    Range("H5:J5") = AA            'Jogo a matriz na planilha, na horizontal. Funciona.
    Range("H8:H10") = Application.Transpose(AA) 'Jogo a matriz na planilha, na vertical. A data fica invertida.
    
End Sub

Outro assunto: você tem na maco a instrução ON ERROR RESUME NEXT. Ela não evita o erro, apenas evita o aviso e a parada da execução. Por exemplo, se uma das linhas da planilha de atividades diárias não tiver mês digitado na coluna AL (ou estiver digitado errado, por exemplo), a macro não vai parar e sabe-se lá o que fará. O melhor seria não usar essa instrução, para tomar conhecimento quando um erro ocorrer.

Jimmy San Juan

Bom dia, Jimmy.

Alterei a parte de inserção de linhas, que ficou mais simples e rápida

Obrigado pelo esforço em melhorar a macro. Agradeço muito mesmo.
É uma rotina que vai ser de muita utilidade para mim.

Outro assunto: você tem na maco a instrução ON ERROR RESUME NEXT. Ela não evita o erro, apenas evita o aviso e a parada da execução. Por exemplo, se uma das linhas da planilha de atividades diárias não tiver mês digitado na coluna AL (ou estiver digitado errado, por exemplo), a macro não vai parar e sabe-se lá o que fará. O melhor seria não usar essa instrução, para tomar conhecimento quando um erro ocorrer.

Sugestão incrementada.

Continuarei fazendo os testes e retorno com qualquer alteração verificada.

Coloco abaixo uma rotina simples que simula o erro causado pelo Transpose, para o caso de alguém se animar e tentar resolver.

Vamos manter o tópico aberto para ver se alguém apresenta uma solução.

Mais uma vez obrigado e tenham um excelente final de semana.

Ok, mas independente da solução do Transpose, a macro deve funcionar bem, apenas com um pouco mais de lentidão.

Qual o volume de dados, no máximo, que essa planilha vai trabalhar? Se não for algo muito maior do que 1000, acho que dá até pra ficar assim, se não houver solução, porque em vez de demorar 1 segundo com o Transpose, fai demorar 5 segundos com o FOR/NEXT. Não é impeditivo.

Independente disso tudo, você pode ir testando pra ver se outros problemas aparecem.

Bom findi pra você também.

Jimmy San Juan

Guru do Excel e Power BI

Segregar dados em tabelas - Matriz VBA

Segregar dados em tabelas - Matriz VBA

Re: Segregar dados em tabelas - Matriz VBA

Re: Segregar dados em tabelas - Matriz VBA

Segregar dados em tabelas - Matriz VBA

Re: Segregar dados em tabelas - Matriz VBA

Segregar dados em tabelas - Matriz VBA

Re: Segregar dados em tabelas - Matriz VBA

Segregar dados em tabelas - Matriz VBA

Re: Segregar dados em tabelas - Matriz VBA

Re: Segregar dados em tabelas - Matriz VBA

Re: Segregar dados em tabelas - Matriz VBA

Re: Segregar dados em tabelas - Matriz VBA

Re: Segregar dados em tabelas - Matriz VBA

Re: Segregar dados em tabelas - Matriz VBA

Re: Segregar dados em tabelas - Matriz VBA

Re: Segregar dados em tabelas - Matriz VBA

Re: Segregar dados em tabelas - Matriz VBA

Re: Segregar dados em tabelas - Matriz VBA

Re: Segregar dados em tabelas - Matriz VBA

Segregar dados em tabelas - Matriz VBA