Como fazer os itens do Listbox procurar em várias planilhas?

AleGrotto

23-08-2010 17:59:00

Amigos, Boa tarde!

O código abaixo que criei, pega os itens selecionados de uma ListBox procura estes itens em outras 4 planilhas. Quando os itens são encontrados, algumas informações são selecionadas e coladas em uma outra planilha chamada de "Apoio" que executa algumas operações e finalmente cola o resultado na planilha final.

O problema é que não consegui fazer funcionar para todos os itens da ListBox, ou seja, ele para no primeiro item.

Como posso fazer para que todos os itens selecionados em uma ListBox sejam procurados?

Agradeço antecipadamente a atenção.

Segue abaixo o código:

Private Sub OkEstAt_Click()


Sheets("Apoio").Activate
Columns("A:J").Select
Selection.ClearContents

'Retira dados da Plan1 e cola na Apoio
Dim i As Integer
For i = 0 To ItemEstAt.ListCount - 1
If ItemEstAt.Selected(i) Then
Sheets("Plan1").Activate
ActiveSheet.Range("$A$1:$F$1009").AutoFilter Field:=1
ActiveSheet.Range("$A$1:$F$1009").AutoFilter Field:=2
ActiveSheet.Range("$A$1:$F$1009").AutoFilter Field:=3
ActiveSheet.Range("$A$1:$F$1009").AutoFilter Field:=4
ActiveSheet.Range("$A$1:$F$1009").AutoFilter Field:=5
ActiveSheet.Range("$A$1:$F$1009").AutoFilter Field:=6
ActiveSheet.Range("$A$1:$F$1009").AutoFilter Field:=3, Criteria1:=ItemEstAt.List(i), _
Operator:=xlAnd

Columns("A:F").Select
Selection.Copy
Sheets("Apoio").Select
Range("A:A").Select
ActiveSheet.Paste


'Retira dados da Plan3 e cola na Apoio

Sheets("Plan3").Activate
ActiveSheet.Range("$A$1:$F$1009").AutoFilter Field:=1
ActiveSheet.Range("$A$1:$F$1009").AutoFilter Field:=2
ActiveSheet.Range("$A$1:$F$1009").AutoFilter Field:=1, Criteria1:=ItemEstAt.List(i), _
Operator:=xlAnd


Columns("B:B").Select
Selection.Copy
Sheets("Apoio").Select
Range("G:G").Select
ActiveSheet.Paste


'Retira dados da Planilha Baixa e cola na Apoio
Sheets("Baixa").Activate
ActiveSheet.Range("$A$1:$F$1009").AutoFilter Field:=1
ActiveSheet.Range("$A$1:$F$1009").AutoFilter Field:=2
ActiveSheet.Range("$A$1:$F$1009").AutoFilter Field:=3
ActiveSheet.Range("$A$1:$F$1009").AutoFilter Field:=2, Criteria1:=ItemEstAt.List(i), _
Operator:=xlAnd

Columns("C:C").Select
Selection.Copy
Sheets("Apoio").Select
Range("H:H").Select
ActiveSheet.Paste

'Retira dados da Planilha Contagem e cola na Apoio
Sheets("Contagem").Activate
ActiveSheet.Range("$A$1:$F$1009").AutoFilter Field:=1
ActiveSheet.Range("$A$1:$F$1009").AutoFilter Field:=2
ActiveSheet.Range("$A$1:$F$1009").AutoFilter Field:=3
ActiveSheet.Range("$A$1:$F$1009").AutoFilter Field:=1, Criteria1:=ItemEstAt.List(i), _
Operator:=xlAnd

Columns("B:C").Select
Selection.Copy
Sheets("Apoio").Select
Range("I:I").Select
ActiveSheet.Paste

'Cola os dados selecionados da planilha Apoio na Planilha Estoque Atual
Sheets("Apoio").Select
Rows("2:2").Select
Selection.Copy
Sheets("EstoqueAtual").Select
ProximaCelula = Application.WorksheetFunction.CountA(Range("A:A")) + 1
Cells(ProximaCelula, 1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

End If
Next i

AleGrotto

23-08-2010 22:20:17

Descobri que o problema acontece porque na linha
If ItemEstAt.Selected(i) Then
O ItemEstAt.Selected(i) muda de Verdadeiro para Falso quando ele abre a planilha seguinte e os itens da ListBox não ficam selecionados.

É possível resolver isso?

Abraço
Alexandre

Mauro Coutinho

23-08-2010 23:37:53

Alexandre, a principio a lógica está correta, apesar de ter muitas linhas que poderiam ser reduzidas e eliminar chamadas repetidas, fiz um teste com sua rotina sem os AutoFilter e funcionou corretamente.

Como não colocou qual a definição está usando na última Linha para ProximaCelula, então acredito que o problema esteja ai, aonde provavelmente tenha outra Variavel (i) associada a ProximaCelula, ffazendo com que a Variavel (i) do Listbox se perca, desabilite essa linha:
ProximaCelula = Application.WorksheetFunction.CountA(Range("A:A")) + 1
Cells(ProximaCelula, 1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

e veja se retorna os dados certos, se ainda não funcionar, veja se não tem alguma outra referencia a Variavel (i) como Global.

Algumas considerações de como reduzir a rotina :
Aonde temos :
Sheets("Apoio").Activate
Columns("A:J").Select
Selection.ClearContents

Nas linhas acima reduzimos da seguinte forma:
Sheets("Apoio").Columns("A:J").ClearContents
Não é necessario usar o Activate o Select e nem o Selection.

Aonde temos :
Columns("A:F").Select
Selection.Copy

Sheets("Apoio").Select
Range("A:A").Select
ActiveSheet.Paste

Trocariámos tudos isso por :
Columns("A:F").Copy Destination:=Sheets("Apoio").Range("A:A")

Veja tambem que depois da linha:
Sheets("Contagem").Activate

Voce usa duas vezes seguida a chamada :
Sheets("Apoio").Select

Agora com as dicas acima é só o caso de acertar essas linhas.

[]s

AleGrotto

25-08-2010 20:04:03

Olá Mauro,

O Problema não está acontecendo nas linhas que você apontou.

Acontece nas linhas abaixo, coloquei uma MsgBox para exibir a condição da ItemEstAt.Selected(i) e ela muda de verdadeiro para falso.

Sheets("Plan3").Activate
MsgBox ItemEstAt.Selected(i) 'Aqui aparece como verdadeiro
ActiveSheet.Range("$A$1:$F$1009").AutoFilter Field:=1, Criteria1:=ItemEstAt.List(i), _
Operator:=xlAnd
MsgBox ItemEstAt.Selected(i) 'Aqui aparece como falso
Columns("B:B").Copy Destination:=Sheets("Apoio").Range("G:G")

Existe outra maneira de fazer a filtragem?

A propósito, fiz as modificações que você sugeriu para eliminar linhas duplicadas. Muito obrigado! Ainda sou iniciante em VBA.


Grande abraço e obrigado novamente por me ajudar.

Mauro Coutinho

25-08-2010 23:07:22

Ale, agora caiu a ficha, o que acontece é que como estamos trabalhando com a opção Selected é associado True para os Itens Selecionados e False para os que não estão, então devemos colocar mais uma condição para se verificar essa condição, adicione a seguinte linha na rotina :

'Retira dados da Plan1 e cola na Apoio
Dim i As Integer
For i = 0 To ItemEstAt.ListCount - 1
If ItemEstAt.Selected(i) Then

If ItemEstAt.Selected(i) <> False Then
........
...............
e no final acrescente mai um End If
...
End If
End If
Next i


assim quando a condição for Falso vai direto para o final e retorna ao inicio da rotina sem executar o restante.

Desculpe pelo equivoco.
[]s

AleGrotto

26-08-2010 00:06:39

Mauro,

Testei a sua sugestão, mas não funcionou.
Quando a linha abaixo é executada na plan3

ActiveSheet.Range("$A$1:$F$1009").AutoFilter Field:=1, Criteria1:=ItemEstAt.List(i), Operator:=xlAnd,

Os itens que estavam selecionados na ListBox simplesmente deixam de estar selecionados, e assim, a varredura para logo no primeiro item.
O que preciso fazer é fixar a seleção dos itens da ListBox, de modo que eles não se percam durante o looping, mas não consigo.
Será que tem algum modo de fazer isso?

Novamente agradeço muito pela ajuda e paciência
Abraço
Ale

Mauro Coutinho

26-08-2010 01:39:38

Ale, refiz o teste e a parte dos Itens Selecionados no ListBox estão corretos, o problema está na definição dos Filtros, faça um teste com menos Dados nas planilhas e percorra sua rotina utilizando o recursso Passo a Passso teclando F8.

O que encontrei no teste que fiz usando algumas linhas com AutoFiltro, como eu disse anteriormente, não havia utilizado os filtros é o seguinte :
Vamos entender o que as mesmas estão fazendo, depois acerte em sua rotina e refaça os testes :
Sheets("Plan1").Activate
ActiveSheet.Range("$A$1:$F$1009").AutoFilter Field:=1
A Propriedade AutoFilter ja realiza a condição do Filtro montando o mesmo, substitua por :
ActiveSheet.Range("$A$1:$F$1009").AutoFilter
Não é necessário colocar o Fieldna primeira Linha, pois o mesmo já está definido na última Linha antes do criterio.
ActiveSheet.Range("$A$1:$F$1009").AutoFilter Field:=1
ActiveSheet.Range("$A$1:$F$1009").AutoFilter Field:=2
ActiveSheet.Range("$A$1:$F$1009").AutoFilter Field:=3
ActiveSheet.Range("$A$1:$F$1009").AutoFilter Field:=4
ActiveSheet.Range("$A$1:$F$1009").AutoFilter Field:=5
ActiveSheet.Range("$A$1:$F$1009").AutoFilter Field:=6

ActiveSheet.Range("$A$1:$F$1009").AutoFilter Field:=3, Criteria1:=ItemEstAt.List(i), _
Operator:=xlAnd


Agora que vem o "X" da questão, supondo que temos dois itens Selecionados, vamos seguir a Lógica, na Primeira passagem a opção Selected é VERDADEIRA a rotina segue e quando chega na instrução ActiveSheet.Range("$A$1:$F$1009").AutoFilter é montado o Filtro e efetuado o restante da Rotina sem problemas, então partimos para o SEGUNDO ITEM, a opção Selected é VERDADEIRA a rotina segue e quando chega novamente na linha que Monta o Filtro o mesmo já estáva Montado, então é necessário primeiro desmontar o Filtro e Monta-lo novamente para que seja respeitado o NOVO CRITERIO que seria o Item opção Selected 1 e rever os Fields que ao reiniciar vai estar sempre pelo Field:= 1 que é a primeira opção.

Procure seguir o que eu disse Passo a Passo utilizando o F8 que poderá visualizar o que coloquei acima.

[]s

AleGrotto

26-08-2010 14:36:16

Mauro,

Ok!
Limpei as linhas desnecessárias conforme sua sugestão.

Mas neste ponto, o problema permanece.
'Retira dados da Plan3
Sheets("Plan3").Activate
ActiveSheet.Range("$A$1:$A$1009").AutoFilter Field:=1, Criteria1:=ItemEstAt.List(i), _
Operator:=xlAnd
Columns("B:B").Copy Destination:=Sheets("Apoio").Range("G:G")
- Quando é executada esta linha, a selecão dos itens na ListBox continua sumindo. E automaticamente o ItemEstAt.Selected(i) vai para o estado de falso.
Na verdade ele nem começa a procurar o segundo item da seleção, portanto não acredito que o problema seja o AutoFilter.

Outra coisa, percebi que quando modifico para a rotina abaixo, a busca é feita com todos os itens da seleção que estão na Plan1, portanto, o AutoFilter não precisa ser desabilitado para o segundo item da seleção.

Dim i As Integer

Sheets("Apoio").Columns("A:J").ClearContents
For i = 0 To ItemEstAt.ListCount - 1
If ItemEstAt.Selected(i) Then
Sheets("Plan1").Activate
ActiveSheet.Range("$A$1:$F$1009").AutoFilter Field:=3, Criteria1:=ItemEstAt.List(i), _
Operator:=xlAnd
Columns("A:F").Copy Destination:=Sheets("Apoio").Range("A:A")
End If
Next i


Não sei se fui claro, mas muito obrigado pela paciência e ajuda

Abraço
Ale

Mauro Coutinho

26-08-2010 16:24:45

Ale, anexe a sua planilha sem as modificações que eu indiquei para poder analizar melhor, pois eu estou me baseando em um modelo de teste tentando forçar o erro que diz, e lógicamente não estamos nos encontrando por alguma informação que eu esteja deixando de lado pelo fato dos mesmos serem diferentes.

abraços

AleGrotto

26-08-2010 16:31:39

Mauro e Amigos,

Finalmente descobri o erro!

O itens que preenchiam a ListBox, estavam na Plan3.
Toda vez que ele fazia o AutoFiltro na Plan3, obviamente os itens sofriam alguma mudança e a seleção se desfazia.
Para resolver isso, criei uma outra planilha identica a Plan3 e retirei dali a seleção.

FUNCIONOU PERFEITAMENTE!!!!

Mauro,
Me desculpe, não tinha como você resolver sem conhecimento desta informação.

Mas realmente muitíssimo obrigado pela sua atenção, aprendi várias coisas com você.

Grande abraço e até a próxima.
Ale

Mauro Coutinho

26-08-2010 16:37:53

Ale, ainda bem que se chegou a o resultado pretendido, e bem no momento em que postava para anexar sua planilha, por isso que muitas vezes dependendo da rotina se faz necessário o arquivo, no exemplo que montei para teste eu não utilizava os dados da planilha, carregava direto alguns Itens no Listbox Add os Itens pela rotina.

[]s