none
Problema com "Filtro Avançado" utilizando VBA no Excel 2010 RRS feed

  • Pergunta

  • Olá!

    Recentemente migrei do Office 2003 para o Office 2007 (empresa) e Office 2010 (em casa). Ocorre que simples códigos em Visual Basic simplesmente não funcionam em nenhuma dessas duas últimas versões do Office!

    Exemplo: Preciso Filtrar dados em uma tabela que contém datas, horas e valores decimais utilizando "Filtro Avançado". Quando executo manualmente pela Barra de Ferramentas funciona ok, mas ao executar pelo VB simplesmente não funciona. O problema somente aparece quando algum dos parâmetros dos critérios possui escrita diferenciada em "inglês", como por exemplo a "data maior ou igual 31 de agosto de 2012": somente funciona por Macro se o critério for ">=08/31/2012" ou ">=31/Aug/2012". Já pela Barra de Ferramentas somente funciona se o critério for ">=31/08/2012".

    O mesmo ocorre para valores decimais: por macro funciona apenas se utilizar "." (mesmo que os dados da tabela contenham ",". Já pela barra de Ferramentas somente funciona se ambos forem ",".

    O código VBA (igual ao gerado pelo comando "Gravar Macro") é o seguinte:

    Range("Dados").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:= _
        Range("Criterios"), CopyToRange:=Range("Resultados"), Unique:=False

    Obrigado!

    quarta-feira, 15 de agosto de 2012 22:20

Respostas

  • Bom dia Benzadeus!

    Acho que finalmente consegui uma saída satisfatória. Não é exatamente o que eu queria, pois penso que isso é uma deficiência adquirida pelo Office, mas vai resolver boa parte dos problemas (apenas a entrada manual e a exibição formatada ficaram comprometidas). Aproveitei sua sugestão com algumas pequenas modificações, e coloquei a planilha em: http://www.sendspace.com/file/c91nce.

    Pra facilitar futuras consultas, segue o código descrito abaixo:

                                

    Sub Filtro_Dados()

    Application.ScreenUpdating = False          'Aceleração do Código!

    If Val(Application.Version) >= 12 Then 'A saída é uma string!
        Range("Criterios0").Select   'Interface Usuário
        Inicio = ActiveCell.Address(RowAbsolute:=False, ColumnAbsolute:=False)


        Range("CriteriosAux").FormulaLocal = "=SUBSTITUIR(" & Inicio & ";"","";""."")"    'Fórmula Básica (Cópia dos Critérios)


        For Each Rng In Range("CriteriosAux")

            If (Rng = 0 Or Rng = "") Then Rng.ClearContents

        Next Rng

        CriteriosMacro = "CriteriosAux"

    Else
        CriteriosMacro = "Criterios0"

    End If


    'Atualizando os Dados (Filtro Avançado):
    Range("Dados").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:= _
        Range(CriteriosMacro), CopyToRange:=Range("Result"), Unique:=False

    Application.ScreenUpdating = True

    End Sub

    Obs: "Criterios0" é onde o usuário digita os critérios do filtro e "CriteriosAux" é a conversão para as versões mais novas do Excel (>2003).

    Muito obrigado pela ajuda!

    Um grande abraço.


    Filipe Magno

    • Marcado como Resposta FilipeMagno quarta-feira, 22 de agosto de 2012 14:34
    • Marcado como Resposta FilipeMagno domingo, 16 de setembro de 2012 13:33
    terça-feira, 21 de agosto de 2012 14:23

Todas as Respostas

  • Olá, veja o link: http://www.ambienteoffice.com.br/excel/filtro_avancado/

    Felipe Costa Gualberto - http://www.ambienteoffice.com.br

    quinta-feira, 16 de agosto de 2012 22:22
  • Boa noite Benzadeus!

    Essa forma, utilizando a Barra de Ferramentas do Excel, como descrevi no meu problema, sempre funciona. Inclusive SEM utilizar a função "Data.Valor". O problema ocorre quando uso a macro descrita para realizar a tarefa, sendo que esse problema não acontece no Excel 2003!

    Além disso, geralmente utilizo referenciando a outra célula que contém a data. Exemplo: =">=" & A1, onde A1 tem o valor da data.

    Mesmo assim obrigado!


    Filipe Magno

    sexta-feira, 17 de agosto de 2012 01:10
  • Se você usar o DATA.VALOR, funcionará em todas as versões do Excel. Sugiro que você adapte o Range("Critérios") na forma como mostra no link que te enviei para evitar problemas.

    Felipe Costa Gualberto - http://www.ambienteoffice.com.br

    sexta-feira, 17 de agosto de 2012 21:35
  • Benzadeus,

    a sua dica funciona para esse caso. Inclusive funcionaria melhor se utilizasse da seguinte forma: [=">=" & TEXTO(A1;"Geral")], já que evita a necessidade de digitar a data como texto, porém isso só resolve parte do meu problema: pra facilitar o entendimento do meu problema, descrevi apenas a parte mais simples. O problema maior ocorre quando a data contém hora junto, tipo [15/08/2012  08:00]. Dessa forma, mesmo que utilize a fórmula acima, o resultado será [>=41136,33333] que resultará em erro por causa da [,].

    Inclusive, pra te exemplificar a dimensão do problema, seu eu quiser filtrar todos os valores [>5,5] em outro campo, por exemplo, SOMENTE irá funcionar com Macro se eu escrever [>5.5], independente de qualquer fórmula. Já pela barra de ferramentas funcionaria SOMENTE da primeira forma ([5,5]). Ou seja, são formas excludentes.

    Resumindo, o problema aparece quando algum dos parâmetros dos critérios possui escrita diferenciada em "inglês", sendo que no Excel 2003 nenhum dos problemas citados existe (existe uma "conversão interna automática")

    Vlw!


    Filipe Magno

    sexta-feira, 17 de agosto de 2012 22:42
  • Estou curioso para resolver esse problema. Teria como você disponibilizar uma pasta de trabalho (num site gratuito como SendSpace.com e postar o link aqui de volta) que dê esse erro para eu dar uma olhada?

    Felipe Costa Gualberto - http://www.ambienteoffice.com.br

    sábado, 18 de agosto de 2012 18:41
  • Boa noite Benzadeus!

    Coloquei uma pequena planilha, bem simples, no endereço que você sugeriu: http://www.sendspace.com/file/sfdrs5

    Note que os formatos exemplo que coloquei que "Não Funciona!" com Macro funcionam perfeitamente com a Barra de Ferramentas (Modo Manual). Já os que funcionam com Macro não funcionam com a Barra de Ferramentas!

    Relembrando: esse problema não existe no Office 2003!

    Não sei mais o que fazer, isso tá me dando muita dor de cabeça no trabalho!

    Desde já agradeço a ajuda!


    Filipe Magno

    domingo, 19 de agosto de 2012 00:17
  • Uma alternativa seria usar como critério:

    =">=" & SUBSTITUIR(F16;",";".")

    ou

    =">=" & SUBSTITUIR(D16+E16;",";".")

    ou

    =SUBSTITUIR(">"&DATA(2012;8;15)+TEMPO(1;2;3);",";".")

    Dessa forma atenderia?


    Felipe Costa Gualberto - http://www.ambienteoffice.com.br

    domingo, 19 de agosto de 2012 18:22
  • Boa noite Benzadeus.

    Eu já havia tentado usar a função "Substituir" anteriormente, só que diretamente no código da Macro. Nesse caso existiam 2 problemas:

    1) Essas planilhas são usadas por várias pessoas, e a maioria ainda usa o Excel 2003 (nesses casos a Macro deixa de funcionar); e

    2) A função não funcionaria caso o valor da célula fosse uma "string" com o formato "[>=15/08/12 08:00]", por exemplo (no caso de uma entrada manual diretamente na célula ou no caso de usar uma fórmula para exibição formatada, como está implementado nas minhas planilhas: [=">=" & TEXTO(A1;"dd/mm/aa hh:mm")]).

    Mas revendo essa possibilidade, pensei na seguinte saída (resolve parcialmente/suficientemente os problemas):

    1) Usaria uma célula de validação, que verificaria a versão do Excel atual e indicaria a necessidade ou não usar as fórmulas ([=INFORMAÇÃO("SOLTAR")<12] ou [=INFORMAÇÃO("release")<12];

    2) Criar um campo "Critérios" auxiliar que seria o utilizado pela Macro (ficaria em linhas ocultas e conterá as fórmulas adicionais (Substituir), caso a situação acima seja "FALSO");

    3) Não utilizar mais a exibição formatada nas células com os critérios, para permitir que a função "Substituir" funcione.

    Amanhã, se der tempo, vou fazer esse teste na empresa, aproveitando as diferentes versões do Excel. Acho que irá funcionar, apesar do inconveniente de não poder exibir o campo formatado, como já venho utilizando em todos as planilhas que criei.

    Valeu pela dica!

    Um abraço.


    Filipe Magno

    segunda-feira, 20 de agosto de 2012 02:02
  • Se o filtro avançado é aplicado somente através de macros, você poderia usar uma rotina como a mostrada abaixo:

    Sub Filtro_Dados()
        Dim rng As Range
        
        If Application.Version >= 12 Then
            For Each rng In Range("Criterios")
                rng = Replace(rng, ",", ".")
            Next rng
        End If
        
        Range("Dados").AdvancedFilter _
          Action:=xlFilterCopy, _
          CriteriaRange:=Range("Criterios"), _
          CopyToRange:=Range("Result"), _
          Unique:=False
    End Sub

    Mas fique atento que qualquer vínculo através de fórmulas de células dentro do intervalo Criterios é perdida. De duas, uma: salve os valores originais dos critérios num outro lugar e volte com eles depois do filtro ser aplicado ou crie um intervalo chamado Criterios2 que será o espelho de Criterios, mas com valores convertidos para sua versão correta.


    Felipe Costa Gualberto - http://www.ambienteoffice.com.br

    segunda-feira, 20 de agosto de 2012 02:43
  • Boa noite Benzadeus!

    Esse problema já tá virando uma novela...

    A solução proposta por vc somente funciona se apenas os campos de interesse forem preenchidos, todos os demais devem ser deixados vazios (caso contrário será escrito o valor "0"), além do grande incoveniente de se perder qualquer fórmula digitada.

    Digo isso pq os meus critérios possuem sempre mais de uma linha, de forma que se possa procurar por critérios "Ou". Nesse caso, por "Default", na segunda linha utilizo a fórmula "=Linha acima" (dessa forma, caso não se deseje utilizá-la, esta não interfere nos critérios). Ao utilizar sua proposta (à exceção da versão do Excel, eu já havia testado antes, como mencionei acima) a segunda linha é preenchida com "0", impedindo o funcionamento do filtro.

    Estou tentando algumas opções com a função substituir diretamente na planilha, mas ainda não está funcionando (a referência a células vazias não está sendo corretamente interpretada). Vou fazer mais alguns testes, e se der certo posto mais detalhes e, quem sabe, o resultado depois.

    Valeu!


    Filipe Magno

    segunda-feira, 20 de agosto de 2012 22:41
  • "A solução proposta por vc somente funciona se apenas os campos de interesse forem preenchidos, todos os demais devem ser deixados vazios (caso contrário será escrito o valor "0")"
    Use uma teste condicional no laço que percorre todas as células dos critérios para utilizar o método ClearContents nas células cujo valor for 0. Dessa forma, os valores 0 serão, apagados e o filtro funcionará.

    "grande incoveniente de se perder qualquer fórmula digitada"
    Como eu disse, basta você salvar os valores com fórmulas num local da planilha antes de alterar os valores para depois restabelece-los ou então fazer as operações e o filtro avançado num intervalo diferente do original.


    Felipe Costa Gualberto - http://www.ambienteoffice.com.br

    segunda-feira, 20 de agosto de 2012 23:56
  • Bom dia Benzadeus!

    Acho que finalmente consegui uma saída satisfatória. Não é exatamente o que eu queria, pois penso que isso é uma deficiência adquirida pelo Office, mas vai resolver boa parte dos problemas (apenas a entrada manual e a exibição formatada ficaram comprometidas). Aproveitei sua sugestão com algumas pequenas modificações, e coloquei a planilha em: http://www.sendspace.com/file/c91nce.

    Pra facilitar futuras consultas, segue o código descrito abaixo:

                                

    Sub Filtro_Dados()

    Application.ScreenUpdating = False          'Aceleração do Código!

    If Val(Application.Version) >= 12 Then 'A saída é uma string!
        Range("Criterios0").Select   'Interface Usuário
        Inicio = ActiveCell.Address(RowAbsolute:=False, ColumnAbsolute:=False)


        Range("CriteriosAux").FormulaLocal = "=SUBSTITUIR(" & Inicio & ";"","";""."")"    'Fórmula Básica (Cópia dos Critérios)


        For Each Rng In Range("CriteriosAux")

            If (Rng = 0 Or Rng = "") Then Rng.ClearContents

        Next Rng

        CriteriosMacro = "CriteriosAux"

    Else
        CriteriosMacro = "Criterios0"

    End If


    'Atualizando os Dados (Filtro Avançado):
    Range("Dados").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:= _
        Range(CriteriosMacro), CopyToRange:=Range("Result"), Unique:=False

    Application.ScreenUpdating = True

    End Sub

    Obs: "Criterios0" é onde o usuário digita os critérios do filtro e "CriteriosAux" é a conversão para as versões mais novas do Excel (>2003).

    Muito obrigado pela ajuda!

    Um grande abraço.


    Filipe Magno

    • Marcado como Resposta FilipeMagno quarta-feira, 22 de agosto de 2012 14:34
    • Marcado como Resposta FilipeMagno domingo, 16 de setembro de 2012 13:33
    terça-feira, 21 de agosto de 2012 14:23
  • Estava com o mesmo problema, e resolvi da seguinte forma:

    Imagine uma tabela de valores, onde na coluna C, tivesse valores variando entre 0,98 e 0,988

    Sub Filtrar ()

    Dim V1_min, V1_max as String 'Coloquei a variável como String para esta ser lida como texto na célula

        Range("A1:E1").Select
        Selection.AutoFilter

        'Dei valores as variáveis

        V1_min = Plan1.Range("F2").Value 'No meu caso está célula era = 0.98
        V1_max = Plan1.Range("F3").Value 'No meu caso está célula era = 0.988

        'Aqui apliquei o filtro utilizando as variáveis como critérios

        ActiveSheet.Range("$A$1:$E$1").AutoFilter Field:=3, Criteria1:=">=" & V1_min & "", _
        Operator:=xlAnd, Criteria2:="<=" & V1_max & ""

    End Sub

    Fiz desta forma para que não precisa entrar no VB toda vez para mudar o valor dos critérios, agora se vc deseja manter fixo o valor dos critérios então poderia ficar assim:

    Sub Filtrar

        Range("A1:E1").Select
        Selection.AutoFilter

        'Dei valores as variáveis

        'Aqui apliquei o filtro utilizando as variáveis como critérios (o vb reconhece o decimal)

        ActiveSheet.Range("$A$1:$E$1").AutoFilter Field:=3, Criteria1:=">=0.98", _
        Operator:=xlAnd, Criteria2:="<="0.988"

    End Sub

    Neste caso você deve inserir o valor no formato americano, ou seja com "." ao invés da vírgola.

    Espero ajudar

    Abraços


    • Editado André Gatti segunda-feira, 10 de dezembro de 2012 18:41
    segunda-feira, 10 de dezembro de 2012 17:57
  • Boa tarde André.

    Obrigado por contribuir com o Fórum. Mas no meu caso o problema era com 'Filtro Avançado' e não com 'Auto Filtro'.

    Com o 'Filtro Avançado' existe a possibilidade de delimitar uma área da planilha com todos os critérios desejados, inclusive condições 'Ou' e "&".

    O problema que eu tinha era a respeito de separadores decimais incompatíveis entre o Excel e o VBA. Mas como pode ver na resposta marcada acima, resolvi o problema utilizado uma pequena área de conversão dos valores (CriteriosAux).

    Vlw.


    Filipe Magno

    segunda-feira, 10 de dezembro de 2012 19:11