Usando Localizar e Substituir no Excel VBA
In this Article
Este tutorial demonstrará como usar os métodos Localizar e Substituir no Excel VBA.
VBA Localizar
O Excel tem excelentes ferramentas internas de Localizar e Localizar & Substituir.
Elas podem ser ativadas com os atalhos CTRL + L (Localizar) ou CTRL + U (Substituir) ou por meio da Faixa de Opções: Página Inicial > Edição > Localizar e Selecionar.
Ao clicar em Opções, você pode ver as opções avançadas de pesquisa:
Você pode acessar facilmente esses métodos usando VBA.
Exemplo de Localizar VBA
Para demonstrar a funcionalidade Find, criamos o seguinte conjunto de dados na Planilha1.
Se quiser acompanhar o exemplo, insira os dados em sua própria pasta de trabalho.
VBA Find Sem Parâmetros Opcionais
Ao usar o método Find do VBA, há muitos parâmetros opcionais que você pode definir.
É altamente recomendável definir todos os parâmetros sempre que usar o método Find!
Se você não definir os parâmetros opcionais, o VBA usará os parâmetros atualmente selecionados na janela Localizar do Excel. Isso significa que você pode não saber quais parâmetros de pesquisa estão sendo usados quando o código é executado. O Find pode ser executado em toda a pasta de trabalho ou em uma planilha. Ele poderia pesquisar fórmulas ou valores. Não há como saber, a menos que você verifique manualmente o que está selecionado no momento na janela Localizar do Excel.
Para simplificar, começaremos com um exemplo sem parâmetros opcionais definidos.
Exemplo de Find Simples
Vamos dar uma olhada em um exemplo simples de Find:
Sub TestarFind()
Dim MeuIntervalo As Range
Set MeuIntervalo = Sheets("Planilha1").UsedRange.Find("empregado")
MsgBox MeuIntervalo.Address
MsgBox MeuIntervalo.Column
MsgBox MeuIntervalo.Row
End Sub
Esse código procura por “empregado” no UsedRange da Planilha1. Se encontrar “empregado”, ele atribuirá o primeiro intervalo encontrado à variável range MeuIntervalo.
Em seguida, as caixas de mensagem serão exibidas com o endereço, a coluna e a linha do texto encontrado.
Neste exemplo, são usadas as configurações padrão do Find (supondo que não tenham sido alteradas na janela Localizar do Excel):
- O texto da pesquisa tem correspondência parcial com o valor da célula (não é necessária uma correspondência exata da célula)
- A pesquisa não diferencia maiúsculas de minúsculas.
- A pesquisa só procura em uma única planilha
Essas configurações podem ser alteradas com vários parâmetros opcionais (discutidos abaixo).
Notas Sobre o Método Find
- O Find não seleciona a célula em que o texto foi encontrado. Ele apenas identifica o intervalo encontrado, que pode ser manipulado em seu código.
- O método Find só localizará a primeira instância encontrada.
- Você pode usar curingas (*), por exemplo, procurar por ‘E*’
Nada Encontrado
Se o texto da pesquisa não existir, o objeto de intervalo permanecerá vazio. Isso causa um grande problema quando seu código tenta exibir os valores de localização porque eles não existem. Isso resultará em uma mensagem de erro que você não deseja.
Felizmente, você pode testar se há um objeto de intervalo vazio no VBA usando o operador Is:
If Not MeuIntervalo Is Nothing ThenAdicione o código ao nosso exemplo anterior:
Sub TestarFind()
Dim MeuIntervalo As Range
Set MeuIntervalo = Sheets("Planilha1").UsedRange.Find("empregado")
If Not MeuIntervalo Is Nothing Then
MsgBox MeuIntervalo.Address
MsgBox MeuIntervalo.Column
MsgBox MeuIntervalo.Row
Else
MsgBox "Não Encontrado"
End If
End Sub Localizar Parâmetros
Até agora, vimos apenas um exemplo básico de uso do método Find. No entanto, há vários parâmetros opcionais disponíveis para ajudá-lo a refinar sua pesquisa
| Parâmetro | Tipo de parâmetro | Descrição | Valores |
| What | Necessário | O valor a ser pesquisado | Qualquer tipo de dados, como string ou numérico |
| After | Opcional | Referência de célula única para iniciar sua pesquisa | Endereço da célula |
| LookIn | Opcional | Usar fórmulas, valores e comentários para a pesquisa | xlValues, xlFormulas, xlComments |
| LookAt | Opcional | Corresponde a parte ou ao todo de uma célula | xlWhole, xlPart |
| SearchOrder | Opcional | A ordem de pesquisa – linhas ou colunas | xlByRows, xlByColumns |
| SearchDirection | Opcional | Direção da pesquisa – para frente ou para trás | xlNext, xlPrevious |
| MatchCase | Opcional | A pesquisa diferencia maiúsculas de minúsculas ou não | True ou False |
| MatchByte | Opcional | Usado somente se você tiver instalado o suporte ao idioma de byte duplo, por exemplo, o idioma chinês | True ou False |
| SearchFormat | Opcional | Permite pesquisar por formato de célula | True ou False |
O Parâmetro After e Localizar Vários Valores
O parâmetro After é usado para especificar a célula inicial da sua pesquisa. Isso é útil quando há mais de uma instância do valor que você está pesquisando.
Se uma pesquisa já tiver encontrado um valor e você souber que haverá mais valores encontrados, use o método Find com o parâmetro “After” para registrar a primeira instância e, em seguida, use essa célula como ponto de partida para a próxima pesquisa.
Você pode usar isso para encontrar várias instâncias do texto pesquisado:
Sub TesteMultiplosCampos()
Dim MyRange As Range, OldRange As Range, FindStr As String
'Procurar a primeira instância de "Luz e Aquecimento"
Set MyRange = Sheets("Planilha1").UsedRange.Find("Luz e Aquecimento")
'Se não for encontrado, sair
If MyRange Is Nothing Then Exit Sub
'Exibir o primeiro endereço encontrado
MsgBox MyRange.Address
'Fazer uma cópia do objeto range
Set OldRange = MyRange
'Adicione o endereço à string delimitando-o com um caractere "|"
FindStr = FindStr & "|" & MyRange.Address
'Iterar pelo intervalo procurando outras instâncias
Do
'Pesquise por "Luz e Aquecimento" usando o endereço encontrado anteriormente como o parâmetro After
Set MyRange = Sheets("Planilha1").UsedRange.Find("Luz e Aquecimento", After:=Range(OldRange.Address))
'Se o endereço já tiver sido encontrado, saia do loop do - isso interrompe o looping contínuo
If InStr(FindStr, MyRange.Address) Then Exit Do
'Exibir o último endereço encontrado
MsgBox MyRange.Address
'Adicionar o endereço mais recente ao string de endereços
FindStr = FindStr & "|" & MyRange.Address
'Fazer uma cópia do range atual
Set OldRange = MyRange
Loop
End Sub
Esse código iterará pelo intervalo usado e exibirá o endereço sempre que encontrar uma instância de “Luz e Aquecimento”.
Observe que o código continuará fazendo um loop até que um endereço duplicado seja encontrado em FindStr e, nesse caso, ele sairá do loop Do.
Parâmetro LookIn
Você pode usar o parâmetro LookIn para especificar em qual componente da célula deseja fazer a pesquisa. Você pode especificar valores, fórmulas ou comentários em uma célula.
- xlValues – Pesquisa os valores da célula (o valor final de uma célula após o cálculo)
- xlFormulas – Pesquisa dentro da própria fórmula da célula (o que quer que tenha sido inserido na célula)
- xlComments – Pesquisa dentro das notas da célula
- xlCommentsThreaded – Pesquisa nos comentários da célula
Supondo que uma fórmula tenha sido inserida na planilha, você poderia usar esse código de exemplo para encontrar o primeiro local de qualquer fórmula:
Sub TestarLookIn()
Dim MyRange As Range
Set MyRange = Sheets("Planilha1").UsedRange.Find("=", LookIn:=xlFormulas)
If Not MyRange Is Nothing Then
MsgBox MyRange.Address
Else
MsgBox "Não Encontrado"
End If
End Sub
Se o parâmetro “LookIn” fosse definido como xlValues, o código exibiria uma mensagem “Não encontrado”. Neste exemplo, ele retornará B10.
Uso do Parâmetro LookAt
O parâmetro LookAt determina se o Find pesquisará uma correspondência exata de célula ou se pesquisará qualquer célula que contenha o valor da pesquisa.
- xlWhole – Requer que a célula inteira corresponda ao valor da pesquisa
- xlPart – Procura em uma célula a string de pesquisa
Este exemplo de código localizará a primeira célula que contém o texto “luz”. Com Lookat:=xlPart, ele retornará uma correspondência para “Luz e Aquecimento”.
Sub TestarLookAt()
Dim MyRange As Range
Set MyRange = Sheets("Planilha1").UsedRange.Find("luz", Lookat:=xlPart)
If Not MyRange Is Nothing Then
MsgBox MyRange.Address
Else
MsgBox "Não Encontrado"
End If
End Sub
Se xlWhole fosse definido, uma correspondência só retornaria se o valor da célula fosse “luz”.
Parâmetro SearchOrder
O parâmetro SearchOrder determina como a pesquisa será realizada em todo o intervalo.
- xlRows – A pesquisa é feita linha por linha
- xlColumns – A pesquisa é feita coluna por coluna
Sub TestarSearchOrder()
Dim MyRange As Range
Set MyRange = Sheets("Planilha1").UsedRange.Find("empregado", SearchOrder:=xlColumns)
If Not MyRange Is Nothing Then
MsgBox MyRange.Address
Else
MsgBox "Não Encontrado"
End If
End Sub
Isso influencia qual correspondência será encontrada primeiro.
Usando os dados de teste inseridos na planilha anteriormente, quando a ordem de pesquisa é colunas, a célula localizada é A5. Quando o parâmetro de ordem de pesquisa é alterado para xlRows, a célula localizada é C4
Isso é importante se você tiver valores duplicados no intervalo de pesquisa e quiser encontrar a primeira instância em um determinado nome de coluna.
Parâmetro SearchDirection
O parâmetro SearchDirection determina a direção em que a pesquisa será feita – efetivamente para frente ou para trás.
- xlNext – Procura o próximo valor correspondente no intervalo
- xlPrevious – Busca o valor correspondente anterior no intervalo
Novamente, se houver valores duplicados dentro do intervalo de pesquisa, isso pode afetar qual deles será encontrado primeiro.
Sub TestarSearchDirection()
Dim MyRange As Range
Set MyRange = Sheets("Planilha1").UsedRange.Find("aquecimento", SearchDirection:=xlPrevious)
If Not MyRange Is Nothing Then
MsgBox MyRange.Address
Else
MsgBox "Não Encontrado"
End If
End Sub
Usando esse código nos dados de teste, uma direção de pesquisa de xlPrevious retornará um local de C9. O uso do parâmetro xlNext retornará um local de A4.
O parâmetro Next significa que a pesquisa começará no canto superior esquerdo do intervalo de pesquisa e trabalhará para baixo. O parâmetro Previous significa que a pesquisa começará no canto inferior direito do intervalo de pesquisa e trabalhará para cima.
Parâmetro MatchByte
O parâmetro MatchByte é usado somente para idiomas que usam um byte duplo para representar cada caractere, como chinês, russo e japonês.
Se esse parâmetro for definido como “True”, o Find só fará a correspondência de caracteres de byte duplo com caracteres de byte duplo. Se o parâmetro for definido como ‘False’, um caractere de byte duplo corresponderá a caracteres de byte simples ou duplo.
Parâmetro SearchFormat
O parâmetro SearchFormat permite que você pesquise formatos de células correspondentes. Pode ser uma fonte específica que está sendo usada, uma fonte em negrito ou uma cor de texto. Antes de usar esse parâmetro, você deve definir o formato necessário para a pesquisa usando a propriedade Application.FindFormat.
Aqui está um exemplo de como usá-lo:
Sub TestarSearchFormat()
Dim MyRange As Range
Application.FindFormat.Clear
Application.FindFormat.Font.Bold = True
Set MyRange = Sheets("Planilha1").UsedRange.Find("aquecimento", Searchformat:=True)
If Not MyRange Is Nothing Then
MsgBox MyRange.Address
Else
MsgBox "Não Encontrado"
End If
Application.FindFormat.Clear
End Sub
Neste exemplo, a propriedade FindFormat é definida para procurar uma fonte em negrito. Em seguida, a instrução Find procura a palavra “aquecimento”, definindo o parâmetro SearchFormat como True, de modo que só retornará uma instância desse texto se a fonte estiver em negrito.
Nos dados da planilha de amostra mostrados anteriormente, isso retornará A9, que é a única célula que contém a palavra “aquecimento” em uma fonte em negrito.
Certifique-se de que a propriedade FindFormat seja limpa no final do código. Caso contrário, sua próxima pesquisa ainda levará isso em conta e retornará resultados incorretos.
Quando você usa um parâmetro SearchFormat, também pode usar um curinga (*) como valor de pesquisa. Nesse caso, ele pesquisará qualquer valor com uma fonte em negrito:
Set MyRange = Sheets("Planilha1").UsedRange.Find("*", Searchformat:=True)Uso de Vários Parâmetros
Todos os parâmetros de pesquisa discutidos aqui podem ser usados em combinação uns com os outros, se necessário.
Por exemplo, você pode combinar o parâmetro “LookIn” com o parâmetro “MatchCase” para que você examine todo o texto da célula, mas com distinção entre maiúsculas e minúsculas
Sub TestarMultiplosParametros()
Dim MyRange As Range
Set MyRange = Sheets("Planilha1").UsedRange.Find("Luz e Aquecimento", LookAt:=xlWhole, MatchCase:=True)
If Not MyRange Is Nothing Then
MsgBox MyRange.Address
Else
MsgBox "Não Encontrado"
End If
End Sub
Neste exemplo, o código retornará A4, mas se usássemos apenas uma parte do texto, por exemplo, aquecimento, nada seria encontrado porque estamos fazendo a correspondência com todo o valor da célula. Além disso, haveria falha devido ao fato de não haver correspondência entre maiúsculas e minúsculas.
Set MyRange = Sheets("Planilha1").UsedRange.Find("aquecimento", LookAt:=xlWhole, MatchCase:=True)Substituir no Excel VBA
Como era de se esperar, há uma função Substituir no Excel VBA, que funciona de maneira muito semelhante à função “Localizar”, mas substitui os valores no local da célula encontrada por um novo valor.
Esses são os parâmetros que você pode usar em uma instrução de método Replace. Eles funcionam exatamente da mesma forma que a instrução do método Find. A única diferença em relação ao ‘Find’ é que você precisa especificar um parâmetro Replacement.
| Nome do parâmetro | Tipo de parâmetro | Descrição | Valores |
| What | Necessário | O valor a ser pesquisado | Qualquer tipo de dados, como string ou numérico |
| Replacement | Necessário | A sequência de caracteres de substituição. | Qualquer tipo de dados, como string ou numérico |
| LookAt | Opcional | Corresponde a parte ou ao todo de uma célula | xlPart ou xlWhole |
| SearchOrder | Opcional | A ordem de pesquisa – Linhas ou Colunas | xlByRows ou xlByColumns |
| MatchCase | Opcional | A pesquisa diferencia maiúsculas de minúsculas ou não | True ou False |
| MatchByte | Opcional | Usado somente se você tiver instalado o suporte a idiomas de byte duplo | True ou False |
| SearchFormat | Opcional | Permite pesquisar por formato de célula | True ou False |
| ReplaceFormat | Opcional | O formato de substituição para o método. | True ou False |
O parâmetro Replace Format procura uma célula com um formato específico, por exemplo, negrito, da mesma forma que o parâmetro SearchFormat opera no método Find. Primeiro, você precisa definir a propriedade Application.FindFormat, conforme utilizado no código de exemplo Find mostrado anteriormente
Substituir sem Parâmetros Opcionais
Em sua forma mais simples, você só precisa especificar o que está procurando e pelo que deseja substituí-lo.
Sub TestarReplace()
Sheets("Planilha1").UsedRange.Replace What:="Luz e Aquecimento", Replacement:="L & A"
End Sub
Observe que o método Find retornará apenas a primeira instância do valor correspondente, enquanto o método Replace trabalha em todo o intervalo especificado e substitui tudo o que encontrar uma correspondência.
O código de substituição mostrado aqui substituirá todas as instâncias de “Luz e Aquecimento” por “L & A” em todo o intervalo de células definido pelo objeto UsedRange
Uso do VBA Para Localizar ou Substituir Texto em uma Sequência de Texto do VBA
Os exemplos acima funcionam muito bem quando se usa o VBA para interagir com os dados do Excel. No entanto, para interagir com sequências de caracteres VBA, você pode usar funções VBA incorporadas, como INSTR e REPLACE.
Você pode usar a função Função INSTR para localizar uma sequência de texto em uma sequência mais longa.
Sub TestarInstr()
MsgBox InStr("Este é o string MeuTexto", "MeuTexto")
End Sub
Esse código de exemplo retornará o valor 17, que é a posição numérica em que “MeuTexto” é encontrado na sequência de texto a ser pesquisada.
Observe que isso diferencia maiúsculas de minúsculas. Se “MeuTexto” estiver em minúsculas, será retornado o valor 0, o que significa que a sequência de texto pesquisada não foi encontrada. A seguir, discutiremos como desativar a diferenciação de maiúsculas e minúsculas.
INSTR – Início
Há mais dois parâmetros opcionais disponíveis. Você pode especificar o ponto de início da pesquisa:
MsgBox InStr(17, "Este é o string MeuTexto", "MeuTexto")O ponto inicial é especificado como 9, portanto, ainda retornará 17. Se o ponto inicial fosse 18, o resultado seria 0 (sem correspondência), pois o ponto inicial estaria muito à frente.
INSTR – Sensibilidade a Maiúsculas e Minúsculas
Você também pode definir um parâmetro Compare como vbBinaryCompare ou vbTextCompare. Se você definir esse parâmetro, a instrução deverá ter um valor de parâmetro inicial.
- vbBinaryCompare – Sensível a maiúsculas e minúsculas (Padrão)
- vbTextCompare – Não diferencia maiúsculas de minúsculas
MsgBox InStr(1, "Este é o string MeuTexto", "meutexto", vbTextCompare)Essa instrução ainda retornará 17, mesmo que o texto da pesquisa esteja em minúsculas.
Para desativar a sensibilidade a maiúsculas e minúsculas, você também pode declarar Option Compare Text na parte superior do seu módulo de código.
Função Replace do VBA
Se quiser substituir caracteres em uma sequência de caracteres por um texto diferente em seu código, o método Replace é ideal para isso:
Sub TestarReplace()
MsgBox Replace("Este é o string MeuTexto", "MeuTexto", "Meu Texto")
End Sub
Este código substitui “MeuTexto” por “Meu Texto”. Observe que a sequência de caracteres de pesquisa diferencia maiúsculas de minúsculas, pois uma comparação binária é o padrão.
Você também pode adicionar outros parâmetros opcionais:
- Start – define a posição na sequência de caracteres inicial a partir da qual a substituição deve começar. Diferentemente do método Find, ele retorna uma sequência de caracteres truncada a partir do número de caracteres definido pelo parâmetro Start.
- Count – define o número de substituições a serem feitas. Por padrão, o Replace alterará cada instância do texto de pesquisa encontrado, mas você pode limitar isso a uma única substituição definindo o parâmetro Count como 1
- Compare – como no método Find, você pode especificar uma pesquisa binária ou uma pesquisa de texto usando vbBinaryCompare ou vbTextCompare. Binário diferencia maiúsculas de minúsculas e texto não diferencia maiúsculas de minúsculas
MsgBox Replace("Este é o string MeuTexto (meutexto)", "MeuTexto", "Meu Texto", 17, 1, vbTextCompare)Esse código retorna “Meu Texto (meutexto)”. Isso ocorre porque o ponto inicial fornecido é 17, portanto, a nova string retornada começa no caractere 17. Somente o primeiro ‘MeuTexto’ foi alterado porque o parâmetro Count está definido como 1.
O método Replace é ideal para resolver problemas como nomes de pessoas que contêm apóstrofos, por exemplo, O’Flynn. Se você estiver usando aspas simples para definir um valor de sequência de caracteres e houver um apóstrofo, isso causará um erro porque o código interpretará o apóstrofo como o fim da sequência de caracteres e não reconhecerá o restante da sequência.
Você pode usar o método Replace para substituir o apóstrofo por nada, removendo-o completamente.
