Questo tutorial dimostrerà come utilizzare i metodi Trova e sostituisci in Excel VBA.
Trova VBA
Excel ha un eccellente built-in Trova e Trova e sostituisci utensili.
Possono essere attivati con le scorciatoie CTRL + F (Trova) o CTRL + H (Sostituisci) o tramite la barra multifunzione: Home > Modifica > Trova e seleziona.
Cliccando Opzioni, puoi visualizzare le opzioni di ricerca avanzata:
Puoi facilmente accedere a entrambi i metodi Trova e Sostituisci utilizzando VBA. Questi metodi integrati sono molto più veloci di qualsiasi cosa tu possa scrivere in VBA.
Trova esempio VBA
Per dimostrare la funzionalità Trova, abbiamo creato il seguente set di dati in Foglio1.
Se desideri seguire, inserisci i dati nella tua cartella di lavoro.
<<<<<<<<<<<<<<<<<<<>>>>>>>>>>>>>>>>>>>
Trova VBA senza parametri opzionali
Quando si utilizza il metodo VBA Find, sono disponibili molti parametri facoltativi che è possibile impostare.
Si consiglia vivamente di definire tutti i parametri ogni volta che si utilizza il metodo Trova!
Se non si definiscono i parametri facoltativi, VBA utilizzerà i parametri attualmente selezionati nella finestra Trova di Excel. Ciò significa che potresti non sapere quali parametri di ricerca vengono utilizzati durante l'esecuzione del codice. Trova potrebbe essere eseguito sull'intera cartella di lavoro o su un foglio. Potrebbe cercare formule o valori. Non c'è modo di saperlo, a meno che tu non controlli manualmente ciò che è attualmente selezionato nella finestra Trova di Excel.
Per semplicità, inizieremo con un esempio senza parametri opzionali definiti.
Esempio di ricerca semplice
Diamo un'occhiata a un semplice esempio di ricerca:
| 123456789 | SottoTestTrova()Dim MyRange As RangeImposta MyRange = Sheets("Sheet1").UsedRange.Find("employee")MsgBox MyRange.AddressMsgBox MyRange.ColumnMsgBox MyRange.RowFine sottotitolo |
Questo codice cerca "dipendente" nell'intervallo utilizzato di Sheet1. Se trova "dipendente", assegnerà il primo intervallo trovato alla variabile di intervallo MyRange.
Successivamente, verranno visualizzate le finestre di messaggio con l'indirizzo, la colonna e la riga del testo trovato.
In questo esempio, vengono utilizzate le impostazioni di ricerca predefinite (supponendo che non siano state modificate nella finestra Trova di Excel):
- Il testo di ricerca è parzialmente abbinato al valore della cella (non è richiesta una corrispondenza esatta della cella)
- La ricerca non fa distinzione tra maiuscole e minuscole.
- Trova cerca solo un singolo foglio di lavoro
Queste impostazioni possono essere modificate con vari parametri opzionali (discussi di seguito).
Trova le note sul metodo
- Trova non seleziona la cella in cui si trova il testo. Identifica solo l'intervallo trovato, che puoi manipolare nel tuo codice.
- Il metodo Find individuerà solo la prima istanza trovata.
- È possibile utilizzare i caratteri jolly (*) ad es. cerca "E*"
Non abbiamo trovato nulla
Se il testo di ricerca non esiste, l'oggetto intervallo rimarrà vuoto. Ciò causa un grave problema quando il codice tenta di visualizzare i valori della posizione perché non esistono. Ciò si tradurrà in un messaggio di errore che non desideri.
Fortunatamente, puoi testare un oggetto intervallo vuoto all'interno di VBA usando l'operatore Is:
| 1 | Se non MyRange è niente allora |
Aggiungendo il codice al nostro esempio precedente:
| 12345678910111213 | SottoTestTrova()Dim MyRange As RangeImposta MyRange = Sheets("Sheet1").UsedRange.Find("employee")Se non MyRange è niente alloraMsgBox MyRange.AddressMsgBox MyRange.ColumnMsgBox MyRange.RowAltroMsgBox "Non trovato"Finisci seFine sottotitolo |
Trova parametri
Finora, abbiamo esaminato solo un esempio di base dell'utilizzo del metodo Find. Tuttavia, ci sono una serie di parametri opzionali disponibili per aiutarti a perfezionare la tua ricerca
| Parametro | Tipo | Descrizione | Valori |
| Che cosa | Necessario | Il valore da cercare | Qualsiasi tipo di dati come una stringa o un numero |
| Dopo | Opzionale | Riferimento a cella singola per iniziare la ricerca | Indirizzo cellulare |
| Guarda dentro | Opzionale | Usa formule, valori, commenti per la ricerca | xlValues, xlFormulas, xlComments |
| Guarda a | Opzionale | Abbina parte o tutta una cella | xlIntero, xlParte |
| Ordine di ricerca | Opzionale | L'ordine in cui cercare - righe o colonne | xlByRows, xlByColummns |
| Direzione di ricerca | Opzionale | Direzione di ricerca per andare in avanti o indietro | xlSuccessivo, xlPrecedente |
| MatchCase | Opzionale | La ricerca fa distinzione tra maiuscole e minuscole | Vero o falso |
| MatchByte | Opzionale | Utilizzato solo se è stato installato il supporto per la lingua a doppio byte, ad es. lingua cinese | Vero o falso |
| Formato di ricerca | Opzionale | Consenti la ricerca per formato di cella | Vero o falso |
Dopo il parametro e trova più valori
tu usi il Dopo il parametro per specificare la cella iniziale per la ricerca. Questo è utile quando è presente più di un'istanza del valore che stai cercando.
Se una ricerca ha già trovato un valore e sai che verranno trovati più valori, allora usi il metodo Trova con il parametro "Dopo" per registrare la prima istanza e poi usa quella cella come punto di partenza per la ricerca successiva.
Puoi usarlo per trovare più istanze del testo di ricerca:
| 123456789101112131415161718192021222324252627282930313233343536 | Sub TestMultipleFinds()Dim MyRange As Range, OldRange As Range, FindStr As String'Cerca la prima istanza di "‘Luce e calore"Imposta MyRange = Sheets("Sheet1").UsedRange.Find("Light & Heat")'Se non lo trovi, esciSe MyRange non è nulla, esci da Sub'Visualizza il primo indirizzo trovatoMsgBox MyRange.Address'Fai una copia dell'oggetto intervalloImposta OldRange = MyRange'Aggiungi l'indirizzo alla stringa delimitata da un "|" carattereTrovaStr = TrovaStr & "|" & MyRange.Address'Esplora l'intervallo alla ricerca di altre istanzeFare"Cerca "Luce e calore" utilizzando l'indirizzo trovato in precedenza come parametro DopoImposta MyRange = Sheets("Sheet1").UsedRange.Find("Light & Heat", After:=Range(OldRange.Address))'Se l'indirizzo è già stato trovato, esci dal ciclo do - questo interrompe il ciclo continuoIf InStr(FindStr, MyRange.Address) Then Exit Do'Visualizza l'ultimo indirizzo trovatoMsgBox MyRange.Address'Aggiungi l'ultimo indirizzo alla stringa di indirizziTrovaStr = TrovaStr & "|" & MyRange.Address'fai una copia dell'intervallo correnteImposta OldRange = MyRangeCiclo continuoFine sottotitolo |
Questo codice scorrerà l'intervallo utilizzato e visualizzerà l'indirizzo ogni volta che trova un'istanza di "Luce e calore"
Si noti che il codice continuerà a eseguire il ciclo finché non viene trovato un indirizzo duplicato in FindStr, nel qual caso uscirà dal ciclo Do.
Parametro LookIn
Puoi usare il Parametro LookIn per specificare in quale componente della cella si desidera eseguire la ricerca. È possibile specificare valori, formule o commenti in una cella.
- xlValues - Cerca i valori delle celle (il valore finale di una cella dopo il suo calcolo)
- xlFormule - Ricerche all'interno della stessa formula della cella (qualunque cosa venga inserita nella cella)
- xlCommenti - Ricerche all'interno delle note della cella
- xlCommentsThreaded - Ricerche all'interno dei commenti delle celle
Supponendo che sia stata inserita una formula nel foglio di lavoro, è possibile utilizzare questo codice di esempio per trovare la prima posizione di qualsiasi formula:
| 12345678910 | SottoTestLookIn()Dim MyRange As RangeImposta MyRange = Sheets("Sheet1").UsedRange.Find("=", LookIn:=xlFormulas)Se non MyRange è niente alloraMsgBox MyRange.AddressAltroMsgBox "Non trovato"Finisci seFine sottotitolo |
Se il parametro "LookIn" è stato impostato su xlValues, il codice visualizzerà un messaggio "Non trovato". In questo esempio restituirà B10.
Utilizzo del parametro LookAt
Il Parametro LookAt determina se find cercherà una corrispondenza esatta di cella o cercherà qualsiasi cella contenente il valore di ricerca.
- xlIntero - Richiede che l'intera cella corrisponda al valore di ricerca
- xlPart - Cerca all'interno di una cella la stringa di ricerca
Questo esempio di codice individuerà la prima cella contenente il testo "luce". Insieme a Guarda:=xlPart, restituirà una corrispondenza per "Light & Heat".
| 123456789 | Sub TestLookAt()Dim MyRange As RangeSet MyRange = Sheets("Sheet1").UsedRange.Find("light", Lookat:=xlPart)Se non MyRange è niente alloraMsgBox MyRange.AddressAltroMsgBox "Non trovato"Finisci seFine sottotitolo |
Se xlIntero è stato impostato, una corrispondenza sarebbe restituita solo se il valore della cella era "leggero".
Parametro ordine di ricerca
Il Parametro Ordine di ricerca determina come verrà eseguita la ricerca in tutto l'intervallo.
- xlRighe - La ricerca viene eseguita riga per riga
- xlXolumns - La ricerca viene eseguita colonna per colonna
| 123456789 | Sub TestSearch Order()Dim MyRange As RangeImposta MyRange = Sheets("Sheet1").UsedRange.Find("employee", SearchOrder:=xlColumns)Se non MyRange è niente alloraMsgBox MyRange.AddressAltroMsgBox "Non trovato"Finisci seFine sottotitolo |
Questo influenza quale corrispondenza verrà trovata per prima.
Utilizzando i dati del test inseriti in precedenza nel foglio di lavoro, quando l'ordine di ricerca è colonne, la cella individuata è A5. Quando il parametro dell'ordine di ricerca viene modificato in xlRows, la cella individuata è C4
Questo è importante se hai valori duplicati all'interno dell'intervallo di ricerca e vuoi trovare la prima istanza sotto un particolare nome di colonna.
Parametro direzione di ricerca
Il Parametro SearchDirection determina la direzione in cui andrà la ricerca - effettivamente avanti o indietro.
- xlSuccessivo - Cerca il prossimo valore corrispondente nell'intervallo
- xlPrecedente - Cerca il valore corrispondente precedente nell'intervallo
Anche in questo caso, se ci sono valori duplicati all'interno dell'intervallo di ricerca, può avere un effetto su quale viene trovato per primo.
| 12345678910 | Sub TestSearchDirection()Dim MyRange As RangeImposta MyRange = Sheets("Sheet1").UsedRange.Find("heat", SearchDirection:=xlPrevious)Se non MyRange è niente alloraMsgBox MyRange.AddressAltroMsgBox "Non trovato"Finisci seFine sottotitolo |
Utilizzando questo codice sui dati di test, una direzione di ricerca di xlPrevious restituirà una posizione di C9. L'utilizzo del parametro xlNext restituirà una posizione di A4.
Il parametro Next significa che la ricerca inizierà nell'angolo in alto a sinistra dell'intervallo di ricerca e funzionerà verso il basso. Il parametro Precedente indica che la ricerca inizierà nell'angolo in basso a destra dell'intervallo di ricerca e procederà verso l'alto.
Parametro MatchByte
Il Parametro MatchBye viene utilizzato solo per le lingue che utilizzano un doppio byte per rappresentare ciascun carattere, come cinese, russo e giapponese.
Se questo parametro è impostato su "True", Trova corrisponderà solo ai caratteri a doppio byte con caratteri a doppio byte. Se il parametro è impostato su "False", un carattere a doppio byte corrisponderà a caratteri a uno o due byte.
Parametro formato di ricerca
Il Parametro SearchFormat ti consente di cercare i formati di cella corrispondenti. Potrebbe trattarsi di un particolare carattere utilizzato, di un carattere in grassetto o di un colore del testo. Prima di utilizzare questo parametro, è necessario impostare il formato richiesto per la ricerca utilizzando la proprietà Application.FindFormat.
Ecco un esempio di come usarlo:
| 12345678910111213 | Sub TestSearchFormat()Dim MyRange As RangeApplicazione.FindFormat.ClearApplication.FindFormat.Font.Bold = TrueImposta MyRange = Sheets("Sheet1").UsedRange.Find("heat", Searchformat:=True)Se non MyRange è niente alloraMsgBox MyRange.AddressAltroMsgBox "Non trovato"Finisci seApplicazione.FindFormat.ClearFine sottotitolo |
In questo esempio, il TrovaFormato la proprietà è impostata per cercare un carattere in grassetto. L'istruzione Trova quindi cerca la parola "calore" impostando il parametro SearchFormat su True in modo che restituisca solo un'istanza di quel testo se il carattere è in grassetto.
Nei dati del foglio di lavoro di esempio mostrati in precedenza, questo restituirà A9, che è l'unica cella contenente la parola "calore" in grassetto.
Assicurati che la proprietà FindFormat sia deselezionata alla fine del codice. Se non lo fai, la tua prossima ricerca terrà comunque conto di questo e restituirà risultati errati.
Quando utilizzi un parametro SearchFormat, puoi anche utilizzare un carattere jolly (*) come valore di ricerca. In questo caso cercherà qualsiasi valore con un carattere in grassetto:
| 1 | Imposta MyRange = Sheets("Sheet1").UsedRange.Find("*", Searchformat:=True) |
Utilizzo di più parametri
Tutti i parametri di ricerca discussi qui possono essere utilizzati in combinazione tra loro se necessario.
Ad esempio, puoi combinare il parametro "LookIn" con il parametro "MatchCase" in modo da guardare l'intero testo della cella, ma fa distinzione tra maiuscole e minuscole
| 123456789 | Sub TestMultipleParameters()Dim MyRange As RangeSet MyRange = Sheets("Sheet1").UsedRange.Find("Light & Heat", LookAt:=xlWhole, MatchCase:=True)Se non MyRange è niente alloraMsgBox MyRange.AddressAltroMsgBox "Non trovato"Finisci seFine sottotitolo |
In questo esempio, il codice restituirà A4, ma se abbiamo usato solo una parte del testo, ad es. 'calore', non verrebbe trovato nulla perché stiamo abbinando l'intero valore della cella. Inoltre, fallirebbe a causa della mancata corrispondenza del caso.
| 1 | Imposta MyRange = Sheets("Sheet1").UsedRange.Find("heat", LookAt:=xlWhole, MatchCase:=True) |
Sostituisci in Excel VBA
C'è, come ci si potrebbe aspettare, una funzione Sostituisci in Excel VBA, che funziona in modo molto simile a "Trova" ma sostituisce i valori nella posizione della cella trovata con un nuovo valore.
Questi sono i parametri che è possibile utilizzare in un'istruzione del metodo Replace. Questi funzionano esattamente allo stesso modo dell'istruzione del metodo Find. L'unica differenza rispetto a "Trova" è che è necessario specificare un parametro di sostituzione.
| Nome | Tipo | Descrizione | Valori |
| Che cosa | Necessario | Il valore da cercare | Qualsiasi tipo di dati come una stringa o un numero |
| Sostituzione | Necessario | La stringa di sostituzione. | Qualsiasi tipo di dati come una stringa o un numero |
| Guarda a | Opzionale | Abbina parte o tutta una cella | xlPart o xlWhole |
| Ordine di ricerca | Opzionale | L'ordine di ricerca in - Righe o Colonne | xlByRows o xlByColumns |
| MatchCase | Opzionale | La ricerca fa distinzione tra maiuscole e minuscole | Vero o falso |
| MatchByte | Opzionale | Utilizzato solo se è stato installato il supporto per la lingua a doppio byte | Vero o falso |
| Formato di ricerca | Opzionale | Consenti la ricerca per formato di cella | Vero o falso |
| SostituisciFormato | Opzionale | Il formato di sostituzione per il metodo. | Vero o falso |
Il parametro Sostituisci formato cerca una cella con un formato particolare, ad es. grassetto nello stesso modo in cui il parametro SearchFormat opera nel metodo Find. È necessario impostare prima la proprietà Application.FindFormat, come mostrato nel codice di esempio Trova mostrato in precedenza
Sostituisci senza parametri opzionali
Nella sua forma più semplice, devi solo specificare cosa stai cercando e con cosa vuoi sostituirlo.
| 123 | SottoprovaSostituisci()Fogli ("Sheet1").UsedRange.Replace What:="Light & Heat", Replacement:="L & H"Fine sottotitolo |
Si noti che il metodo Find restituirà solo la prima istanza del valore corrispondente, mentre il metodo Replace funziona sull'intero intervallo specificato e sostituisce tutto ciò su cui trova una corrispondenza.
Il codice di sostituzione mostrato qui sostituirà ogni istanza di "Light & Heat" con "L & H" attraverso l'intero intervallo di celle definito dall'oggetto UsedRange
Utilizzo di VBA per trovare o sostituire il testo all'interno di una stringa di testo VBA
Gli esempi sopra funzionano alla grande quando si utilizza VBA per interagire con i dati di Excel. Tuttavia, per interagire con le stringhe VBA, puoi utilizzare funzioni VBA integrate come INSTR e REPLACE.
Puoi usare il Funzione INSTR per individuare una stringa di testo all'interno di una stringa più lunga.
| 123 | Sub TestInstr()MsgBox InStr("Questa è la stringa MyText", "MyText")Fine sottotitolo |
Questo codice di esempio restituirà il valore 9, che è la posizione del numero in cui si trova "MyText" nella stringa da cercare.
Nota che fa distinzione tra maiuscole e minuscole. Se 'MyText' è tutto minuscolo, verrà restituito il valore 0, il che significa che la stringa di ricerca non è stata trovata. Di seguito discuteremo come disabilitare la distinzione tra maiuscole e minuscole.
INSTR - Start
Sono disponibili altri due parametri opzionali. È possibile specificare il punto di partenza per la ricerca:
| 1 | MsgBox InStr(9, "Questa è stringa MyText", "MyText") |
Il punto di partenza è specificato come 9, quindi restituirà ancora 9. Se il punto di partenza fosse 10, restituirebbe 0 (nessuna corrispondenza) poiché il punto di partenza sarebbe troppo avanti.
INSTR - Maiuscole/minuscole
Puoi anche impostare un parametro di confronto su vbBinaryCompare o vbTextCompare. Se si imposta questo parametro, l'istruzione deve avere un valore di parametro iniziale.
- vbBinaryCompare - Maiuscole/minuscole (predefinito)
- vbTextCompare - Non sensibile a maiuscole e minuscole
| 1 | MsgBox InStr(1, "Questo è MyText string", "mytext", vbTextCompare) |
Questa istruzione restituirà comunque 9, anche se il testo di ricerca è in minuscolo.
Per disabilitare la distinzione tra maiuscole e minuscole puoi anche dichiarare Option Compare Text nella parte superiore del modulo di codice.
Funzione di sostituzione VBA
Se desideri sostituire i caratteri in una stringa con un testo diverso all'interno del tuo codice, il metodo Replace è l'ideale per questo:
| 123 | SottoprovaSostituisci()MsgBox Replace("Questo è MyText string", "MyText", "My Text")Fine sottotitolo |
Questo codice sostituisce "MyText" con "My Text". Si noti che la stringa di ricerca fa distinzione tra maiuscole e minuscole poiché un confronto binario è l'impostazione predefinita.
Puoi anche aggiungere altri parametri opzionali:
- Cominciare - definisce la posizione nella stringa iniziale da cui deve partire la sostituzione. A differenza del metodo Find, restituisce una stringa troncata a partire dal numero di caratteri definito dal parametro Start.
- Contare - definisce il numero di sostituzioni da effettuare. Per impostazione predefinita, Sostituisci cambierà ogni istanza del testo di ricerca trovato, ma puoi limitarlo a una singola sostituzione impostando il parametro Conteggio su 1
- Confrontare - come nel metodo Trova puoi specificare una ricerca binaria o una ricerca testuale usando vbBinaryCompare o vbTextCompare. Binary fa distinzione tra maiuscole e minuscole e il testo non fa distinzione tra maiuscole e minuscole
| 1 | MsgBox Replace("Questo è MyText string (mytext)", "MyText", "My Text", 9, 1, vbTextCompare) |
Questo codice restituisce "My Text string (mytext)". Questo perché il punto di inizio dato è 9, quindi la nuova stringa restituita inizia con il carattere 9. Solo il primo "MyText" è stato modificato perché il parametro Count è impostato su 1.
Il metodo Replace è ideale per risolvere problemi come nomi di persone contenenti apostrofi, ad es. O'Flynn. Se stai usando le virgolette singole per definire un valore di stringa e c'è un apostrofo, questo causerà un errore perché il codice interpreterà l'apostrofo come la fine della stringa e non riconoscerà il resto della stringa.
Puoi usare il metodo Replace per sostituire l'apostrofo con niente, rimuovendolo completamente.
