Este tutorial demonstrará como usar os métodos Localizar e Substituir no Excel VBA.
VBA Find
Excel tem excelente embutido Achar e Encontrar e substituir Ferramentas.
Eles podem ser ativados com os atalhos CTRL + F (Encontrar) ou CTRL + H (Substitua) ou através da fita: Home> Editando> Find & Select.
Clicando Opções, você pode ver as opções de pesquisa avançada:
Você pode acessar facilmente os métodos Find e Replace usando o VBA. Esses métodos integrados são muito mais rápidos do que qualquer coisa que você possa escrever em VBA.
Encontre um exemplo de VBA
Para demonstrar a funcionalidade Find, criamos o seguinte conjunto de dados na Planilha1.
Se desejar acompanhar, insira os dados em sua própria pasta de trabalho.
<<<<<<<<<<<<<<<<<<<>>>>>>>>>>>>>>>>>>>
VBA Find sem parâmetros opcionais
Ao usar o método VBA Find, há muitos parâmetros opcionais que você pode definir.
Recomendamos fortemente 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 selecionados atualmente 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. Encontrar pode ser executado em toda a pasta de trabalho ou em uma planilha. Ele pode pesquisar fórmulas ou valores. Não há como saber, a menos que você verifique manualmente o que está selecionado na janela Localizar do Excel.
Para simplificar, começaremos com um exemplo sem parâmetros opcionais definidos.
Exemplo de descoberta simples
Vejamos um exemplo simples do Find:
| 123456789 | Sub TestFind ()Dim MyRange As RangeDefinir MyRange = Sheets ("Sheet1"). UsedRange.Find ("funcionário")MsgBox MyRange.AddressMsgBox MyRange.ColumnMsgBox MyRange.RowEnd Sub |
Este código procura por “funcionário” no intervalo usado da planilha1. Se encontrar “funcionário”, ele atribuirá o primeiro intervalo encontrado à variável de intervalo MyRange.
Em seguida, as caixas de mensagem serão exibidas com o endereço, coluna e linha do texto encontrado.
Neste exemplo, as configurações padrão de Localizar são usadas (assumindo que não foram alteradas na janela Localizar do Excel):
- O texto da pesquisa corresponde parcialmente ao valor da célula (não é necessária uma correspondência exata da célula)
- A busca não diferencia maiúsculas de minúsculas.
- Localizar pesquisa apenas uma única planilha
Essas configurações podem ser alteradas com vários parâmetros opcionais (discutidos abaixo).
Encontrar notas de método
- Localizar não seleciona a célula onde o texto foi encontrado. Ele apenas identifica o intervalo encontrado, que você pode manipular em seu código.
- O método Find localizará apenas a primeira instância encontrada.
- Você pode usar curingas (*), por exemplo procure 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 um objeto de intervalo vazio no VBA usando o Operador Is:
| 1 | If Not MyRange Is Nothing Then |
Adicionando o código ao nosso exemplo anterior:
| 12345678910111213 | Sub TestFind ()Dim MyRange As RangeDefinir MyRange = Sheets ("Sheet1"). UsedRange.Find ("funcionário")If Not MyRange Is Nothing ThenMsgBox MyRange.AddressMsgBox MyRange.ColumnMsgBox MyRange.RowOutroMsgBox "Não encontrado"Fim seEnd Sub |
Parâmetros de busca
Até agora, vimos apenas um exemplo básico do uso do método Find. No entanto, existem vários parâmetros opcionais disponíveis para ajudá-lo a refinar sua pesquisa
| Parâmetro | Modelo | Descrição | Valores |
| O que | Obrigatório | O valor a ser pesquisado | Qualquer tipo de dados, como string ou numérico |
| Depois de | Opcional | Referência de célula única para começar sua pesquisa | Endereço de celular |
| Olhar dentro | Opcional | Use fórmulas, valores, comentários para pesquisa | xlValues, xlFormulas, xlComments |
| Olhe para | Opcional | Corresponde a parte ou todo de uma célula | xlWhole, xlPart |
| SearchOrder | Opcional | A ordem de pesquisa em - linhas ou colunas | xlByRows, xlByColummns |
| SearchDirection | Opcional | Direção para a pesquisa avançar - para frente ou para trás | xlNext, xlPrevious |
| Caso de compatibilidade | Opcional | A pesquisa diferencia maiúsculas de minúsculas ou não | Verdadeiro ou falso |
| MatchByte | Opcional | Usado apenas se você tiver instalado o suporte a idiomas de byte duplo, por exemplo, língua chinesa | Verdadeiro ou falso |
| SearchFormat | Opcional | Permitir pesquisa por formato de célula | Verdadeiro ou falso |
Após o parâmetro e encontrar vários valores
Você usa o Após o parâmetro para especificar a célula inicial de sua pesquisa. Isso é útil onde há mais de uma instância do valor que você está procurando.
Se uma pesquisa já encontrou um valor e você sabe que haverá mais valores encontrados, então use o método Find com o parâmetro ‘Depois’ 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 seu texto de pesquisa:
| 123456789101112131415161718192021222324252627282930313233343536 | Sub TestMultipleFinds ()Dim MyRange As Range, OldRange As Range, FindStr As String'Procure a primeira instância de "' Luz e calor"Defina MyRange = Sheets ("Sheet1"). UsedRange.Find ("Light & Heat")'Se não for encontrado, saiaSe MyRange não for nada, saia do Sub'Mostrar primeiro endereço encontradoMsgBox MyRange.Address'Faça uma cópia do objeto de alcanceDefinir OldRange = MyRange'Adicione o endereço à string delimitando com um "|" personagemFindStr = FindStr & "|" & MyRange.Address'Iterar através do intervalo procurando outras instânciasFazer'Procure por' Luz e Calor 'usando o endereço encontrado anteriormente como o parâmetro DepoisDefinir MyRange = Sheets ("Sheet1"). UsedRange.Find ("Light & Heat", After: = Range (OldRange.Address))'Se o endereço já foi encontrado, saia do loop do - isso interrompe o loop contínuoIf InStr (FindStr, MyRange.Address) Then Exit Faça'Mostrar o último endereço encontradoMsgBox MyRange.Address'Adicione o último endereço à cadeia de endereçosFindStr = FindStr & "|" & MyRange.Address'fazer uma cópia do intervalo atualDefinir OldRange = MyRangeCicloEnd Sub |
Este código irá iterar através do intervalo usado e exibirá o endereço toda vez que encontrar uma instância de 'Luz e Calor'
Observe que o código continuará em loop até que um endereço duplicado seja encontrado em FindStr, caso em que sairá do loop Do.
Parâmetro LookIn
Você pode usar o Parâmetro LookIn para especificar em qual componente da célula você deseja pesquisar. Você pode especificar valores, fórmulas ou comentários em uma célula.
- xlValues - Pesquisa valores de células (o valor final de uma célula após seu cálculo)
- xlFormulas - Pesquisa dentro da própria fórmula da célula (tudo o que for inserido na célula)
- xlComments - Pesquisas em notas de células
- xlCommentsThreaded - Pesquisas nos comentários das células
Supondo que uma fórmula foi inserida na planilha, você pode usar este código de exemplo para encontrar o primeiro local de qualquer fórmula:
| 12345678910 | Sub TestLookIn ()Dim MyRange As RangeDefinir MyRange = Sheets ("Sheet1"). UsedRange.Find ("=", LookIn: = xlFormulas)If Not MyRange Is Nothing ThenMsgBox MyRange.AddressOutroMsgBox "Não encontrado"Fim seEnd Sub |
Se o parâmetro ‘LookIn’ foi definido como xlValues, o código exibiria uma mensagem ‘Não encontrado’. Neste exemplo, ele retornará B10.
Usando o parâmetro LookAt
o Parâmetro LookAt determina se find irá pesquisar uma correspondência de célula exata ou pesquisar qualquer célula que contenha o valor de pesquisa.
- xlWhole - Requer que toda a célula corresponda ao valor de pesquisa
- xlPart - Pesquisa dentro de uma célula pela string de pesquisa
Este exemplo de código irá localizar a primeira célula que contém o texto “light”. Com Lookat: = xlPart, ele retornará uma correspondência para “Light & Heat”.
| 123456789 | Sub TestLookAt ()Dim MyRange As RangeDefinir MyRange = Sheets ("Sheet1"). UsedRange.Find ("light", Lookat: = xlPart)If Not MyRange Is Nothing ThenMsgBox MyRange.AddressOutroMsgBox "Não encontrado"Fim seEnd Sub |
Se xlWhole foi definido, uma correspondência retornaria apenas se o valor da célula fosse “light”.
Parâmetro SearchOrder
o Parâmetro SearchOrder dita como a pesquisa será realizada em todo o intervalo.
- xlRows - A pesquisa é feita linha por linha
- xlXolumns - A pesquisa é feita coluna por coluna
| 123456789 | Sub TestSearchOrder ()Dim MyRange As RangeDefinir MyRange = Sheets ("Sheet1"). UsedRange.Find ("funcionário", SearchOrder: = xlColumns)If Not MyRange Is Nothing ThenMsgBox MyRange.AddressOutroMsgBox "Não encontrado"Fim seEnd Sub |
Isso influencia a combinação que 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 dentro do intervalo de pesquisa e quiser encontrar a primeira instância em um nome de coluna específico.
Parâmetro SearchDirection
o Parâmetro SearchDirection dita em qual direção a pesquisa irá - efetivamente para frente ou para trás.
- xlNext - Pesquise o próximo valor correspondente no intervalo
- xlPrevious - Pesquise o valor correspondente anterior no intervalo
Novamente, se houver valores duplicados dentro do intervalo de pesquisa, pode haver um efeito sobre qual deles será encontrado primeiro.
| 12345678910 | Sub TestSearchDirection ()Dim MyRange As RangeDefinir MyRange = Sheets ("Sheet1"). UsedRange.Find ("heat", SearchDirection: = xlPrevious)If Not MyRange Is Nothing ThenMsgBox MyRange.AddressOutroMsgBox "Não encontrado"Fim seEnd Sub |
Usando esse código nos dados de teste, uma direção de pesquisa de xlPrevious retornará uma localização de C9. Usar o parâmetro xlNext retornará uma localização de A4.
O próximo parâmetro significa que a pesquisa começará no canto superior esquerdo do intervalo de pesquisa e trabalhará para baixo. O parâmetro Anterior significa que a pesquisa começará no canto inferior direito do intervalo de pesquisa e trabalhará para cima.
Parâmetro MatchByte
o Parâmetro MatchBye só é usado para idiomas que usam um byte duplo para representar cada caractere, como chinês, russo e japonês.
Se este parâmetro for definido como ‘True’, Find só combinará caracteres de byte duplo com caracteres de byte duplo. Se o parâmetro for definido como ‘Falso’, um caractere de byte duplo corresponderá a caracteres de byte único ou duplo.
Parâmetro SearchFormat
o Parâmetro SearchFormat permite que você pesquise formatos de células correspondentes. Pode ser uma fonte específica em uso, uma fonte em negrito ou uma cor de texto. Antes de usar este 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:
| 12345678910111213 | Sub TestSearchFormat ()Dim MyRange As RangeApplication.FindFormat.ClearApplication.FindFormat.Font.Bold = TrueDefina MyRange = Sheets ("Sheet1"). UsedRange.Find ("heat", Searchformat: = True)If Not MyRange Is Nothing ThenMsgBox MyRange.AddressOutroMsgBox "Não encontrado"Fim seApplication.FindFormat.ClearEnd Sub |
Neste exemplo, o FindFormat propriedade é definida para procurar uma fonte em negrito. A instrução Find então procura a palavra ‘calor’ configurando o parâmetro SearchFormat como True para que ele retorne apenas 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 ‘calor’ em negrito.
Certifique-se de que a propriedade FindFormat seja desmarcada no final do código. Se você não fizer isso, sua próxima pesquisa ainda levará isso em consideração e retornará resultados incorretos.
Onde você usa um parâmetro SearchFormat, também pode usar um curinga (*) como o valor de pesquisa. Nesse caso, ele pesquisará qualquer valor com uma fonte em negrito:
| 1 | Definir MyRange = Sheets ("Sheet1"). UsedRange.Find ("*", Searchformat: = True) |
Usando 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 ver todo o texto da célula, mas diferencia maiúsculas de minúsculas
| 123456789 | Sub TestMultipleParameters ()Dim MyRange As RangeDefina MyRange = Sheets ("Sheet1"). UsedRange.Find ("Light & Heat", LookAt: = xlWhole, MatchCase: = True)If Not MyRange Is Nothing ThenMsgBox MyRange.AddressOutroMsgBox "Não encontrado"Fim seEnd Sub |
Neste exemplo, o código retornará A4, mas se usarmos apenas uma parte do texto, por exemplo, ‘Calor’, nada seria encontrado porque estamos combinando em todo o valor da célula. Além disso, haveria falha devido ao caso não corresponder.
| 1 | Defina MyRange = Sheets ("Sheet1"). UsedRange.Find ("heat", LookAt: = xlWhole, MatchCase: = True) |
Substituir no Excel VBA
Há, como você pode esperar, uma função Substituir no Excel VBA, que funciona de maneira muito semelhante a ‘Encontrar’, mas substitui os valores no local da célula encontrado por um novo valor.
Esses são os parâmetros que você pode usar em uma instrução do método Replace. Eles operam exatamente da mesma maneira que para a instrução do método Find. A única diferença para ‘Encontrar’ é que você precisa especificar um parâmetro de substituição.
| Nome | Modelo | Descrição | Valores |
| O que | Obrigatório | O valor a ser pesquisado | Qualquer tipo de dados, como string ou numérico |
| Substituição | Obrigatório | A string de substituição. | Qualquer tipo de dados, como string ou numérico |
| Olhe para | 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 |
| Caso de compatibilidade | Opcional | A pesquisa diferencia maiúsculas de minúsculas ou não | Verdadeiro ou falso |
| MatchByte | Opcional | Usado apenas se você tiver instalado o suporte a idiomas de byte duplo | Verdadeiro ou falso |
| SearchFormat | Opcional | Permitir pesquisa por formato de célula | Verdadeiro ou falso |
| ReplaceFormat | Opcional | O formato de substituição do método. | Verdadeiro ou falso |
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. Você precisa definir a propriedade Application.FindFormat primeiro, conforme mostrado no código de exemplo Find mostrado anteriormente
Substituir sem parâmetros opcionais
Na sua forma mais simples, você só precisa especificar o que está procurando e o que deseja substituir.
| 123 | Sub TestReplace ()Folhas ("Folha1"). UsedRange.Replace What: = "Light & Heat", Replacement: = "L & H"End Sub |
Observe que o método Find retornará apenas a primeira instância do valor correspondido, 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 irá substituir todas as instâncias de ‘Light & Heat’ por ‘L & H’ em todo o intervalo de células definido pelo objeto UsedRange
Usando VBA para localizar ou substituir texto em uma string de texto VBA
Os exemplos acima funcionam muito bem ao usar o VBA para interagir com dados do Excel. No entanto, para interagir com strings VBA, você pode usar funções VBA integradas, como INSTR e REPLACE.
Você pode usar o Função INSTR para localizar uma string de texto dentro de uma string mais longa.
| 123 | Sub TestInstr ()MsgBox InStr ("Esta é a string MeuTexto", "MeuTexto")End Sub |
Este código de exemplo retornará o valor 9, que é a posição do número onde ‘MeuTexto’ é encontrado na string a ser pesquisada.
Observe que é sensível a maiúsculas e minúsculas. Se ‘MeuTexto’ estiver em letras minúsculas, um valor 0 será retornado, o que significa que a string de pesquisa não foi encontrada. Abaixo, discutiremos como desativar a diferenciação de maiúsculas e minúsculas.
INSTR - Iniciar
Existem mais dois parâmetros opcionais disponíveis. Você pode especificar o ponto de início da pesquisa:
| 1 | MsgBox InStr (9, "Esta é a string de MeuTexto", "MeuTexto") |
O ponto inicial é especificado como 9, portanto, ainda retornará 9. Se o ponto inicial fosse 10, ele retornaria 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 para vbBinaryCompare ou vbTextCompare. Se você definir este parâmetro, a instrução deve 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
| 1 | MsgBox InStr (1, "This is MyText string", "mytext", vbTextCompare) |
Essa instrução ainda retornará 9, embora o texto da pesquisa esteja em letras minúsculas.
Para desativar a diferenciação de 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 Substituir VBA
Se você deseja substituir caracteres em uma string por um texto diferente em seu código, o método Replace é ideal para isso:
| 123 | Sub TestReplace ()MsgBox Replace ("Esta é a string de MeuTexto", "MeuTexto", "Meu Texto")End Sub |
Este código substitui ‘MeuTexto’ por ‘Meu texto’. Observe que a string 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:
- Começar - define a posição na string inicial a partir da qual a substituição deve começar. Ao contrário do método Find, ele retorna uma string truncada a partir do número do caractere definido pelo parâmetro Start.
- Contar - define o número de substituições a serem feitas. Por padrão, Replace mudará cada instância do texto de pesquisa encontrado, mas você pode limitar isso a uma única substituição, definindo o parâmetro Count para 1
- Comparar - como no método Find, você pode especificar uma pesquisa binária ou uma pesquisa de texto usando vbBinaryCompare ou vbTextCompare. O binário diferencia maiúsculas de minúsculas e o texto não diferencia maiúsculas de minúsculas
| 1 | MsgBox Replace ("This is MyText string (mytext)", "MyText", "My Text", 9, 1, vbTextCompare) |
Este código retorna ‘My Text string (mytext)’. Isso ocorre porque o ponto inicial fornecido é 9, então a nova string retornada começa no caractere 9. Apenas o primeiro ‘MyText’ foi alterado porque o parâmetro Count está definido como 1.
O método Substituir é ideal para resolver problemas como nomes de pessoas contendo apóstrofos. O'Flynn. Se você estiver usando aspas simples para definir um valor de string e houver um apóstrofo, isso causará um erro porque o código interpretará o apóstrofo como o final da string e não reconhecerá o restante da string.
Você pode usar o método Replace para substituir o apóstrofo por nada, removendo-o completamente.
