Menu a tendina con ricerca di lettere comunque contenute nell'elenco

Question

Salve

ho utilizzazto la formula   =SCARTO(A2;CONFRONTA(C2&"*";A2:A12;0)-1;;CONTA.SE(A2:A12;C2&"*"))  per la ricerca dinamica in un menu a tendina.

Vorrei sapere se è possibile utilizzare la stessa formula ma modificata in modo che mi selezioni non solo le parole che iniziano con le lettere che io digito ma anche quelle che contengono le stesse in qualsiasi posizione della parola.

Per es. se digito (in un elenco di città) "man" vorrei ottenere non solo Mantova ma anche Casalromano, Palmanova, etc..

Il tutto senza l'uso del VBA.

Grazie

A.C.

Answer 1

Ciao Aurelio,

Per rendere il codice più robusto e aumentare il numero di elementi visualizzati nel ComboBox, prova la seguente modifica del mio codice:

• Aggiungi un ComboBox (ComboBox1) al foglio Comuni Italiani
• Fai clic dx sulla linguetta del foglio di interesse
• Seleziona l'opzione Visualizza Codicedal****menu contestuale risultante
• Incolla il seguente codice:

'========>>

Option Explicit

'-------->>

Private Sub ComboBox1_Change()

    Call Update_Combo

End Sub

'-------->>

Private Sub ComboBox1_DropButtonClick()

 Call Update_Combo

End Sub

'<<========

• Alt+IMper inserire un nuovo modulo di codice
• Nel nuovo modulo vuoto, incolla il seguente codice:

 '========>>

Option Explicit

Option Compare Text

'-------->>

Public Sub Update_Combo()

    Dim WB As Workbook

    Dim SH As Worksheet

    Dim srcRng As Range, destRng As Range

    Dim arrIn As Variant, arrOut() As Variant

    Dim OleObj As OLEObject

    Dim CBox As ComboBox

    Dim sCriterio As String

    Dim i As Long, iCtr As Long

    Dim LRow As Long

    Const sFoglio As String = "Comuni Italiani"

    Const sCella_Convalida_Dati As String = "D2"

    Set WB = ThisWorkbook

    Set SH = WB.Sheets(sFoglio)

    With SH

        LRow = LastRow(SH, .Columns("A:A"))

        Set srcRng = .Range("A2:A" & LRow)

        Set destRng = .Range(sCella_Convalida_Dati)

    End With

    Set OleObj = SH.OLEObjects("ComboBox1")

    Set CBox = OleObj.Object

    sCriterio = "*" & CBox.Value & "*"

    arrIn = srcRng.Value

    For i = 1 To UBound(arrIn)

        If arrIn(i, 1) Like sCriterio Then

            iCtr = iCtr + 1

            ReDim Preserve arrOut(1 To iCtr)

            arrOut(iCtr) = arrIn(i, 1)

        End If

    Next i

    If CBool(iCtr) Then

        With CBox

            .List = arrOut

            .DropDown

            .ListRows = 25

        End With

    End If

End Sub

'--------->>

Public Function LastRow(SH As Worksheet, _

    Optional Rng As Range, _

    Optional minRow As Long = 1)

    If Rng Is Nothing Then

        Set Rng = SH.Cells

    End If

    On Error Resume Next

    LastRow = Rng.Find(What:="*", _

        After:=Rng.Cells(1), _

        Lookat:=xlPart, _

        LookIn:=xlFormulas, _

        SearchOrder:=xlByRows, _

        SearchDirection:=xlPrevious, _

        MatchCase:=False).Row

    On Error GoTo 0

    If LastRow < minRow Then

        LastRow = minRow

    End If

End Function

'<<========  

• Alt+Q per chiudere l'editor di VBA e tornare a Excel
• Salva il file con l’estensionexlsm

Potresti scaricare il mio file aggiornato Aurelio20210407.xlsm

===

Regards,

Norman

Answer 2

Ciao Aurelio,

ti volevo informare che ho cercato un po non sembra esistere niente del genere che io conosca, pensavo esistesse qualche formula che ritornasse i valori trovati, ne esiste una matriciale che ritorna i riferimenti alle righe e non è nemmeno utilizzabile nella convalida in quanto va "trascinata" quindi non ritorna tutti i valori trovati. A quanto pare l'unica soluzione e' il VBA.

Answer 3

In effetti il problema e' che la formula non fa altro che ritornarti, successivamente al primo valore trovato, il numero di parole contenenti quel testo all'interno del range selezionato.

Ovvero tu cerchi rcan, trova la prima parola contenente Rcan e poi con Scarto ti seleziona altre X righe dove X e' il numero di celle contenenti "rcan" che nel caso in cui fossero ordinate alfabeticamente e la parola iniziasse per Rcan allora andrebbe bene perché le successive X celle conterranno proprio Rcan, nel tuo caso invece non funziona.

Quindi quella formula non va proprio bene per il tuo caso, dovrebbe esserci qualcosa in grado di cercare le parole contenenti quel testo all'interno di un range e oltre a cercarle, restituirtele o quantomeno restituirti il riferimento ad esso.

Non so se esista in realtà, devo cercare.

Answer 4

Ciao Daniele

ti ringrazio per la risposta. Ho provato come scrivi tu e mi funziona... parzialmente, mi spiego:

Come puoi vedere nel file "Dbprova" in OneDrive (che contiene l'elenco dei Comuni d'Italia) https://1drv.ms/x/s!Alw7fYh53QMygxqtuggE1a2oy7gM?e=PcEzvX se in C2 digito "rcang" mi trova esattamente tutti i Comuni d'Italia che contengono le lettere scritte,

se invece digito "rcan" mi trova come primo comune "Rive d'Arcane" e poi altri che non contengono le 4 lettere da me scritte.

Ma la cosa più strana è che non mi trova i Comuni che contengono "rcang".

Non comprendo la logica e di conseguenza l'errore.

Grazie per la tua pazienza.

A.

Answer 5

Ciao Aurelio,

bentrovato nella community Microsoft, piacere di aiutarti, sono Daniele un consulente indipendente,

dovrebbe essere sufficiente aggiungere "*"& prima di C2 sia in CONTA.SE che in CONFRONTA

=SCARTO(A2;CONFRONTA("*"&C2&"*";A2:A12;0)-1;;CONTA.SE(A2:A12;"*"&C2&"*")) 

Fammi sapere se funziona, nel caso non funzionasse condividimi un file di esempio esente di dati sensibili caricandolo per esempio su qualche sito di upload oppure condividendolo tramite OneDrive.

Vedi il link di seguito per sapere come fare per condividere tramite OneDrive:

https://support.office.com/it-it/article/condiv...

Saluti,

Daniele