Usare il Filtro Automatico con scelta libera del campo e del criterio di ricerca.

Quando usiamo il Filtro Automatico di Excel, sappiamo che dobbiamo selezionare la prima a sinistra delle celle che formano le intestazioni di colonna, celle che rappresentano i "campi" della tabella o database che dir si voglia, dopodichè sceglieremo il "campo" che intendiamo filtrare applicando un "criterio" di ricerca tra i valori presenti in quel campo. Non è altresì difficile realizzare una macro che ci consenta di automatizzare tramite codice vba le operazioni volute, semplicemente usando il "registratore di macro".

L'esercizio che presento si diversifica da una macro realizzata nel modo su esposto, in quanto lasceremo al vba il compito di identificare l'area del database, di contare il numero di colonne ( campi ) che formano il database, di chiederci tramite una InputBox il numero di campo ( o colonna ) su cui vorremo applicare il filtro, di trovare quanto è lungo l'elenco in modo da definire tutte le righe in cui faremo cercare, tramite un'altra InputBox, il criterio di ricerca sul quale applicare il filtro, e una volta accertato che il criterio è presente, filtrare tutti i dati. Vediamo i passaggi, e alla fine la routine completa:

• come prima condizione sarà quella di inserire nella sezione Generale - Dichiarazioni del Modulo che conterrà la nostra routine ( o macro che dir si voglia ) una semplice istruzione che ha lo scopo di rendere indifferente l'uso di maiuscole/minuscole nelle Inputbox che useremo per reperire le variabili. (Diversamente la ricerca sarebbe CaseSensitive, cioè sensibile alle maiusc/minusc. in quanto le istruzioni risiedono su un Modulo)

Option Compare Text

• identificare la posizione della tabella (o database ) sul foglio di lavoro; per fare questo ci affideremo all' UsedRange, (di cui esistono spiegazioni in questa sezione, paragrafo "Copia/Incolla 4") e assegneremo con l'istruzione Set, un nome alla tabella (zona) dell'UsedRange nel foglio attivo:

Set zona = ActiveSheet.UsedRange

• identificazione del numero di colonne da cui è formata la tabella. Questo ci consentirà di usare il numero colonna per scegliere il campo su cui applicare il filtro, anzichè il nome del campo, e di poter controllare poi se abbiamo richiesto un numero di colonna che non esiste. Assegneremo ad una variabile, la X, il numero di colonne ottenuto con questa istruzione:

X = zona.Columns.Count

• dichiarazione dei nomi delle variabili che memorizzeranno il numero di campo (Campo) ed il criterio di ricerca (Criterio) .

Dim Campo
Dim Criterio

• assegnazione alla prima variabile ( campo ) del valore che scriveremo nella prima finestra di dialogo ( InputBox )

Campo = InputBox("Inserire il numero del campo di ricerca")

• inserimento di un ciclio If...Then...End If  per controllare due eventi: il primo controlla che il valore restituito dalla inputbox  non sia vuoto, nel qual caso si uscirebbe dalla routine; precauzione necessaria per evitare proteste del debugger che se si proseguisse con l'esecuzione delle altre istruzioni si genererebbe un errore per mancanza di dati (la variabile Campo non conterrebbe nessun valore). Il secondo ciclo invece controlla se il valore numerico della variabile Campo ( Val(Campo) ) è maggiore del numero di colonne che la variabile X iniziale ha memorizzato. Se il valore sarà maggiore, verremo avvisati con un messaggio e si uscirà dalla routine anche in questo caso.

If Campo = "" Then Exit Sub
If Val(Campo) > X Then
MsgBox "N° Campo non presente"
Exit Sub
End If

• ora si passa ad assegnare alla seconda variabile ( Criterio ) il valore che inseriremo nella seconda InputBox. Anche qui eseguiremo il controllo per uscire se Criterio sarà vuoto. Trattandosi di testo da assegnare a Criterio, non sarà necessario dichiarare il "tipo" di dato numerico ( Val(Criterio) ), necessario peraltro nel caso che anche Criterio sia un valore numerico.

Criterio = InputBox("Inserire il criterio di ricerca")
If Criterio = "" Then Exit Sub

• a questo punto avremo bisogno, se vogliamo controllare la presenza del valore (Criterio) da filtrare nel campo già deciso, di un ciclo di ricerca che partendo dall'inizio della colonna identificata con Campo, trovi quante righe sono presenti nella colonna, e in questo Range effettui la ricerca. Per questo istruiremo un ciclo For Each....Next , usando la procedura già vista tante volte su questo sito.

Dim CL As Object

• dovremo far sapere da quale riga e quale colonna iniziare, rendendo "pippo" uguale alla riga 1 dell'area "zona", colonna numero Campo
  Set pippo = zona.Cells(1, Val(Campo))

• ora possiamo inizializzare il ciclo "per ogni cella (CL)" presente tra "pippo" e la fine di "pippo" in basso (End(xlDown)
  For Each CL In Range(pippo, pippo.End(xlDown))

• se il valore di una cella sarà uguale al valore memorizzato in Criterio, allora...
  If CL.Value = Criterio Then

• in questo caso (che il valore Criterio esista nell'elenco della colonna scelta) avremo bisogno di interrompere il ciclo, passando alle istruzioni di applicazione del filtro, ed useremo il vecchio GoTo indicando a quale indice riga spostarsi

GoTo 10

• nel caso che il valore del Criterio non sia presente, il compilatore proseguirà cercando in tutte le celle e alla fine concluderà la ricerca, e qui inseriremo un messaggio che ci avviserà che il valore non è presente, uscendo dalla routine:

End If
Next
MsgBox "Criterio Non presente"
Exit Sub

• dopo questa istruzione troveremo il riferimento all'indice riga a cui il compilatore sarebbe saltato se il Criterio veniva trovato, con a seguire l'istruzione per l'applicazione del filtro alla prima cella della prima riga dell'area tabella ( zona ), e l'indicazione del Campo e del Criterio da applicare.

10:
zona.Cells(1, 1).Select
Selection.AutoFilter
Selection.AutoFilter Field:=Campo, Criteria1:=Criterio

• una volta applicato il filtro riselezioniamo la prima cella e terminiamo l'istruzione

zona.Cells(1, 1).Select
End Sub

e questa l'intera routine:

Nota per quanto riguarda l'"UsedRange", faccio solo presente che l'esempio sopra è impostato su un foglio in cui sia presente solo il database, completo delle intestazioni dei "campi". Se invece sul foglio, prima del database, avessimo usato celle per scrivere altre cose, tipo "Ufficio Statistiche", o altre amenità del genere, l'UsedRange comprenderebbe nell'area anche queste celle, sballando completamente i valori dell'identificazione dei riferimenti riga, colonna, ecc.ecc.

Un'altra cosa: visto che con l'applicazione di un filtro, compaiono i famosi menù su ogni campo, sarebbe opportuno prevedere di poterli togliere e ripristinare il database dopo la consultazione. Per fare questo, suggerisco di usare un CommandButton delle ActiveX (Strumenti di controllo o Casella degli strumenti) sfruttando la sua proprietà Caption, in modo da differenziare nell'evento Click le istruzioni da seguire. Questo un esempio(dopo aver impostato la sua proprietà Caption a "Filtro"):

Private Sub CommandButton1_Click()
If CommandButton1.Caption = "Filtro" Then
CommandButton1.Caption = "Ritorno" 'cambia la caption "Filtro" in "Ritorno"
RicercaeFiltra 'chiama la routine sopra esposta
Else 'se invece la caption sarà diversa da "Filtro", e quindi "Ritorno"
CommandButton1.Caption = "Filtro" 'reimposta la caption a "Filtro"
Set zona = ActiveSheet.UsedRange  'e toglie dalla cella 1 di "zona" il filtro
zona.Cells(1, 1).Select
Selection.AutoFilter
End If
End Sub

Buon lavoro.

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