Funzione di sostituzione VBA di Excel
Come in Excel, abbiamo una funzione in cui possiamo trovare e sostituire qualsiasi parola o carattere o frase con qualsiasi lettera. Ma con questo processo, possiamo sostituire solo un tipo di frase o lettera alla volta. Con l'aiuto della funzione Sostituisci VBA, possiamo sostituire quante più parole o lettere o frasi in un solo colpo. Ciò consente di risparmiare tempo e fatica enormi facendo più volte una singola attività. Per questo, useremo la funzione Sostituisci che è nell'elenco delle funzioni VBA integrate.
Sotto la sintassi e l'argomento della funzione Sostituisci in VBA.
Come utilizzare la funzione di sostituzione VBA di Excel?
Impareremo come utilizzare una funzione Sostituisci Excel VBA con alcuni esempi.
Puoi scaricare questo VBA Sostituisci modello Excel qui - VBA Sostituisci modello ExcelFunzione di sostituzione VBA - Esempio n. 1
Abbiamo alcuni dati di esempio di alcune frasi in cui sostituiremo una parola e incolleremo quella frase aggiornata nella colonna Frase B.
Seguire i passaggi seguenti per utilizzare la funzione Sostituisci in Excel VBA.
Passaggio 1: vai su VBA e nella scheda del menu Inserisci, seleziona Modulo.
Passo 2: Ora scrivi la sottocategoria nel nome di una funzione eseguita o in qualsiasi nome secondo la tua scelta, come mostrato di seguito.
Codice:
Sottotitolo secondario VBA_Replace2 ()
Passaggio 3: ora definisci una dimensione come Lunga, poiché selezioneremo una frase completa. Qui l'abbiamo preso come X.
Codice:
Sottotitolo VBA_Replace2 () Dim X come sottotitolo long end
Passo 4: Ora in quella X lunga, seleziona l'intervallo massimo fino a dove i nostri dati potrebbero salire. Qui abbiamo preso come intervallo di celle A1000 e per salire fino a quando i nostri dati iniziano a utilizzare End (xlUp) seguito da un punto (.) Row. Ciò significa che ci sposteremo verso l'alto nella stessa colonna fino a quella riga che contiene i dati. Questo processo nella vita Ctrl + tasto freccia Su in Excel.
Codice:
Sub VBA_Replace2 () Dim X As Long X = Range ("A1000"). End (xlUp) .Row End Sub
Passaggio 5: ora definire di nuovo un'altra dimensione Y per quanto indicato di seguito.
Codice:
Sottotitolo VBA_Replace2 () Dim X come long X = Range ("A1000"). End (xlUp) .Row Dim Y as Long End Sub
Passaggio 6: avviare ora un ciclo For Next per la seconda dimensione definita Y dalla posizione della cella 2 fino all'intervallo X (ultima cella riempita)
Nota: la selezione della cella 2 significa che non stiamo prendendo in considerazione l'intestazione qui per la sostituzione.Codice:
Sottotitolo VBA_Replace2 () Dim X come lungo X = intervallo ("A1000"). Fine (xlUp). Dim. Dim Y come lungo per Y = 2 - X Prossimo Y Fine Sottotitolo
Passo 7: Ora seleziona il valore della colonna B come intervallo come Y seguito da un punto (.) È come inserire una funzione in Excel.
Codice:
Sub VBA_Replace2 () Dim X come Long X = Range ("A1000"). End (xlUp) .Row Dim Y Long come Y = 2 To X Range ("B" & Y) .Value = Next Y End Sub
Passo 8: Ora, come abbiamo visto nella sintassi VBA della funzione Sostituisci sopra, digita Sostituisci e seleziona Colonna A e Y come prima espressione della stringa seguita dalle parole che dobbiamo sostituire dalla tabella.
Codice:
Sotto VBA_Replace2 () Dim X come Long X = Range ("A1000"). End (xlUp) .Row Dim Y Long come Y = 2 To X Range ("B" & Y) .Value = Sostituisci (Range ("A "& Y), " Delhi ", " New Delhi ") Next Y End Sub
Passaggio 9: compilare ora il codice completo ed eseguirlo. Una volta fatto ciò, la parola "Delhi" della frase A viene ora sostituita con il lavoro "Nuova Delhi" e l'intera frase viene copiata nella frase B.
Possiamo selezionare una stringa di parole o una frase e sostituirla con qualsiasi lettera o frase richiesta.
Funzione di sostituzione VBA - Esempio n. 2
C'è un altro modo per sostituire parole o frasi con l'aiuto della codifica VBA. Per questo, abbiamo un elenco di 7 argomenti che si stanno ripetendo. E abbiamo bisogno di sostituire quei nomi di soggetto con l'aggiunta di numeri di serie prima di ogni nome di soggetto come mostrato di seguito.
Come possiamo vedere nello screenshot sopra, la colonna E ha nomi univoci di quei soggetti che sono lì nella colonna B e la colonna F ha i nomi dei soggetti con un numero seriale all'inizio di esso.
Seguire i passaggi seguenti per utilizzare la funzione Sostituisci in VBA.
Passaggio 1: ora vai su VBA e dal menu Inserisci aggiungi nuovo modulo. Una volta ottenuto, inizia a scrivere la sottocategoria nel nome di una funzione che viene eseguita come mostrato di seguito.
Codice:
Sub VBA_Replace () End Sub
Passaggio 2: ora considera una dimensione Rng come intervallo, InputRng come intervallo e ReplaceRng come intervallo. Puoi scegliere qualsiasi altra lettera o parola per definire gli intervalli.
Codice:
Sub VBA_Replace () Dim Rng As Range Dim InputRng As Range, ReplaceRng As Range End Sub
Passaggio 3: ora usa xTitleId come finestra di dialogo e assegnagli un nome. Qui l'abbiamo chiamato " VBA_Replace ".
Codice:
Sub VBA_Replace () Dim Rng As Range Dim InputRng As Range, ReplaceRng As Range xTitleId = "VBA_Replace" End Sub
Passaggio 4: Ora assegnare Application.Selection con InputRng, ciò consentirà di utilizzare l'applicazione selezionata in VBA.
Codice:
Sub VBA_Replace () Dim Rng As Range Dim InputRng As Range, ReplaceRng As Range xTitleId = "VBA_Replace" Set InputRng = Application.Selection End Sub
Passaggio 5: ora nella riga successiva inserire un InputBox di 3 tipi, intervallo originale, xTitleId e InputRng. Intervallo originale è un elenco di quegli argomenti che sono necessari per essere sostituiti, elencati nella colonna B. xTitledId sono i nomi delle finestre di dialogo nella funzione eseguita. E InputRng è un intervallo di tabella di dati nelle colonne E e F.
Codice:
Sub VBA_Replace () Dim Rng As Range Dim InputRng As Range, ReplaceRng As Range xTitleId = "VBA_Replace" Set InputRng = Application.Selection Set InputRng = Application.InputBox ("Range originale", xTitleId, InputRng.Address, Type: = 8) End Sub
Passaggio 6: ora nella riga successiva assegnare un input per ReplaceRng e per questo selezionare la colonna Sostituisci intervallo. Il resto è uguale.
Codice:
Sub VBA_Replace () Dim Rng As Range Dim InputRng As Range, ReplaceRng As Range xTitleId = "VBA_Replace" Set InputRng = Application.Selection Set InputRng = Application.InputBox ("Range originale", xTitleId, InputRng.Address, Type: = 8) Imposta ReplaceRng = Application.InputBox ("Sostituisci intervallo:", xTitleId, Tipo: = 8) End Sub
Passo 7: Ora useremo la funzione Application.Screenupdating, come dice il nome, è usata per l'aggiornamento se è FALSE.
Codice:
Sub VBA_Replace () Dim Rng As Range Dim InputRng As Range, ReplaceRng As Range xTitleId = "VBA_Replace" Set InputRng = Application.Selection Set InputRng = Application.InputBox ("Range originale", xTitleId, InputRng.Address, Type: = 8) Imposta ReplaceRng = Application.InputBox ("Sostituisci intervallo:", xTitleId, Tipo: = 8) Application.ScreenUpdating = Sottotitolo falso
Passaggio 8: ora inserire un ciclo For-Next . E per ogni intervallo Rng, sostituisci i valori dalla colonna ReplaceRng.
Codice:
Sub VBA_Replace () Dim Rng As Range Dim InputRng As Range, ReplaceRng As Range xTitleId = "VBA_Replace" Set InputRng = Application.Selection Set InputRng = Application.InputBox ("Range originale", xTitleId, InputRng.Address, Type: = 8) Imposta ReplaceRng = Application.InputBox ("Sostituisci intervallo:", xTitleId, Tipo: = 8) Application.ScreenUpdating = False per ogni Rng in ReplaceRng.Columns (1) .Cells Next End Sub
Passaggio 9: Sostituire infine InputRng con i valori presenti in Rng dall'intero foglio.
Codice:
Sub VBA_Replace () Dim Rng As Range Dim InputRng As Range, ReplaceRng As Range xTitleId = "VBA_Replace" Set InputRng = Application.Selection Set InputRng = Application.InputBox ("Range originale", xTitleId, InputRng.Address, Type: = 8) Imposta ReplaceRng = Application.InputBox ("Sostituisci intervallo:", xTitleId, Tipo: = 8) Application.ScreenUpdating = False per ogni Rng in ReplaceRng.Columns (1) .Cells InputRng.Sostituisci cosa: = Rng.Value, sostituzione: = Rng.Offset (0, 1) .Value, Lookat: = xlWhole Next End Sub
Passaggio 10: una volta terminato, compilare ed eseguire il codice. Otterremo una finestra di dialogo nel nome di VBA_Replace. Da qui selezionare l'elenco degli argomenti che è necessario sostituire come intervallo originale e fare clic su OK. Quindi otterremo un'altra casella da lì selezionare l'intervallo di sostituzione da E2 a F8 . Questa tabella contiene i dati che devono essere sostituiti e fare clic su OK.
Otterremo i dati nella colonna B che verranno sostituiti dai dati nella colonna F con il numero di serie.
Per una panoramica, la colonna E ha nomi di soggetti univoci. E la colonna F ha nomi di soggetti con numeri di serie. Quindi da questi dati nella colonna B vengono sostituiti i dati nella colonna F.
Pro di VBA Replace Function
- Possiamo sostituire più parole o frasi in un solo colpo.
- Non ci sono limiti di parole o testo a cui non possiamo sostituire.
- La sintassi di Sostituisci in VBA è facile come usare la funzione SumIf in Excel.
- Sostituisci VBA come mostrato nell'esempio 1 è il modo più semplice per applicare.
Cose da ricordare
- Salva come Marco Abilita Excel per evitare di perdere il codice scritto in VBA.
- Considera sempre le dimensioni in modo tale da creare un valore aggiunto mentre selezioniamo quelle nel codice.
- Assicurati di selezionare l'intera gamma di fogli come mostrato nell'esempio-2 se è limitato per ottenere il risultato. Altrimenti puoi definire e selezionare un intervallo di celle limitato che verrebbe utilizzato nella funzione Sostituisci.
Articoli consigliati
Questa è stata una guida alla funzione di sostituzione VBA. Qui abbiamo discusso di VBA Sostituisci e di come utilizzare la funzione Sostituisci VBA di Excel insieme ad esempi pratici e template Excel scaricabili. Puoi anche consultare i nostri altri articoli suggeriti:
- Tutorial completi sui loop VBA
- Come usare VBA Do While Loop?
- Che cos'è il formato numero VBA?
- Come creare VBA MsgBox?