Cerca.Orizz + Cerca.Vert col Vba

Dovremmo tutti conoscere come lavorano queste due funzioni del foglio di lavoro. (ev. vedi sull'altro sito, sezione Formule, articolo "Funzione =CERCA.VERT").

Questa recente richiesta : "E' POSSIBILE FARE UN CERCA VERTICALE E ORIZZONTALE NELLA STESSA FORMULA, TIPO BATTAGLIA NAVALE. ESEMPIO : IN QUESTA DATA(ORIZZONTALE), TROVAMI LA PAROLA VINO (VERTICALE )" è il motivo di questo articolo.

Si chiede di riunire le due funzioni in una unica formula, che richiede inoltre altre funzioni come la funzione SE, VAL.NON.DISP, VAL.VUOTO, ecc. Questo porterebbe ad una formula chilometrica non impossibile da realizzare, ma secondo me, molto difficile da gestire. Il mio orientamento poi è quello di indirizzare verso il VBA, che è lo scopo del sito, quanti più pellegrini posso, e per questo presento queste possibili soluzioni in vba.

In questo articolo, visto che i dati che andremo cercando, potranno essere "tipi di dati" diversi fra loro all'interno della tabella, area della nostra ricerca, vedremo anche come definire il "tipo di dato" che reperiremo tramite InputBox. Vediamo intanto una tabella esempio, dove appunto sono mescolati numeri, nomi (testo) e date, e celle vuote; l'area che forma la tabella è da C3 a H18 :

Sull'esempio impostato sulla richiesta ricevuta, abbiamo la riga 3 dove si troveranno le date da cercare (quindi useremo una sorta di CERCA.ORIZZ), e una volta trovata la data, cercheremo nelle righe della colonna corrispondente alla data trovata, un valore (quindi useremo una sorta di CERCA.VERT). Sappiamo che per svolgere una ricerca, dobbiamo necessariamente fornire il "criterio" di ricerca; visto che dobbiamo svolgere due ricerche, avremo bisogno di definire due "criteri" di ricerca ; possiamo usare due sistemi per definire i criteri di ricerca:

Vedremo tutte e due le situazioni, implementate da altre routine che ci consentiranno di ampliare le possibilità operative, gestendo sia istruzioni che contemplano ricerche tra data e data, sia come identificare il "tipo di dati" reperito tramite le InputBox, ed in funzione del tipo di dato, assegnare alla variabile la giusta "Funzione di conversione del tipo di dati". (a questo proposito suggerisco di dare un occhiata all'articolo "Variabili e Tipi di Dati" in questa stessa sezione).

Situazione: per poter scorrere le celle della tabella, useremo due cicli For. Potevamo usare sia il ciclo For...Next, contando le righe e le colonne per avere i numeri che fornissero i limiti superiori dei cicli, sia il ciclo For Each Next, come proposto in questi esempi. Come in ogni ciclo For Each Next che si rispetti, dobbiamo definire le aree (o intervalli) su cui agire. Per l'intervallo che riguarda la ricerca orizzontale, usiamo la funzione End(xlToRight), che ci consente, impostata la cella di inizio date ( la C3), di trovare la cella finale ed avere quindi tutto l'intervallo. Per quanto riguarda invece l'intervallo verticale, NON possiamo usare la funzione End(xlDown) perchè non sapendo quale Colonna verrà scelta, potremo incontrare nelle righe della colonna, delle celle vuote (come per esempio nelle colonne G e H), e questo limiterebbe l'azione di reperire tutto l'intervallo in quanto End si fermerebbe alla prima cella vuota che trova. Aggiriamo l'ostacolo usando UsedRange che ci consente di ottenere l'intera area che contiene dati, e di questa ne contiamo le righe. Assegneremo questo numero ad una variabile R, variabile che useremo per determinare il numero dell'ultima riga nell'istruzione Cells.

Per la restituzione del secondo criterio cercato, ho usato il Select, cioè faccio selezionare la cella il cui valore corrisponde al secondo criterio di ricerca, ma sarà posibile usare qualunque altro sistema per utilizzare il valore così trovato, come il restituirlo in una altra cella, magari anche di un'altro foglio, oppure usare il valore trovato assegnandolo ad una variabile da usare in successive istruzioni, o ancora reperire il suo "indirizzo" (Address).

Sub CercaOrizVert()
Dim CR, CV As Object 'CR sarà la cella della riga orizz. e CV della verticale scorse dai cicli
R = ActiveSheet.UsedRange.Rows.Count 'contiamo da quante righe è composta l'area dati
Set zonaoriz = ActiveSheet.Range([C3], [C3].End(xlToRight)) 'impostiamo l'intervallo orizzont.
For Each CR In zonaoriz 'e per ogni cella nella zona orizzontale
If CR = [A1] Then 'cerchiamo la cella (CR) che avrà la data uguale alla data scritta in A1
C = CR.Column 'trovata la cella, con C prendiamo il numero di colonna, che ci serve per definire 'il numero di colonna usato dal riferimento Cells usato per impostare l'intervallo verticale, sotto:
Set zonavert = ActiveSheet.Range(Cells(3, C), Cells(R, C)) 'si inizia dalla riga 3
For Each CV In zonavert 'per ogni cella nella zona verticale
If CV = [B1] Then 'cerchiamo il secondo criterio scritto in B1
CV.Select 'se lo troviamo, selezioniamo la cella ed usciamo dai due cicli con unica istruzione. Se 'invece volessimo inserire il valore trovato in una cella del Foglio2, useremo un'istruzione tipo: 'Foglio2![D1.Value = CV.Value, oppure assegnare ad una variabile come Z = CV.Value e poi 'usare Z come vettore del valore trovato, ma potevamo solo volere l'indirizzo della cella, ed allora 'potremo anche qui usare una variabile, esempio W = CV.Address
Exit For: Exit For
End If
Next
End If
Next
End Sub

Seconda routine con i criteri reperiti tramite due InputBox. (le spiegazioni uguali alla routine precedente non verranno riportate):

Sub CercaOrizVert2()
Dim CL, CX As Object
R = ActiveSheet.UsedRange.Rows.Count
Set zonaoriz = ActiveSheet.Range([C3], [C3].End(xlToRight))

'sotto: con le variabili "uno" e "due" prendiamo i valori che inseriremo nelle due InputBox, il testo 'tra parentesi indica il tipo di risposta da inserire.
uno = InputBox("Scrivi la data che cerchi")
due = InputBox("Scrivi il secondo dato che cerchi")
For Each CL In zonaoriz
If CL = CDate(uno) Then 'se la cella (CL) in quel momento letta ha la data uguale alla data che 'avremo scritto nella prima InputBox, allora...(da notare che dobbiamo usare la funzione di 'conversione CDate(uno) per definire che il valore reperito con la InputBox è appunto una data e 'non testo come verrebbe "letto" dal codice (tutti i valori presi tramite InputBox sono sempre visti 'come stringhe (cioè testo))
C = CL.Column
Set zonavert = ActiveSheet.Range(Cells(3, C), Cells(R, C))
For Each CX In zonavert
If CX = due Then
CX.Select
Exit For: Exit For
End If
Next
End If
Next
End Sub

Ora, questa routine, per effetto della mancata definizione del tipo di dati della variabile "due" (presa con la seconda InputBox), riconoscerebbe solo i valori stringa, nella ricerca verticale. Ma se ci troviamo una condizione come quella della colonna E, dove sono presenti valori testo, valori numerici e date, e cercassimo il numero 50 (che è nella cella E9) scrivendolo nella seconda InputBox, questo valore non verrebbe trovato perchè "due" sarebbe il "testo" 50, mentre Excel sa che nella cella E9 c'è il "numero" 50, e i due valori NON sono gli stessi.

Soluzione: definiamo anche il tipo di dati che viene reperito con la variabile "due", esempio CDbl(due) (valore numerico con decimali). Già, ma se invece che un numero cerchiamo una data come quella presente in E13 (11/11/01) ? Sentiremmo il lamento del debugger che ci avvisa di un'errore: i dati non corrispondono: abbiamo scritto una data e poi la definiamo come Double. Orrore!!!

Enunciato: se i valori di una tabella appartengono tutti ad uno stesso tipo di dati, potremo se dati stringa, rinunciare a definire il tipo di dati, negli altri casi basterà definire il tipo di dati giusto usando la funzione di conversione appropriata. Nel caso invece che i valori contenuti nella tabella siano di tipo eterogeneo, seguiremo un'altra procedura.

Esiste la possibilità di "intercettare" che tipo di dato abbiamo inserito nella seconda InputBox, creando delle verifiche con IsNumeric e IsDate, ed assegnare la funzione di conversione appropriata. Lo vediamo in questa terza routine, dove ho inserito anche la variante di far controllare, per la ricerca orizzontale, se la data è compresa tra due date, prendendo come limite inferiore la prima data presente nell'intervallo di ricerca (cella C3). In questo modo saranno lette tutte le colonne le cui date rientrano nell'intervallo.

Terza routine con i criteri reperiti tramite due InputBox e controllo del tipo di dati. (le spiegazioni uguali alla routine precedente non verranno riportate):

Sub CercaOrizVert3()
Dim CL, CX As Object
Dim giorni 'si aggiunge la variabile "giorni" che serve a fare la differenza in giorni tra la data 'immessa nella inputbox e la prima data dell'elenco
R = ActiveSheet.UsedRange.Rows.Count
Set zonaoriz = ActiveSheet.Range([C3], [C3].End(xlToRight))
uno = InputBox("Scrivi la data che cerchi")
due = InputBox("Scrivi il secondo dato che cerchi")
If IsNumeric(due) Then 'si controlla se "due" è un numero, se corrisponde allora si imposta due 'come numero con possibilità di decimali (CDbl())
due = CDbl(due)
ElseIf IsDate(due) Then 'se invece "due" è una data, si imposta (sotto) "due come data (CDate())
due = CDate(due)
Else 'negli altri casi si imposta come stringa (CStr())
due = CStr(due)
End If
For Each CL In zonaoriz
primadata = CDate([C3]) 'ora con "primadata" prendiamo la data che è in C3
datauno = CDate(uno) 'e con "datauno" prendiamo la data immessa nella prima inputbox
giorni = DateDiff("d", primadata, datauno) 'con "giorni" otteniamo la differenza tra le due date

'sotto: ora si controlla se la data in quel momento letta nell'intervallo zonaoriz è inferiore o uguale 'alla data richiesta, oppure è uguale o superiore alla data richiesta MENO i giorni di "giorni"
If CL <= CDate(uno) Or CL >= datauno - giorni Then
C = CL.Column 'trovata una cella che corrisponde alla condizione sopra, prendiamo con C il 'numero di colonna da usarsi come indice colonna nelle istruzioni Cells che definiscono l'area per 'la ricerca verticale (con la variabile "zonavert")
Set zonavert = ActiveSheet.Range(Cells(3, C), Cells(R, C))
For Each CX In zonavert
If CX = due Then
CX.Select
Exit For: Exit For
End If
Next
End If
Next
End Sub

Inutile ricordare che per le ricerche con testo, se vogliamo trovare corrispondenza tra ciò che scriveremo e i valori stringa contenuti nelle celle dell'area dati, per quanto riguarda maiuscole/minuscole, basterà inserire nella sezione Generale - Dichiarazioni, l'istruzione Option Compare Text.

Va da se che oltre a fare ciò che vorrete con il valore trovato, potrete munire le routine di istruzioni per continuare a cercare un dato se dello stesso possono esservi duplicati, come pure munire le routine di controlli che verifichino (nel caso di usare celle come variabili da cercare) se le celle contengono dati, o altre amenità similari.