Usare la ricerca dati su VBA Excel (metodo Find)

Ultimo aggiornamento 30 dicembre 2021 alle 13:55 da Claudia Scarciolla .

La funzione di ricerca dati, metodo Find, è una delle funzioni più comode usate su VBA. In questa guida cosa serve e come trovare un valore in una colonna Excel con la funzione Trova di VBA.

Metodo Find di Excel: cos'è e a cosa serve?
Cercare valori in una colonna Excel
- Ricercare valori con il metodo Find di VBA
Il metodo Find di VBA
Valori rinviati del metodo Find
Esempi del metodo Find su VBA
Ricerche multiple su VBA
- Usare la variante FindNext
- FindAll

Metodo Find di Excel: cos'è e a cosa serve?

Questo metodo consente agli utenti di individuare in prima occorrenza un’informazione di un intervallo e si può usare per cercare in un foglio o in una parte del foglio di calcolo Microsoft Excel.

Cercare valori in una colonna Excel

VBA Trova valore (Find) è una funziona simile a quella base di ricerca. Se sta cercando di eseguire una sola ricerca di tutti i dati presenti nel foglio di lavoro, si può cliccare la scorciatoia di tastiera CTRL + F per poter aprire una casella di ricerca. Qui, basterà digitare la parola chiave o il valore che si vuole trovare e cliccare Invio. Così facendo Excel evidenzierà tutte le celle che corrispondono alla propria ricerca.

Ricercare valori con il metodo Find di VBA

Ecco come appare la funzione Find, Trova valore di VBA. Da notare il termine di ricerca utilizzato per questo esempio è Valore:

Cells.Find(What:="Value", After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, 
SearchFormat:=False).Activate

Il metodo Find di VBA

Ecco un rapido elenco di tutti i parametri utilizzabili dal metodo Find:

MyRange.Find(What, After, LookIn, LookAt, SearchOrder, SearchDirection, MatchCase, SearchFormat)

MyRange: questa espressione rappresenta l’oggetto dell’intervallo, che designa in quali celle sei vuole effettuare la ricerca del valore contenuta nel parametro What. Questo essere l’intero foglio (Sheets(1).Cells.Find(...), una colonna (Sheets(1).Columns(3).Find(...)), una riga (Sheets(1).Rows(7).Find(....)), o un intervallo di celle (Sheets(1).Range("D12:F56").Find(....)).

What: questo parametro è richiesto e permette di determinare quale valore si vuole cercare nel proprio intervallo. Il parametro può essere costituito da un qualunque tipo di dati supportati da Excel.
After: questo parametro è opzionale e indica la cella da cui iniziare la ricerca (da notare che quest’ultima deve essere unica). Se After non è specificato, la ricerca inizierà dall’intervallo situato nell’angolo in alto a sinistra.
LookIn: anch’esso opzionale, si tratta di un parametro usato per collocare un valore in un intervallo. Altre variazioni di LookIn includono: xlValues, xlFormulas, e xlComments.
LooAt: parametro opzionale indica se il valore deve o non, essere esattamente o in parte uguale al valore cercato.

Per esempio, se si cerca il valore "10" in una matrice che include 6210, 4105, 540, 163, 154, 132, ci sono vari metodi che possono essere usati. Per comunicare a VBA che si vuole solo il numero 10, si può usare il parametro LookAt:=XlWhole. Mentre al contrario, per indicare di cercare un valore che contiene il numero 10 (in questo caso specifico, 6210 o 4105), si può usare LookAt:=XlPart.

SearchOrder è un parametro opzionale, l’ordine di ricerca è soggetto a due costanti: xlByRows (per le righe) o xlByColumns (per le colonne).
SearchDirection: opzionale è un parametro che indica la direzione della ricerca in un particolare intervallo. Le due costanti sono xlNext, che p usata per cercare valori consecutivi nell’intervallo, e xlPrevious che è usato per cercare un valore avuto in precedenza.
MatchCase: sempre opzionale, include due valori per questo argomento, ossia True e False. Le ricerche case-sensitive (sensibili alle maiuscole) devono essere impostate su True.
SearchFormat: parametro opzionale che può essere impostato su True o False a seconda del fatto che venga o meno assegnato un formato (ad esempio, standard monetario, numero, confine, riempimento, allineamento, ecc.).

Valori rinviati del metodo Find

Il metodo Find restituirà l’oggetto Range che rappresenta la cella in cui, per la prima volta, si è verificato un valore. Questo metodo fa risultare Nothing (niente) se non trova il match giusto. I parametri degli argomenti LookIn, LookAt, SearchOrder e MatchCase sono registrati ogni volta che si usa questo metodo, tanto che se non si specificano i valori per questi argomenti, riutilizzando il metodo Find, i valori precedenti verranno utilizzati in automatico.

Esempi del metodo Find su VBA

Opzione Esplicita

Sub Ricerca()
'dichiarazione delle variabili’:
Dim Trova As Range, Intervallodiricerca As Range
Dim Valore_Ricerca As String, IndirizzoTrovato As String

'********* da adattare ***********
'assegnazione dei valori alle variabili:
    'si cerca la parola "Trova"
Valore_Cercato = " Trova "
    'nella prima pagina del foglio attivo
Set Intervallodiricerca = ActiveSheet.Columns(1)
'*******************************

'metodo find, qui si cerca il valore esatto (LookAt:=xlWhole)
Set Trova = Intervallodiricerca.Cells.Find(what:= Valore_Cercato, LookAt:=xlWhole)

‘trattamento dell’errore possibile: se non si trova nulla
If Trouve Is Nothing Then
    ‘qui, cosa fare nel caso in cui il valore non è trovato
    IndirizzoTrovato = Valore_Cercato & "non è presente in" & Intervallodiricerca.Address
Else
    'qui, trattamento per il caso in cui il valor è trovato
        IndirizzoTrovato = Trova.Indirizzo
End If
MsgBox IndirizzoTrovato
‘pulizia delle variabili
Set Intervallodiricerca = Nothing
Set Trova = Nothing
End Sub

Nota Bene: se si è fatta una ricerca del numero 1024 al posto della parola ‘Trova’, avremmo dovuto dichiarare Valore_Cercato come numero intero. La variante What permette di cercare ogni tipo di data.

Ricerche multiple su VBA

I metodi FindNext e FindPrevious consentono di effettuare ricerche multiple.

Usare la variante FindNext

Nell’esempio che segue cercheremo l'espressione ‘parola’ nell’intervallo A1:A20:

Sub Principale()
Dim Intervallo As Range
Dim Linee(), i As Long
Dim Testo As String
Dim Bandiera As Boolean
Set Intervallo = Sheets("Feuil1").Range("A1:A20") 'intervallo di ricerca
Texte = "parola"   'espressione ricercata
Flag = Find_Next(Plage, Texte, Linee())  ‘richiamo della funzione
If Bandiera Then  'se la funzione ritorna Vero = espressione trovata nell’intervallo
    For i = LBound(Linee) To UBound(Linee)   ‘restituzione delle linee corrispondenti
        Debug.Print Lignes(i)
    Next i
Else
    MsgBox "L'espressione : " & Texte & " non è stato trovato nell’intervallo : " & Intervallo.Address
End If
End Sub
'Fonte : Michel_m
'http://www.commentcamarche.net/forum/affich-31432413-importation-de-donnees-sans-doublons#9
Function Find_Next(Rng As Range, Texte As String, Tbl()) As Boolean
Dim Nbre As Integer, Lig As Long, Cptr As Long, Adresse As String
Nbre = Application.CountIf(Rng, Texte)
    If Nbre > 0 Then
        ReDim Tbl(Nbre - 1)
        Lig = 1
        For Cptr = 0 To Nbre - 1
            Lig = Rng.Find(Texte, Cells(Lig, Rng.Column), xlValues).Row
            Tbl(Cptr) = Lig
        Next
    Else
        GoTo Absent
    End If
    Find_Next = True
    Exit Function
Absent:
    Find_Next = False
End Function

Nota Bene: per l’intera colonna, sostituire

Set Intervallo = Sheets("Foglio1").Range("A1:A20")

Con:

Set Plage = Sheets("Foglio1").Colonne(1)

Attenzione per reindirizzare le celle anziché il numero di riga in Find_Next function, sostituire:

Lig = Rng.Find(Texte, Cells(Lig, Rng.Column), xlValues).Row
Tbl(Cptr) = Lig

Con:

Lig = Rng.Find(Texte, Cells(Lig, Rng.Column), xlValues).Row
Adresse = Cells(Lig, Rng.Column).Address
Tbl(Cptr) = Adresse

FindAll

Questa funzione personalizzata ritorna a Find FindNext dando come risultato un array di valori. Questo cercherà tutte le istanze di una stringa (sText As String) e darà come risultato un array contenente i numeri di riga. I parametri di questa funzione sono i seguenti:

ByVal sText As String rappresenta il valore target;

ByRef oSht As Worksheet rappresenta il foglio dell’obiettivo, target;

ByRef sRange As String rappresenta l’intervallo;

ByRef arMatches() As String rappresenta l’ array che memorizzerà i valori restituiti.

Il codice:

Function FindAll(ByVal sText As String, ByRef oSht As Worksheet, ByRef sRange As String, ByRef arMatches() As String) As Boolean
' --------------------------------------------------------------------------------------------------------------
' FindAll - To find all instances of the1 given string and return the row numbers.
' If there are not any matches the function will return false
' --------------------------------------------------------------------------------------------------------------
On Error GoTo Err_Trap
Dim rFnd As Range ' Range Object
Dim iArr As Integer ' Counter for Array
Dim rFirstAddress ' Address of the First Find
' -----------------
' Clear the Array
' -----------------
Erase arMatches
Set rFnd = oSht.Range(sRange).Find(what:=sText, LookIn:=xlValues, lookAt:=xlPart)

If Not rFnd Is Nothing Then
rFirstAddress = rFnd.Address
Do Until rFnd Is Nothing
iArr = iArr + 1
ReDim Preserve arMatches(iArr)
arMatches(iArr) = rFnd.Row 'rFnd.Address pour adresse complete ' rFnd.Row Pour N° de ligne
Set rFnd = oSht.Range(sRange).FindNext(rFnd)
If rFnd.Address = rFirstAddress Then Exit Do ' Do not allow wrapped search
Loop
FindAll = True
Else
' ----------------------
' No Value is Found
' ----------------------
FindAll = False
End If
' -----------------------
' Error Handling
' -----------------------
Err_Trap:
If Err <> 0 Then
MsgBox Err.Number & " " & Err.Description, vbInformation, "Find All"
Err.Clear
FindAll = False
Exit Function
End If
End Function