Função SubTotal com Condição VBA

Função para Validar Inscrição Estadual e Formulário (VBA)
6 de fevereiro de 2011
Formatar CEP CPF, CNPJ, Ano, Mês, Dia no Excel
Formatar CEP CPF, CNPJ, Ano, Mês, Dia no Excel
10 de fevereiro de 2011

Objetivo: Utilizar a função subtotal com uma condição de igualdade. Exemplo: Quantidade de veículos Gol em um intervalo de dados com filtro, ou a soma dos valores dos veículos GOL filtrados no intervalo.

A função subtotal tem por objetivo trabalhar com várias funções em dados filtrados, mas ás vezes é necessário que estas informações sejam filtradas  mesmo na exibição dos dados, como é o caso da figura acima, aonde estão sendo contados apenas os veículos GOL e somado apenas o valor destes mesmos veículos.

A função VBA abaixo realiza o retorno de um intervalo de dados que atende ao critério de filtro, sendo desta forma utilizado no lugar da seleção dos dados na função SubTotal.

'fSubTotalCondicao
'Esta função traz um range de dados conforme a condição determinada
'lRange = intervalo de busca de dados
'lRngValor = intervalo de valores para funções diferentes de contagem
Function fSubTotalCondicao(ByVal lRange As Range, ByVal vValor As Variant, Optional ByVal lRngValor As Range) As Range

    Application.Volatile

    Dim lRangeSelect    As Range
    Dim lRng            As Range
    Dim lCel            As Range
    Dim lSelect         As Range
    Dim lCol            As Long

    Set lRangeSelect = lRange.SpecialCells(xlCellTypeVisible)

    If lRngValor Is Nothing Then
        For Each lCel In lRangeSelect
            If lCel.Value = vValor Then
                If fSubTotalCondicao Is Nothing Then
                    Set fSubTotalCondicao = Range(CStr(lCel.Address))
                Else
                    Set fSubTotalCondicao = Union(Range(CStr(lCel.Address)), Range(CStr(fSubTotalCondicao.Address)))
                End If
            End If
        Next lCel
    Else
        lCol = lRngValor.Column
        For Each lCel In lRangeSelect
            If lCel.Value = vValor Then
                If fSubTotalCondicao Is Nothing Then
                    Set fSubTotalCondicao = Range(CStr(Cells(lCel.Row, lCol).Address))
                Else
                    Set fSubTotalCondicao = Union(Range(CStr(Cells(lCel.Row, lCol).Address)), Range(CStr(fSubTotalCondicao.Address)))
                End If
            End If
        Next lCel
    End If
End Function

A utilização da função é a seguinte:

fSubTotalCondicao(ByVal lRange As Range, ByVal vValor As Variant, Optional ByVal lRngValor As Range) As Range

  • lRange = Intervalo aonde será realizado o filtro
  • vValor = Valor que será filtrado
  • lRngValor = Intervalo aonde serão aplicadas opções diferentes de contagem, tal como soma por exemplo. Este parâmetro é opcional.

Para implementá-la você deve seguir o artigo: http://guiadoexcel.com.br/criando-funcoes-proprias-globais.

Abaixo o download do exemplo:

Marcos Rieper

6 Comments

  1. Willian disse:

    Boa matéria, Marcos! Irá ajudar a melhorar a apresentação dos meus trabalhos

  2. Pedro disse:

    Caros amigos.

    Preciso de ajuda. preciso criar uma função no excel para contar e outra para somar células com o fundo colorido, porém, as fórmulas devem contemplar a opção de subtotal, ou seja, quando eu filtrar qualquer item de uma determinada coluna a fórmula deverá recalcular, ora com soma, ora com contar.

    Alguém consegue me ajudar, por favor? Obrigado

  3. Bom dia, Prezados

    Parabéns pelo site, o post e excelente a função e muito funcional, tenho uma duvida ou encontrei um erro.
    Ela não pode ser utilizada em planilhas com mas de 190 linha, ocasionando em erro na função.
    Poderiam me auxiliar?

    Muito Obrigado

    Felipe

    • Marcos Rieper disse:

      Boa noite Felipe,

      Tem toda razão, é um problema da função Union do VBA, na verdade um limite dele.

      Pensaremos em uma nova forma de realizar a mesma funcionalidade em um próximo artigo.

      Abraço

      Marcos Rieper

      • Felipe Alavarse disse:

        Boa noite, Marcos
        Obrigado pela análise, caso tenha uma solução ou talvez outra maneira de realizar o subtotal condicionado, peço a gentileza de nos informar.

  4. Luis disse:

    Como fazer a mesma coisa para a função: CONT.SE?

Deixe uma resposta

O seu endereço de e-mail não será publicado. Campos obrigatórios são marcados com *

Esse site utiliza o Akismet para reduzir spam. Aprenda como seus dados de comentários são processados.

Inscreva-se no nosso canal do Youtube!


Junte-se ao nosso canal do Youtube. Começamos em abril de 2016, mas já temos mais de 06:00 h de treinamentos gratuitos e este número irá aumentar. Vídeos novos todos os sábados.