Usare l'Operatore LIKE     (19/07/03)

Sappiamo tutti, almeno spero, cosa sono gli "Operatori di confronto", eventualmente date un occhiata all'articolo "operatori numerici" nella sezione "primi passi". Comunque servono a confrontare due valori (testi, numeri o date). In genere i confronti si fanno per valutare la corrispondenza esatta tra due o più valori, oppure se un valore può essere maggiore o minore rispetto ad un altro; ma quando abbiamo bisogno di confrontare "approssimativamente" due valori, gli operatori normalmente usati non bastano più. Un esempio potrebbe essere quello di cercare, in un elenco, tutti valori la cui lettera iniziale corrisponde ad "a". Sappiamo che una parola come "atlante", che pure comincia con "a",  non verrebbe trovata in quanto in quanto i metodi di ricerca si basano su un valore preciso fornito come chiave di ricerca, e la stringa "a" ovviamente è diversa dalla stringa "atlante". In questi casi ci viene in aiuto l'operatore LIKE, che vuol dire appunto "simile a...", "come" o " che "assomiglia a..". Perchè Like ci possa aiutare in una ricerca, va a sua volta aiutato, (indicando di seguito ad un valore chiave per esempio la lettera "c"), un carattere speciale definito sintatticamente "criterio", e che può essere rappresentato da uno dei seguenti caratteri Jolly :

  • ?  -  (punto di domanda) che fa corrispondere qualsiasi carattere singolo.

  • *  -  (asterisco) che fa corrispondere zero o più caratteri.

  • #  -  (cancelletto) che fa corrispondere qualsiasi cifra singola (0-9).

I caratteri Jolly ci consentono quindi di sostituirsi ad elenchi di caratteri o intervalli di caratteri in qualsiasi combinazione. Un'istruzione quindi che ci consentirebbe di trovare "atlante" digitando solo la lettera "a", diventerebbe:

If CL.Value Like "a*" Then

dove l'asterisco posto dopo la lettera "a" consentirebbe di riconoscere "a tlante" perchè l'asterisco sostituisce l'intervallo di caratteri "tlante". In parole povere, l'istruzione sopra dice:

se il valore della variabile CL comincia con "a" con tutto quello che ci sia dopo, Then ecc. ecc.

Ma come e dove usare Like ? Essenzialmente per eseguire ricerche di dati, di cui si voglia inserire una sola lettera iniziale, o un numero come chiave di ricerca, e si vogliano trovare tutti i valori che iniziano con quella lettera (o gruppo di lettere), e quel numero (o gruppi di numeri). Avremo quindi che con la stringa di ricerca

  • c*  - verranno trovati tutti i valori che iniziano per  "c" : catullo, circo, caramba, cucurnia, ceffo, ecc.

  • ca* - verranno trovati tutti i valori che iniziano per  "ca" : catullo,  caramba

  • car* - verranno trovati tutti i valori che iniziano per "car" :  caramba

lo stesso dicasi per i numeri, o per le date (più avanti troverete una precisazione a questo proposito)

  • 1*  - verranno trovati tutti i valori che iniziano per  "1" : 10, 125, 1543, 10/07/03 ecc.

  • 10* - verranno trovati tutti i valori che iniziano per  "10" : 10, 10/07/03

  • 154* -verranno trovati tutti i valori che iniziano per  "154" : 1543

Abbiamo quindi imparato un metodo flessibile che ci consente un'ampia possibilità di variare le ricerche. Vediamo ora qualche esempio di applicazione, con relative istruzioni e commenti (in verde). Iniziamo con chiarire come impostare la chiave di ricerca:

  • inserendo la chiave di ricerca direttamente nelle istruzioni: es. Like "a*" ; sistema poco flessibile perchè ci costringerebbe alla modifica dell'istruzione nel codice se volessimo trovare altri valori che inizino con altre lettere.

  • usare una cella del foglio di lavoro, nella quale di volta in volta scrivere il valore che serva come chiave di ricerca; es. usando la cella C1, la nostra istruzione diventerebbe Like Range("C1") & "*". Come si nota usiamo un concatenatore di stringa (la &) per restituire, uniti, il valore che scriveremo in C1 seguito dall'asterisco (carattere Jolly)

  • usare una textbox se usiamo una userform, nella quale scriveremo il valore chiave di ricerca (come sopra) e in questo caso l'istruzione sarà : Like TextBox1 & "*"

  • usare una InputBox. in questo caso dovremo assegnare ad una variabile il valore restituito dalla InputBox, e richiamare questa variabile+asterisco dopo Like.  Sarà sufficiente compilare:

  • Myvar = InputBox("Inserisci il valore da cercare")


    • ......Like CStr(Myvar) & "*"

Ora passiamo a vedere in quale contesto usare queste istruzioni. Molte sono le situazioni in cui si può presentare la necessità di svolgere una ricerca, qui sceglieremo l'esempio di cercare tutti i valori la cui iniziale corrisponderà ad una determinata lettera, e con i dati così trovati, riempire una ListBox. Sotto vediamo l'immagine dell'esercizio: la colonna A è la zona dove risiedono i dati, la cella C1 è la cella che usiamo come vettore per l'iniziale che serve a trovare nella colonna A tutti i valori che cominciano con una lettera, in questo caso la lettera "c", e la ListBox1 con i nomi trovati

  e questa la routine usata:

Sub carica1()
Dim CL As Object
ActiveSheet.ListBox1.Clear 'predisponiamo la pulizia della listbox per la nuova ricerca
For Each CL In Range("A1:A20") 'inizia il ciclo di ricerca: per ogni CL (cella presente 'nel Range A1:A20 (tanto per fare l'esempio mi fermo alla riga 20)
'sotto: se il valore che presente nella cella in quel momento scorsa, è uguale al valore che 'è in C1*, cioè basta che inizi con "c" (in questo caso), allora
If CL.Value Like Range("C1") & "*" Then

'carichiamo con AddItem quel valore nella ListBox1
ActiveSheet.ListBox1.AddItem CL.Value
End If
Next
End Sub

Basterà quindi variare la lettera in C1 per ottenere, se presenti nell'elenco, i nomi che iniziano per quella lettera, nella ListBox. Un'altro aspetto interessante di questo metodo di estrazione dati, perchè di questo si tratta, è che se nell'elenco dati esistono celle vuote, queste non figureranno mai nella ListBox.

Qualcuno potrebbe obiettare: ma come fare per ottenere i nomi trovati, messi in ordine alfabetico nella ListBox? E ancora: come fare perchè eventuali dati doppi vengano riportati una sola volta? A quest'ultima domanda rispondo: leggete l'articolo "ComboBox . ordinare la lista", dove si parla della routine per l'eliminazione dei doppioni. Alla prima domanda rispondo.

  • applicare un'ordinamento all'elenco a cui si attingono i dati da ricercare: i dati trovati saranno già in ordine alfabetico.

  • creare in una colonna fuori vista, un riepilogo dei dati estratti, applicare a questo secondo elenco un'ordinamento ascendente, reperire quindi il riferimento alla cella iniziale e con End il riferimento alla cella finale, e fornire questi riferimenti alla proprietà ListFillRange della ListBox (anzichè usare AddItem) in modo che la ListBox ci mostri l'elenco in ordine alfabetico.

Vediamo un'immagine basata su quest'ultima ipotesi, dove, per comodità visive, il secondo elenco lo ospito nella colonna D, a partire dalla cella D4. A questo elenco è stato già applicato l'ordinamento, a destra la Listbox caricata usando la sua proprietà ListFillRange :

E questa la routine, le istruzioni sono un pò più complesse rispetto alla routine carica1, ma viene svolto un lavoro diverso :

Sub carica2()

'iniziamo pulendo la ListBox1 predisponendo alla nuova ricerca, e NON usiamo Clear, 'ma impostiamo il ListFillRange a vuoto ("")
ActiveSheet.ListBox1.ListFillRange = ""
Dim CL As Object  'dichiariamo CL come "oggetto" (usato nel ciclo For Each..Next)
Dim iRow As Integer  'dichiariamo la variabile iRow come Integer (è la variabile con la 'quale impostiamo da quale riga iniziare il riepilogo dei dati trovati (il secondo elenco)
iRow = 3  'rendiamo iRow uguale a 3, cioè il riepilogo inizierà dalla riga 3
Range("D:D").ClearContents  'Puliamo tutta la colonna destinata al riepilogo, e la puliamo 'tutta in quanto non sappiamo quanto lungo era il riepilogo precedente.

'sotto: ATTENZIONE : istruzione necessaria per ripristinare tutta la colonna destinata al 'riepilogo, impostando il formato celle a "Generale". Operazione necessaria nel caso in cui 'si cerchino occasionalmente anche valori tipo data (es. 10/07/03), quando il dato 'cercato viene trovato, se è una data, Excel nel copiare la data nel riepilogo, modifica 'anche il formato della cella che ospiterà la data a formato cella data. Questa 'impostazione rimane anche per le nuove ricerche, e se nella nuova ricerca venissero 'cercati numeri anzichè date, il numero che finisse dove prima era stata ospitata una data, 'sarebbe errato e inservibile.
Range("D:D").NumberFormat = "General"

'sotto: si imposta il valore della cella, ora riga 3, 4 colonna, cioè la D, a "riepilogo" come 'se fosse un'intestazione di campo
Cells(iRow, 4).Value = "riepilogo"

'sotto: si inizia il ciclo di ricerca nel Range A1:A20, ognuno metterà i suoi riferimenti
For Each CL In Range("A1:A20")
'sotto: se il valore che è  presente nella cella in quel momento scorsa, è uguale al valore 'che è in C1*, cioè basta che inizi con "a" (in questo caso), allora
If CL.Value Like Range("C1") & "*" Then

'inizia il ciclo interno per cercare una cella libera, colonna D (4) dove scrivere il valore 'trovato sopra (CL.Value)
Do While Cells(iRow, 4).Value <> "" 'fino a che la cella è occupata
iRow = iRow + 1 'si passa a controllare la cella sottostante (+1) e si continua con Loop
Cells(iRow, 4) = CL.Value 'quando si verifica che una cella è vuota, gli si passa il valore 'rappresentato da Cl.Value
Exit Do 'e si esce dal ciclo Do While..Loop
Loop 'altrimenti si continua a cercare una cella vuota ritornando a Do While
End If
Next ' con Next si scorrono, una ad una le celle del Range A1:A20

'a questo punto, finita la ricerca e caricato il "riepilogo", abbiamo bisogno di reperire gli 'Address, cioè gli indirizzi delle celle che delimitano il "riepilogo"
lista = Cells(4, 4).Address 'con "lista" prendiamo l'indirizzo della cella riga 4, colonna 4 '(D4)
nera = Cells(4, 4).End(xlDown).Address 'con "nera" reperiamo l'ultima cella occupata 'della colonna D.

'sotto: applichiamo l'ordinamento ai range i cui estremi sono stati trovati con le due 'variabili
Range(lista & ":" & nera).Sort Key1:=Range("D4"), Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

'e alla fine carichiamo la ListFillRange della ListBox1 con l'assegnazione dei range di 'pertinenza
ActiveSheet.ListBox1.ListFillRange = (lista & ":" & nera)

End Sub

Sotto riporto, senza spiegazioni (sono uguali), la routine che si avvale di una InputBox per reperire il valore da cercare:

Sub caricainput()
ActiveSheet.ListBox1.ListFillRange = ""
Dim CL As Object
Dim iRow As Integer
iRow = 3
cosa = InputBox("Scrivi cosa cerchi") 'con "cosa" prendiamo il valore da cercare
Range("D:D").ClearContents
Range("D:D").NumberFormat = "General"
Cells(iRow, 4).Value = "riepilogo"
For Each CL In Range("A1:A20")

If CL.Value Like CStr(cosa) & "*" Then 'e usiamo la variabile stringa "cosa" al posto 'dell'indicazione di una cella
Do While Cells(iRow, 4).Value <> ""
iRow = iRow + 1
Cells(iRow, 4) = CL.Value
Exit Do
Loop
End If
Next
lista = Cells(4, 4).Address
nera = Cells(4, 4).End(xlDown).Address
Range(lista & ":" & nera).Sort Key1:=Range("D4"), Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
ActiveSheet.ListBox1.ListFillRange = (lista & ":" & nera)

End Sub

Unica precisazione: il carattere Jolly * (asterisco), può essere usato come valore iniziale da cercare, seguito da una o più lettere, e quindi ancora un asterisco. In questo modo possiamo trovare valori al cui interno compaia la lettera o le lettere digitate tra gli asterischi. Esempio: se in C1 o nella InputBox scriviamo *lo*, verranno trovati tutti i nomi al cui interno compare la coppia "lo", cioè : bellotti, cellosi, luppolo .

 

Buon lavoro.

prelevato sul sito http://ennius.interfree.it