Localizar e substituir VBA do Excel

Você já pensou em automatizar a função Localizar e substituir no Excel. No VBA, podemos criar uma macro que pode ser usada para localizar e substituir tudo o que normalmente fazemos no Excel pressionando as teclas de atalho Ctrl + H. Com a ajuda do VBA Find and Replace, podemos automatizar a localização de qualquer palavra e substituí-la por outra. Isso ajuda quando precisamos executar a mesma atividade várias vezes.

Como encontrar e substituir palavras no Excel VBA?

Abaixo estão os diferentes exemplos para encontrar e substituir as palavras no Excel usando o código VBA.

Você pode baixar este modelo de localização e substituição do VBA do Excel aqui - Modelo de localização e substituição do VBA do Excel

Localizar e substituir VBA - Exemplo # 1

Em um exemplo muito simples, temos um conjunto de dados que consiste em algum nome. E alguns dos nomes estão sendo repetidos, como mostrado abaixo.

Agora vamos criar um código, pelo qual encontraremos qualquer um dos nomes que está sendo repetido e substituiremos por outra. Para isso, siga as etapas abaixo.

Etapa 1: Vá para o menu Inserir no VBA e selecione a opção Módulo, como mostrado abaixo.

Etapa 2: assim que fizermos isso, abriremos um novo módulo. Nesse módulo, escreva a subcategoria no nome do VBA Find and Replace ou você pode escolher qualquer nome dessa subcategoria.

Código:

 Sub Find_Replace1 () End Sub

Etapa 3: como temos os dados na coluna B da célula B2 a B10, selecionaremos esse intervalo primeiro de B2: B10.

Código:

 Sub Find_Replace1 () Range ("B2: B10"). Substitua End Sub

Etapa 4: agora, para substituir algo, precisamos procurar essa palavra. Aqui também vamos primeiro selecionar a função Substituir para ver o que precisamos substituir.

Código:

 Sub Find_Replace1 () Range ("B2: B10"). Substitua End Sub

De acordo com a sintaxe da função Substituir, encontraremos o que precisamos substituir.

Etapa 5: vamos selecionar o primeiro nome da lista " Ben " como o que precisamos substituir.

Código:

 Sub Find_Replace1 () Range ("B2: B10"). Substitua o que: = "Ben" End Sub

Etapa 6: Agora escolha a palavra de substituição pela qual precisamos substituir a palavra selecionada. Aqui, escolhemos substituir o nome Ben por Sam, que é mencionado como Substituição, como mostrado abaixo.

Código:

 Sub Find_Replace1 () Range ("B2: B10"). Substitua o que: = "Ben", Substituição: = "Sam" End Sub

Etapa 7: Agora compile o código e execute-o clicando no botão Play ou pressionando a tecla F5, como mostrado abaixo.

Veremos que todas as células que contêm o nome como Ben agora são substituídas pelo nome Sam . E isso também é destacado na cor amarela.

Localizar e substituir VBA - exemplo # 2

Todos nós podemos ter enfrentado uma situação em que tentamos encontrar e substituir algumas palavras por letras que diferenciam maiúsculas de minúsculas. Suponhamos em uma lista, se queremos substituir apenas a palavra que possui letras minúsculas ou maiúsculas. E se estivermos usando o processo que vimos no exemplo-1, acabaríamos substituindo todas as palavras semelhantes nessa lista junto com a necessária. Este processo não pode ser feito com a ajuda de encontrar e substituir a função (Ctrl + H) do excel. Para isso, temos uma provisão no VBA.

Vamos considerar os dados abaixo para isso. Como podemos ver, propositadamente adicionamos uma palavra BEN em maiúsculas na célula B2.

Siga as etapas abaixo para encontrar e substituir a palavra BEN.

Etapa 1: Inicie a subcategoria do VBA Find and Replace, conforme mostrado abaixo.

Código:

 Sub Find_Replace2 () End Sub

Etapa 2: selecione a lista como Intervalo de B2 a B10.

Código:

 Sub Find_Replace2 () Intervalo ("B2: B10") End Sub

Etapa 3: agora escolha a palavra exata que queremos substituir. Aqui nós selecionamos BEN, que está na célula B2.

Código:

 Sub Find_Replace2 () Range ("B2: B10"). Substitua o que: = "BEN" End Sub

Etapa 4: Mais uma vez, selecionamos a mesma palavra que vimos no exemplo 1 como substituição do BEN como Sam .

Código:

 Sub Find_Replace2 () Range ("B2: B10"). Substitua o que: = "BEN", Substituição: = "Sam" End Sub

Etapa 5: agora, para substituir a palavra exata BEN, precisamos usar o MatchCase se isso se tornar VERDADEIRO, como mostrado abaixo.

Código:

 Sub Find_Replace2 () Range ("B2: B10"). Substitua o que: = "BEN", Substituição: = "Sam", MatchCase: = True End Sub

O MatchCase nos ajudará a identificar a palavra que queremos substituir por uma correspondência exata. Aqui, a palavra que selecionamos é BEN em maiúsculas e list também tem outras palavras semelhantes, mas no caso apropriado.

Etapa 6: Agora execute o código clicando no botão Play ou pressionando a tecla F5.

Veremos que a palavra BEN que estava na célula B2 agora é substituída pela palavra " Sam ", destacada em amarelo. E as palavras semelhantes Ben, que estão localizadas nas células B5 e B8, respectivamente, continuam inalteradas.

É assim que funciona a correspondência exata.

Etapa 7: agora, removeremos o MatchCase adicionado do código acima e veremos como isso funcionaria.

Código:

 Sub Find_Replace2 () Range ("B2: B10"). Substitua o que: = "BEN", Substituição: = "Sam", MatchCase: = True End Sub

Etapa 8: Compile novamente e execute o código.

Veremos que o código substituiu todas as células que contêm a palavra Ben, como mostrado acima. O que significa que, após a remoção do MatchCase, o código funcionará como vimos no exemplo 1.

Profissionais do Excel VBA Localizar e substituir

  • O aprendizado principal é que podemos substituir as palavras que diferenciam maiúsculas de minúsculas e o conteúdo da célula com a ajuda do VBA Find and Replace, conforme mostrado no exemplo-2.
  • Se a atividade for repetida manualmente e várias vezes, automatizar isso seria uma vantagem para economizar tempo e esforço.
  • Mesmo se tivermos um grande conjunto de dados em que desejamos substituir palavras específicas, isso pode ser feito sem causar uma falha no excel em arquivos volumosos.

Coisas para lembrar

  • Podemos criar uma macro da função Localizar e substituir com a ajuda da opção Gravar macro na guia Desenvolvedor. Essa é a maneira mais fácil de criar uma macro se você não conhece a codificação.
  • Salvar o arquivo no formato Macro Ativar Excel permitirá codificar a utilização no futuro.
  • Podemos substituir qualquer tipo de texto, palavra, número ou caractere pelo VBA Find and Replace.

Artigos recomendados

Este é um guia para o VBA Find and Replace. Aqui discutimos como encontrar e substituir palavras no Excel usando o VBA, além de exemplos práticos e modelo de excel para download. Você também pode consultar nossos outros artigos sugeridos -

  1. Intervalo de VBA para loop
  2. SUBSTITUIR Fórmula no Excel
  3. Uma Guia Completa do VBA Find
  4. Função FIND no Excel

Categoria:

VBA