Cerca.Vert  su qualsiasi cella - Simulazione della funzione, in vba. - pagina vista: volte

Una recente domanda è alla base di questo articolo: "disponendo di una tabella database, come poter ottenere i dati correlati ad una determinata chiave di ricerca presente nella tabella, digitando la chiave in una qualsiasi cella di un qualsiasi foglio nella stessa cartella ?". Per meglio capire, posta una tabella come questa sotto:

  A B C D E
1

p02

Milano

Lombardia

Nord

Italia

2

p06

Roma

Lazio

Centro

Italia

3

p0040

Bucarest

Bucaresti

Centro

Romania

4

p011

Torino

Piemonte

Nord

Italia

si vuole che digitando in una qualsiasi cella (ovviamente al di fuori della tabella) di un qualsiasi foglio, una chiave di ricerca, ad esempio "p06", nelle celle a lato compaiano i dati correlati a "p06", quindi Roma, Lazio, Centro, Italia.

Ora, al di là delle motivazioni personali del "pellegrino" che ha posto la domanda, è evidente che l'esercizio diventa interessante in rapporto ad altri possibili utilizzi: si pensi ad esempio ad un elenco di articoli di magazzino, elenco che comprenderà un codice articolo (univoco), alla descrizione articolo, all'unità di misura di vendita, all'aliquota iva, al prezzo unitario di vendita, ed altro, e alla necessità di ottenere tutti i dati correlati ad un codice articolo, da digitare in una cella di una griglia di emissione DDT oppure di fatturazione, in modo da ottenere automaticamente tutti i dati che interessano la fatturazione, correlati al codice digitato; ma di esempi ne potremmo riempire una pagina. E comunque il concetto è quello di poter liberamente usare una qualsiasi cella dove digitare una chiave di ricerca, ed ottenere nelle celle a lato, i dati correlati.

Si tratta in definitiva di una operazione ottenibile con la classica Funzione CERCA.VERT, solo che perchè la cosa funzioni, è necessario predisporre nelle celle interessate a ricevere i dati correlati ad un determinato dato cercato, appunto,  tante funzioni =CERCA.VERT(argomenti), in altrettante celle, per quanti saranno i dati correlati da ottenere.

Il che vorrebbe dire, per poter usare una qualsiasi cella dove scrivere la chiave di ricerca,  predisporre a caso tante funzioni CERCA.VERT() quante sono le celle di un foglio, e questo per ogni foglio; una cosa impensabile e impossibile da realizzare per una serie di ovvie motivazioni.

E' però possibile usando istruzioni vba, ottenere la risposta alla domanda posta, e senza ammattire eccessivamente. Vediamo come procedere:

  • per prima cosa dovremo impostare la nostra tabella, che converrebbe inserire su un foglio ad hoc preposto, e dove non useremo mai la nostra pseudo-funzione Cerca.Vert. lasciando quindi questo foglio al solo contenimento della tabella dati. Per quanto riguarda la Tabella, useremo la prima colonna a sinistra della tabella come contenitore dei dati da ricercare, cioè dei dati che serviranno come chiave di ricerca, alla stessa stregua di come si imposta una tabella da usarsi con la funzione CERCA.VERT().

  • altra condizione necessaria per le istruzioni che useremo, è che le chiavi di ricerca siano univoche all'interno della tabella dati; questo perchè le istruzioni cercheranno e restituiranno (come accade con la funzione CERCA.VERT) il primo valore che corrisponderà al criterio cercato.

  • Dovremo poi scegliere un'evento che ci consenta, al semplice inserimento della chiave di ricerca in una qualsiasi cella, di attivare le istruzioni che vedremo; sceglieremo quindi l'evento Change di un foglio, che si verifica dopo aver digitato o modificato un valore in una qualsiasi cella del foglio stesso, e visto che vorremo poter disporre di questo evento su qualsiasi foglio, sceglieremo l'evento Workbook_SheetChange dell'oggetto Workbook (attenzione: non WorkSheet_Change relativo ad un solo Worksheet definito) che ci consente appunto di intervenire qualunque foglio dello stesso Workbook subisca un cambiamento.

  • L'evento Workbook_SheetChange possiede l'argomento Target, che ci consente di ottenere il riferimento alla cella di un foglio, identificando quindi la cella che ha subito il cambiamento.

Fatte queste premesse, diventa facile impostare le istruzioni necessarie, vediamole: intanto sfruttiamo l'evento Workbook_SheetChange, ad inizio routine inseriamo un controllo errori, necessario per evitare di bloccare l'esecuzione del codice nel caso si selezionino più celle contemporaneamente, ad esempio per cancellare il loro contenuto; questo tipo di azione crea un errore sull'istruzione If x = "" Then Exit Sub in quanto x è riferito ad una sola cella (la cella Target). Nell'esempio ho simulato che la tabella dati sia sul Foglio 1, quindi usiamo il riconoscimento dei fogli tramite il loro nome: se saremo sul Foglio1 (lo stesso della tabella) usciremo dalla routine impedendo l'esecuzione delle istruzioni:

  • Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    On Error Resume Next
    If ActiveSheet.Name = Sheets(1).Name Then Exit Sub
    Dim CL As Object
    x = Target.Value   
     'con la variabile "x" prendiamo il valore che avremo scritto in una cella qualsiasi
    If x = "" Then Exit Sub  
    'se "x" sarà vuoto (magari cancellando il contenuto) si esce dalla routine
    For Each CL In Sheets(1).Range("A1:A10")
     'poi iniziamo un ciclo che scorra le celle della tabella sul foglio1, nella 'colonna che contiene i valori chiavi di ricerca (nell'esempio la colonna A)
    If CL.Value = x Then   
    'e controlliamo se la cella letta dal ciclo è uguale al valore "x" digitato, in caso positivo
    a = CL.Offset(0, 1)  
    ' memorizziamo nelle variabili (a,b,c,d,) i valori corrispondenti alle celle il cui scarto lo
    b = CL.Offset(0, 2) 
     ' impostiamo tramite Offset(riga, colonna) rispetto alla cella letta nel ciclo
    c = CL.Offset(0, 3)
    d = CL.Offset(0, 4)
    Target.Offset(0, 1) = a  '
    quindi rendiamo, sempre usando Offset(riga, colonna) ma scartando sulla cella (TARGET)
    Target.Offset(0, 2) = b 
    'cioè quella dove avremo digitato la chiave di ricerca, il contenuto delle variabili a,b,c,d,
    Target.Offset(0, 3) = c
    Target.Offset(0, 4) = d
    Exit For  
    ' ed usciamo dal ciclo
    End If
    Next
    Resume
    End Sub

Va da se che se in una cella qualsiasi digiteremo un dato non corrispondente ad una chiave di ricerca, attiveremo comunque le istruzioni, ma non otterremo dati correlati. Unica controindicazione: se disporremo di tabelle dati molto estese, poichè ad ogni digitazione in qualsiasi cella di qualsiasi foglio, visto che comunque causiamo un cambiamento (Change), attiviamo comunque le istruzioni, che su elenchi molto lunghi potranno richiedere anche molto tempo di esecuzione ciclo.

Possibili soluzioni saranno quelle, ad esempio, di attivare le istruzioni solo se saremo su un determinato foglio, oppure anzichè usare il Workbook_SheetChange potremo usare il WorkSheet_Change del foglio in cui ci interessa siano attive le istruzioni.

Altra considerazione è che potremo non solo "prendere" dati correlati specifici usando opportunamente lo Scarto (Offset), ma anche destinare i dati correlati in celle non contigue rispetto alla cella Target, sempre impostando il giusto Scarto (Offset).

Se invece di una chiave univoca, avremo più valori uguali nella stessa colonna dove svolgiamo la ricerca, per restare in esempio, due o più "p06", avremo bisogno di decidere se la chiave di ricerca trovata tra le simili, è quella che desideriamo; possiamo inserire l' istruzione di farci porre una domanda di confermare o meno il gruppo trovato, e quindi la modifica sarà:

  • Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    On Error Resume Next
    If ActiveSheet.Name = Sheets(1).Name Then Exit Sub
    Dim CL As Object
    x = Target.Value
    If x = "" Then Exit Sub
    For Each CL In Sheets(1).Range("A1:A10")
    If CL.Value = x Then
    a = CL.Offset(0, 1)
    b = CL.Offset(0, 2)
    c = CL.Offset(0, 3)
    d = CL.Offset(0, 4)
    Dim idom As Integer
     'aggiungiamo una domanda  che chiede di rispondere Si/No
    idom = MsgBox("Trovato questa corrispondenza: " & vbCr _
    & a & "-" & b & "-" & c & "-" & d & "" & vbCr _
    & "Vuoi questa?", vbYesNo)
    If idom = vbYes Then
     'se risponderemo Si, allora rendiamo le Celle a lato Target uguali ai valori letti, in caso 'contrario la routine cercherà altri valori uguali alla chiave di ricerca scritta nella cella Target e riporrà la domanda
    Target.Offset(0, 1) = a
    Target.Offset(0, 2) = b
    Target.Offset(0, 3) = c
    Target.Offset(0, 4) = d
    Exit For
    End If: End If
    Next
    Resume
    End Sub

Buon Lavoro.

prelevato sul sito www.ennius.altervista.org