Extraindo dados de uma tabela para gerar relatório

joseguilherme

20-06-2016 19:55:00

Boa tarde,

Tenho uma base com um pouco mais de 40 mil linhas.
Postei a planilha em anexo.

1) Na aba "UF" preciso carregar o combobox através da Sub CarregaCategorias. Não estou conseguindo incluir na rotina Workbook_Open.
2) Ao selecionar a UF na listbox1, quando eu clico na caixa de opção município gostaria que aparecessem os municípios somente dessa UF (até ai eu consegui), porém sem duplicatas, preciso fazer tipo um consolidado de municípios dessa UF selecionada.
3) Preciso que na aba "UF", coluna "Disp" = soma dos valores da coluna "H" da plan "BASE".

Obrigado a todos!

Mikel Silveira Fraga

22-06-2016 13:22:20

Jose Guilherme, bom dia e seja bem vindo ao fórum.

Bem, segue respostas para suas suas dúvidas, conforme abaixo:

1ª DUVIDA
1) Na aba "UF" preciso carregar o combobox através da Sub CarregaCategorias. Não estou conseguindo incluir na rotina Workbook_Open.
Dentro da aba "UF", foi criada a planilha CarregaCategorias, mas a mesma estava como Private. Esse private não permite que outras Plan´s, Módulos e Userform´s, enxerguem a rotina criada. Dessa forma, você precisa apenas retirar a instrução Private que esta no inicio da declaração da rotina, ficando assim:

Código: Selecionar todos

Sub CarregaCategorias()
Após esta alteração, é necessário inserir o código em um evento Workbook_Open, direcionando em qual planilha se encontra a rotina a ser chamada, conforme abaixo:

Código: Selecionar todos

Private Sub Workbook_Open()
    Call Plan3.CarregaCategorias
End Sub
2ª DUVIDA
2) Ao selecionar a UF na listbox1, quando eu clico na caixa de opção município gostaria que aparecessem os municípios somente dessa UF (até ai eu consegui), porém sem duplicatas, preciso fazer tipo um consolidado de municípios dessa UF selecionada.
A alguns meses, desenvolvi um modelo que elimina duplicidades de preenchimento para ComboBox e ListBox. Fácil de ser adaptado. Segue abaixo o link do modelo para download:
- [MODELO] Verificação Duplicidade ao Carregar ComboBox e ListBox

3ª DUVIDA
3) Preciso que na aba "UF", coluna "Disp" = soma dos valores da coluna "H" da plan "BASE".
Para conseguir realizar essa soma, você pode utilizar a fórmula SomaSe, conforme instrução abaixo:

Código: Selecionar todos

=SOMASE(BASE!$C:$C;A6;BASE!$H:$H)
Veja se as instruções acima irão lhe ajudar e, quaisquer dúvidas, a disposição.

Forte abraço e excelente semana.

joseguilherme

22-06-2016 14:15:43

Mikel Silveira Fraga escreveu:Jose Guilherme, bom dia e seja bem vindo ao fórum.

Bem, segue respostas para suas suas dúvidas, conforme abaixo:

1ª DUVIDA
1) Na aba "UF" preciso carregar o combobox através da Sub CarregaCategorias. Não estou conseguindo incluir na rotina Workbook_Open.
Dentro da aba "UF", foi criada a planilha CarregaCategorias, mas a mesma estava como Private. Esse private não permite que outras Plan´s, Módulos e Userform´s, enxerguem a rotina criada. Dessa forma, você precisa apenas retirar a instrução Private que esta no inicio da declaração da rotina, ficando assim:

Código: Selecionar todos

Sub CarregaCategorias()
Após esta alteração, é necessário inserir o código em um evento Workbook_Open, direcionando em qual planilha se encontra a rotina a ser chamada, conforme abaixo:

Código: Selecionar todos

Private Sub Workbook_Open()
    Call Plan3.CarregaCategorias
End Sub
2ª DUVIDA
2) Ao selecionar a UF na listbox1, quando eu clico na caixa de opção município gostaria que aparecessem os municípios somente dessa UF (até ai eu consegui), porém sem duplicatas, preciso fazer tipo um consolidado de municípios dessa UF selecionada.
A alguns meses, desenvolvi um modelo que elimina duplicidades de preenchimento para ComboBox e ListBox. Fácil de ser adaptado. Segue abaixo o link do modelo para download:
- [MODELO] Verificação Duplicidade ao Carregar ComboBox e ListBox

3ª DUVIDA
3) Preciso que na aba "UF", coluna "Disp" = soma dos valores da coluna "H" da plan "BASE".
Para conseguir realizar essa soma, você pode utilizar a fórmula SomaSe, conforme instrução abaixo:

Código: Selecionar todos

=SOMASE(BASE!$C:$C;A6;BASE!$H:$H)
Veja se as instruções acima irão lhe ajudar e, quaisquer dúvidas, a disposição.

Forte abraço e excelente semana.
Primeiramente obrigado pela atenção e disposição em ajudar!

Quanto a 1ª dúvida, realmente havia esquecido desse detalhe. Já consegui resolver!!

2ª dúvida

Creio que não expliquei da melhor forma. Na verdade a duplicidade não está na listbox. Ao selecionar um estado na listbox, e depois disso selecionar a opção municipio (Na aba "UF"), uma rotina extrai da base apenas os municípios do estado selecionado na listbox. Porém essa informação vem com duplicatas, preciso que venha de uma forma "consolidada". É possível ?

3ª dúvida

Tem como fazer essa função usando código vba, de forma que a função se aplique até a última célula de um Range ? Porque se eu colocar por fórmula, e depois copiar e colar a fórmula através do vba minha planilha vai ficar muito lenta.

Obrigado mais uma vez pela ajuda !

Mikel Silveira Fraga

22-06-2016 17:01:16

Jose Guilherme, boa tarde.

Respondendo as duas dúvidas em um único código, substitua a parte do código:

Código: Selecionar todos

            If .Range("A" & i) = ListBox1.Value Then
                Plan3.Range("A" & j) = .Range("C" & i)
                j = j + 1
            End If
Pelo código:

Código: Selecionar todos

            If .Range("A" & i) = ListBox1.Value Then
            
                'Testa se já existe o municipio atual.
                If Application.WorksheetFunction _
                        .CountIf(Me.Columns(1), .Range("C" & i).Value) <= 0 Then
                    
                    Plan3.Range("A" & j) = .Range("C" & i)
                    
                    'Realiza um SomaSe, utilizando a função SumIf da classe WorksheetFunction.
                    Plan3.Range("B" & j).Value = VBA.CDbl(VBA.Format(Application.WorksheetFunction _
                        .SumIf(.Columns(3), Me.Range("A" & j).Value, .Columns(8)), "#,##0.00"))
                    
                    Me.Columns(1).AutoFit
                    j = j + 1
                    
                End If
            
            End If
Neste código, já estão sendo tratados a duplicidade e o SomaSe via VBA.

Fiz a adaptação do código apenas para o Município. Deixei para que você fizesse a mudança como Célula, para que possa praticar e entender o código.

Abraços e excelente semana.

joseguilherme

23-06-2016 14:57:57

Mikel Silveira Fraga escreveu:Jose Guilherme, boa tarde.

Respondendo as duas dúvidas em um único código, substitua a parte do código:

Código: Selecionar todos

            If .Range("A" & i) = ListBox1.Value Then
                Plan3.Range("A" & j) = .Range("C" & i)
                j = j + 1
            End If
Pelo código:

Código: Selecionar todos

            If .Range("A" & i) = ListBox1.Value Then
            
                'Testa se já existe o municipio atual.
                If Application.WorksheetFunction _
                        .CountIf(Me.Columns(1), .Range("C" & i).Value) <= 0 Then
                    
                    Plan3.Range("A" & j) = .Range("C" & i)
                    
                    'Realiza um SomaSe, utilizando a função SumIf da classe WorksheetFunction.
                    Plan3.Range("B" & j).Value = VBA.CDbl(VBA.Format(Application.WorksheetFunction _
                        .SumIf(.Columns(3), Me.Range("A" & j).Value, .Columns(8)), "#,##0.00"))
                    
                    Me.Columns(1).AutoFit
                    j = j + 1
                    
                End If
            
            End If
Neste código, já estão sendo tratados a duplicidade e o SomaSe via VBA.

Fiz a adaptação do código apenas para o Município. Deixei para que você fizesse a mudança como Célula, para que possa praticar e entender o código.

Abraços e excelente semana.
Funcionou perfeitamente, muito obrigado mesmo !! Excelente!!
Agora to com uma dificuldade pra conseguir dividir cada célula da coluna "B" pela célula da coluna "C".
Já tentei usar o "For Each" e o "For" mas não consegui acertar a sintaxe.

Abraços e boa semana !

Mikel Silveira Fraga

23-06-2016 17:31:29

Jose Guilherme, boa tarde.

Bom saber que funcionou.

Bem, sobre essa nova dúvida, eu realmente não entendei o que você esta querendo fazer. Pode dar mais detalhes?

Abraços.

joseguilherme

23-06-2016 19:07:13

Na verdade eu já consegui também.
O que eu queria era na coluna "D" ter o resultado da coluna "B" / coluna "C".

Usei:

Código: Selecionar todos

For j = 6 To Plan3.Range("A" & Rows.Count).End(xlUp).Row
    Plan3.Range("D" & j) = Plan3.Range("B" & j).Value / Plan3.Range("C" & j).Value
Next j
E na coluna "E" eu precisava do percentual que cada valor representava no total de cada estado, pra isso alterei o SumIf.
Acrescentei ao seu código:

Código: Selecionar todos

Plan3.Range("E" & j).Value = Plan3.Range("C" & j).Value / VBA.CDbl(VBA.Format(Application.WorksheetFunction _
                        .SumIf(.Columns(1), ListBox1.Value, .Columns(9)), "#,##0.00"))
Muito obrigado novamente!
Bom saber que podemos contar com pessoas como vc aqui nesse fórum. :D

joseguilherme

23-06-2016 19:22:00

Mikel Silveira Fraga escreveu:Jose Guilherme, boa tarde.

Bom saber que funcionou.

Bem, sobre essa nova dúvida, eu realmente não entendei o que você esta querendo fazer. Pode dar mais detalhes?

Abraços.
Acabei de encontrar uma falha, o SumIf está somando o total geral do municipio, desconsiderando o estado selecionado.
Nesse caso o valor fica incorreto, como poderia fazer ? Teria que ser algo como um "SOMASES" com critério do estado selecionado e do municipio.