Tópicos relacionados a códigos VBA, gravação de macros, etc.
Por SandroLima 07 Abr 2019 às 21:19
Membro 5 Estrelas
Mensagens: 565
Reputação: 11
#42797
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 todosIf 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. :cry: :cry:

Segue planilha em anexo.
Apenas usuários registrados podem ver ou baixar anexos.
Avatar do usuário
Por Jimmy 08 Abr 2019 às 14:01
Membro 5 Estrelas
Mensagens: 753
Reputação: 478
#42811
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
Por SandroLima 08 Abr 2019 às 15:46
Membro 5 Estrelas
Mensagens: 565
Reputação: 11
#42818
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[color=#0000FF] 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?[/color]

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.
Apenas usuários registrados podem ver ou baixar anexos.
Avatar do usuário
Por Jimmy 08 Abr 2019 às 16:17
Membro 5 Estrelas
Mensagens: 753
Reputação: 478
#42821
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 !?!?!!
Por SandroLima 08 Abr 2019 às 17:19
Membro 5 Estrelas
Mensagens: 565
Reputação: 11
#42826
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.
Apenas usuários registrados podem ver ou baixar anexos.
Avatar do usuário
Por Jimmy 08 Abr 2019 às 18:03
Membro 5 Estrelas
Mensagens: 753
Reputação: 478
#42831
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?
Por SandroLima 08 Abr 2019 às 18:18
Membro 5 Estrelas
Mensagens: 565
Reputação: 11
#42832
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.
Avatar do usuário
Por Jimmy 09 Abr 2019 às 13:10
Membro 5 Estrelas
Mensagens: 753
Reputação: 478
#42865
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?
Por SandroLima 09 Abr 2019 às 15:20
Membro 5 Estrelas
Mensagens: 565
Reputação: 11
#42878
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.
Avatar do usuário
Por Jimmy 10 Abr 2019 às 13:44
Membro 5 Estrelas
Mensagens: 753
Reputação: 478
#42914
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
Apenas usuários registrados podem ver ou baixar anexos.
Editado pela última vez por Jimmy em 12 Abr 2019 às 02:48, em um total de 1 vez.
Por SandroLima 10 Abr 2019 às 21:25
Membro 5 Estrelas
Mensagens: 565
Reputação: 11
#42937
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.
Avatar do usuário
Por Jimmy 10 Abr 2019 às 23:56
Membro 5 Estrelas
Mensagens: 753
Reputação: 478
#42942
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
Apenas usuários registrados podem ver ou baixar anexos.
Por SandroLima 11 Abr 2019 às 18:10
Membro 5 Estrelas
Mensagens: 565
Reputação: 11
#42989
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.
Por SandroLima 11 Abr 2019 às 22:18
Membro 5 Estrelas
Mensagens: 565
Reputação: 11
#42993
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.
Apenas usuários registrados podem ver ou baixar anexos.
Avatar do usuário
Por Jimmy 12 Abr 2019 às 03:06
Membro 5 Estrelas
Mensagens: 753
Reputação: 478
#42994
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
Por SandroLima 12 Abr 2019 às 11:25
Membro 5 Estrelas
Mensagens: 565
Reputação: 11
#42999
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.
Por SandroLima 12 Abr 2019 às 19:21
Membro 5 Estrelas
Mensagens: 565
Reputação: 11
#43015
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?
Avatar do usuário
Por Jimmy 13 Abr 2019 às 02:14
Membro 5 Estrelas
Mensagens: 753
Reputação: 478
#43019
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 todosSub 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
Apenas usuários registrados podem ver ou baixar anexos.
Editado pela última vez por Jimmy em 13 Abr 2019 às 11:24, em um total de 3 vezes.
Por SandroLima 13 Abr 2019 às 11:07
Membro 5 Estrelas
Mensagens: 565
Reputação: 11
#43023
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.
Avatar do usuário
Por Jimmy 13 Abr 2019 às 13:40
Membro 5 Estrelas
Mensagens: 753
Reputação: 478
#43026
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