Função Substituir VBA do Excel

Como no Excel, temos uma função na qual podemos encontrar e substituir qualquer palavra, caractere ou frase por qualquer letra. Mas, por esse processo, só podemos substituir um tipo de frase ou letra de cada vez. Com a ajuda do VBA Replace Function, podemos substituir quantas palavras, letras ou frases em um único tiro. Isso economiza muito tempo e esforço ao realizar uma única atividade várias vezes. Para isso, usaremos a função Replace, que está na lista de funções do VBA incorporada.

Abaixo da sintaxe e do argumento Substituir a função no VBA.

Como usar a função de substituição do Excel VBA?

Vamos aprender como usar uma função VBA Replace Excel com alguns exemplos.

Você pode fazer o download deste VBA Replace Excel Template aqui - VBA Replace Excel Template

Função Substituir VBA - Exemplo # 1

Temos um exemplo de dados de algumas frases em que substituiremos uma palavra e colaremos a frase atualizada na coluna da sentença B.

Siga as etapas abaixo para usar a função Substituir no Excel VBA.

Etapa 1: Vá para VBA e, na guia Inserir, selecione Módulo.

Etapa 2: Agora escreva a subcategoria no nome de uma função executada ou em qualquer nome, conforme sua escolha, como mostrado abaixo.

Código:

 Sub VBA_Replace2 () End Sub

Etapa 3: Agora defina uma dimensão como Long, pois selecionaremos uma frase completa. Aqui nós o tomamos como X.

Código:

 Sub VBA_Replace2 () Dim X como sub final longo

Etapa 4: agora nesse X longo, selecione o intervalo máximo até onde nossos dados podem subir. Aqui tomamos como intervalo de células A1000 e, para subir até nossos dados começarem, use End (xlUp) seguido de uma linha de ponto (.). O que significa que iremos subir na mesma coluna até a linha que contém os dados. Esse processo na vida Ctrl + Seta para cima no excel.

Código:

 Sub VBA_Replace2 () Dim X como longo X = Intervalo ("A1000"). End (xlUp) .Row End Sub

Nota : Podemos selecionar End (xlDown), mas ele não pode adicionar nenhuma quebra ou célula em branco entre os dados, se houver.

Etapa 5: Agora defina mais uma dimensão Y, desde que seja mostrado abaixo.

Código:

 Sub VBA_Replace2 () Dim X como longo X = Intervalo ("A1000"). Fim (xlUp) .Da linha Y como comprimento final Sub

Etapa 6: Agora inicie um loop For Next para a segunda dimensão definida Y da posição da célula 2 até o intervalo X (última célula preenchida)

Nota: Selecionar a célula 2 significa que não estamos considerando o cabeçalho aqui para substituição.

Código:

 Sub VBA_Replace2 () Dim X como longo X = Intervalo ("A1000"). Fim (xlUp) .Linha Dim Y como comprimento para Y = 2 a X Próximo Y Fim Sub

Etapa 7: Agora selecione o Valor da coluna B como Intervalo como Y, seguido de um ponto (.), Isto é como inserir uma função no Excel.

Código:

 Sub VBA_Replace2 () Dim X como longo X = intervalo ("A1000"). Fim (xlUp) .Remota Dim Y como longo para Y = 2 a X intervalo ("B" e Y) .Valor = próximo Y End Sub

Etapa 8: Agora, como vimos na sintaxe do VBA da função Substituir acima, digite Substituir e selecione as Colunas A e Y como a primeira expressão da sequência seguida pelas palavras que precisamos substituir da tabela.

Código:

 Sub VBA_Replace2 () Dim X como longo X = intervalo ("A1000"). Fim (xlUp) .Dimensão da linha Y como comprimento para Y = 2 a X intervalo ("B" e Y). Valor = substituir (intervalo ("A "& Y), " Delhi ", " New Delhi ") Próximo Y End Sub

Etapa 9: Agora compile o código completo e execute. Assim que fizermos isso, substituiremos a palavra "Delhi" da frase A pelo trabalho "New Delhi" e a frase inteira será copiada para a frase B.

Podemos selecionar uma sequência de palavras ou uma frase e substituí-la por quaisquer letras ou frases necessárias.

Função Substituir VBA - Exemplo # 2

Há outra maneira de substituir palavras ou frases com a ajuda da codificação VBA. Para isso, temos uma lista de 7 assuntos que estão sendo repetidos. E precisamos substituir esses nomes de sujeitos pela adição de números de série antes de cada nome de assunto, conforme mostrado abaixo.

Como podemos ver na captura de tela acima, a coluna E possui nomes exclusivos dos sujeitos que estão na coluna B e a coluna F possui os nomes dos sujeitos com um número de série no início.

Siga as etapas abaixo para usar a função Substituir no VBA.

Etapa 1: Agora, vá para VBA e, no menu Inserir, adicione um novo módulo. Assim que o obtivermos, comece a escrever a subcategoria no nome de uma função que está sendo executada como mostrado abaixo.

Código:

 Sub VBA_Replace () End Sub

Etapa 2: Agora considere uma dimensão Rng como intervalo, InputRng como intervalo e ReplaceRng como intervalo. Você pode escolher outras letras ou palavras para definir intervalos.

Código:

 Sub VBA_Replace () Dim Rng como intervalo Dim InputRng como intervalo, ReplaceRng como intervalo End Sub

Etapa 3: Agora use o xTitleId como caixa de diálogo e dê um nome a ele. Aqui denominamos " VBA_Replace ".

Código:

 Sub VBA_Replace () Dim Rng como intervalo Dim InputRng como intervalo, ReplaceRng como intervalo xTitleId = "VBA_Replace" End Sub

Etapa 4: agora atribua Application.Selection com InputRng, isso permitirá que o aplicativo selecionado seja usado no VBA.

Código:

 Sub VBA_Replace () Dim Rng como intervalo Dim InputRng como intervalo, ReplaceRng como intervalo xTitleId = "VBA_Replace" Definir InputRng = Application.Selection End Sub

Etapa 5: Agora, na próxima linha, insira um InputBox de 3 tipos, Original range, xTitleId e InputRng. Intervalo original é uma lista dos assuntos necessários para a substituição, listados na coluna B. xTitledId é o nome da caixa de diálogo na função executada. E InputRng é um intervalo de tabela de dados nas colunas E e F.

Código:

 Sub VBA_Replace () Dim Rng como intervalo Dim InputRng como intervalo, ReplaceRng como intervalo xTitleId = "VBA_Replace" Defina InputRng = Application.Selection Define InputRng = Application.InputBox ("Intervalo original", xTitleId, InputRng.Address, Tipo: = 8) End Sub

Etapa 6: Agora, na próxima linha, atribua uma entrada para ReplaceRng e, para isso, selecione a coluna Substituir intervalo. Resto da mesma forma.

Código:

 Sub VBA_Replace () Dim Rng como intervalo Dim InputRng como intervalo, ReplaceRng como intervalo xTitleId = "VBA_Replace" Defina InputRng = Application.Selection Define InputRng = Application.InputBox ("Intervalo original", xTitleId, InputRng.Address, Tipo: = 8) Defina ReplaceRng = Application.InputBox ("Substituir intervalo:", xTitleId, Tipo: = 8) End Sub

Etapa 7: Agora usaremos a função Application.Screenupdating, como o nome diz que é usado para atualizar, se for FALSE.

Código:

 Sub VBA_Replace () Dim Rng como intervalo Dim InputRng como intervalo, ReplaceRng como intervalo xTitleId = "VBA_Replace" Defina InputRng = Application.Selection Define InputRng = Application.InputBox ("Intervalo original", xTitleId, InputRng.Address, Tipo: = 8) Defina ReplaceRng = Application.InputBox ("Replace Range:", xTitleId, Tipo: = 8) Application.ScreenUpdating = False End Sub

Etapa 8: Agora insira um loop For-Next . E para cada intervalo de Rng, substitua os valores da coluna ReplaceRng.

Código:

 Sub VBA_Replace () Dim Rng como intervalo Dim InputRng como intervalo, ReplaceRng como intervalo xTitleId = "VBA_Replace" Defina InputRng = Application.Selection Define InputRng = Application.InputBox ("Intervalo original", xTitleId, InputRng.Address, Tipo: = 8) Defina ReplaceRng = Application.InputBox ("Replace Range:", xTitleId, Type: = 8) Application.ScreenUpdating = False para cada Rng em ReplaceRng.Columns (1). Células Next End Sub

Etapa 9: Por fim, substitua InputRng pelos valores presentes em Rng da folha inteira.

Código:

 Sub VBA_Replace () Dim Rng como intervalo Dim InputRng como intervalo, ReplaceRng como intervalo xTitleId = "VBA_Replace" Defina InputRng = Application.Selection Define InputRng = Application.InputBox ("Intervalo original", xTitleId, InputRng.Address, Tipo: = 8) Defina ReplaceRng = Application.InputBox ("Replace Range:", xTitleId, Type: = 8) Application.ScreenUpdating = False para cada Rng Em ReplaceRng.Columns (1) .Cells InputRng.Replace what: = Rng.Value, substituto: = Rng.Offset (0, 1) .Value, Lookat: = xlWhole Próximo final Sub

Etapa 10: uma vez concluído, agora compile e execute o código. Obteremos uma caixa de diálogo em nome de VBA_Replace. A partir daqui, selecione a lista de assuntos que precisamos substituir como Faixa original e clique em OK. Em seguida, obteremos outra caixa a partir daí, selecione o intervalo Substituir de E2 a F8 . Esta tabela possui os dados que precisam ser substituídos e clique em OK.

Obteremos dados na coluna B, que serão substituídos pelos dados da coluna F pelo número de série.

Para uma visão geral, a coluna E possui nomes de assunto exclusivos. E a coluna F tem nomes de assunto com números de série. Portanto, esses dados na coluna B são substituídos pelos dados da coluna F.

Profissionais da função de substituição do VBA

  • Podemos substituir várias palavras ou frases em um único tiro.
  • Não há limite de palavras ou texto pelo que não podemos substituir.
  • A sintaxe de Substituir no VBA é tão fácil quanto usar a função SumIf no Excel.
  • Substituir VBA como mostrado no exemplo-1 é a maneira mais fácil de aplicar.

Coisas para lembrar

  • Salvar como Marco Habilitou o Excel para evitar a perda de código escrito no VBA.
  • Sempre considere as dimensões de forma a criar um valor agregado enquanto as selecionamos no código.
  • Certifique-se de selecionar todo o intervalo de folhas, como mostrado no exemplo-2, se for limitado para obter o resultado. Ou então, você pode definir e selecionar um intervalo limitado de células que seria usado na função Substituir.

Artigos recomendados

Este foi um guia para o VBA Replace Function. Aqui discutimos o VBA Replace e como usar a função VBA Replace do Excel, além de exemplos práticos e modelo de excel para download. Você também pode consultar nossos outros artigos sugeridos -

  1. Tutoriais completos sobre loops VBA
  2. Como usar o VBA Do While Loop?
  3. O que é o formato de número VBA?
  4. Como criar VBA MsgBox?

Categoria:

VBA