Condividi:        

Excel Convalida di Convalida Ordinata e Doppi

Vuoi potenziare i tuoi documenti Word? Non sai come si fa una macro in Excel? Devi creare una presentazione in PowerPoint?
Oppure sei passato a OpenOffice e non sei sicuro di come lavorare al meglio?

Moderatori: Anthony47, Flash30005

Re: Excel Convalida di Convalida Ordinata e Doppi

Postdi boman » 24/01/11 18:54

Riprendo la discussione in quanto mi sono imbattuto in un problemino. Andando a proteggere il foglio "elenco pratiche" (quindi proteggendo anche le 3 colonne di appoggio) e poi cliccando nella cella ove c'è la convalida nel foglio "schede orario" mi restituisce "errore di run-time 1004 - La cella o di un grafico che si sta tentando di modificare è protetto e pertanto in sola lettura. Per modificare una cella protetta o di un grafico, rimuovere la protezione tramite il comando Rimuovi protezione foglio (menu Strumenti, sottomenu protezione). Potrebbe essere richiesta una password"
E possibile risolvere? vorrei poter tener bloccate quelle 3 colonne appoggio per evitare che qualcuno le modifichi.
boman
Utente Junior
 
Post: 35
Iscritto il: 18/12/10 19:18

Sponsor
 

Re: Excel Convalida di Convalida Ordinata e Doppi

Postdi Anthony47 » 24/01/11 22:58

Senza dover andare nella prima parte della discussione e capire come sono organizzati i dati, metti questa macro in ThisWorkbook:
Codice: Seleziona tutto
Private Sub Workbook_Open()
Sheets("Foglio1").Unprotect        '<<< Inserisci il vero foglio di lavoro
Sheets("Foglio1").Protect UserInterFaceOnly:=True  '<<<Idem
End Sub

In questo modo il foglio e' protetto agli utenti ma non alle macro.
Perche' diventi attivo questo meccanismo dovrai salvare il file, chiuderlo e riaprirlo.

Ciao
Avatar utente
Anthony47
Moderatore
 
Post: 19196
Iscritto il: 21/03/06 16:03
Località: Ivrea

Re: Excel Convalida di Convalida Ordinata e Doppi

Postdi boman » 25/01/11 15:14

Anthony mi ha risolto perfettamente il problema ma me ne ha creato un'altro: mi succede che quando vado a proteggere il foglio e spunto la casella usa filtro automatico.. al momento il filtro funziona correttamente, quando però chiudo il file (salvandolo naturalmente) e lo riapro il filtro non funziona più.
Credo che sia colpa di quest'ultima macro che mi hai suggerito in quanto eliminandola il filtro funziona correttamente anche alla riapertura... ma naturalmente non funzionano le restanti macro.
boman
Utente Junior
 
Post: 35
Iscritto il: 18/12/10 19:18

Re: Excel Convalida di Convalida Ordinata e Doppi

Postdi ricky53 » 25/01/11 17:20

Ciao,
come già risposto nell'altro tuo intervento, prova con

Codice: Seleziona tutto
    Sheets("Foglio1").Protect UserInterFaceOnly:=True, AllowFiltering:=True
Dice il vecchio saggio provare e riprovare è l'unica strada per imparare

Più chiara è la vostra spiegazione
Più immediata sarà la nostra soluzione


. . . . . . . . . .
S.O. W10; Office 2003-10-13-16-19
Avatar utente
ricky53
Utente Senior
 
Post: 4565
Iscritto il: 11/04/09 19:29
Località: Italia

Re: Excel Convalida di Convalida Ordinata e Doppi

Postdi boman » 10/02/20 21:57

Buonasera, mi riallaccio a questo mio vecchio post (10 anni fa!). Ho sostanzialmente bisogno di fare la stessa cosa ma con più convalide, sei per la precisione. Ho provato più e più volte a fare modifiche al codice, ma non è per me :roll:
Questo è il file https://drive.google.com/file/d/16nAxkPhOOlLwr0v3GcfQ2TuVHzhD8Cq1/view?usp=sharing
Partendo da un foglio "DATABASE" con dati fissi
Immagine
devo estrarre in un secondo foglio "CONVALIDE" il valore UNITA' e COSTO corrispondenti alle sei convalide fatte
Immagine
Mi potete aiutare a riformulare il codice.
Grazie!!
boman
Utente Junior
 
Post: 35
Iscritto il: 18/12/10 19:18

Re: Excel Convalida di Convalida Ordinata e Doppi

Postdi boman » 11/02/20 09:33

Aggiorno il post di sopra (che non riesco più a modificare) allegando il vero file nella speranza che sia più comprensibile e che, nel caso mi mandiate il codice, poi di debba nuovamente chiedervi aiuto per le modifiche che dovrei fargli per adattarlo (riferimenti diversi, collone, righe, celle).
Questo è il file https://drive.google.com/file/d/19e3QdNM5r4qzjDQEQPu8qnR_2lutkhCa/view?usp=sharing
Partendo da un foglio "appoggioC2" con dati fissi:
Immagine
nel foglio di lavoro "C2"
Immagine
vorrei avere la possibilità di compilare le righe con sei convalide dati ad elenco condizionate dal quelle precedenti e che alla fine mi restituisca i valori "unità" e "costo" associati a quella determinata stringa.
Esempio (nel foglio C2):
nella cella F9 (Conv1) scelgo: "FABBRICATI INDUSTRIALI CAT D/1-D/7"
nella cella G9 (Conv2 - elenco condizionato alla scelta in Conv1) scelgo: "Capannoni ad un piano - struttura prefabbricata..."
nella cella H9 (Conv3 - elenco condizionato alla scelta in Conv1 e Conv2) scelgo: "Capannoni industriali fino a 1600 mq"
nella cella I9 (Conv4 - elenco condizionato alla scelta in Conv1, Conv2 e Conv3) scelgo: "Altezza fino 5 m"
nella cella J9 (Conv5 - elenco condizionato alla scelta in Conv1, Conv2, Conv3 e Conv4) scelgo: "anni 90"
nella cella K9 (Conv6 - elenco condizionato alla scelta in Conv1, Conv2, Conv3, Conv4 e Conv5) scelgo: "area UFFICIO"
nella cella L9 (UNITA') mi restituisce: "€/mq"
nella cella M9 (COSTO') mi restituisce: "160,00"
(l'esempio fa riferimento alla riga 12 del foglio "appoggioC2")

Scusate il doppio post e grazie!
boman
Utente Junior
 
Post: 35
Iscritto il: 18/12/10 19:18

Re: Excel Convalida di Convalida Ordinata e Doppi

Postdi Anthony47 » 11/02/20 12:41

Quale e' la tua versione di Excel?
Avatar utente
Anthony47
Moderatore
 
Post: 19196
Iscritto il: 21/03/06 16:03
Località: Ivrea

Re: Excel Convalida di Convalida Ordinata e Doppi

Postdi boman » 11/02/20 12:53

Home and Student 2010
boman
Utente Junior
 
Post: 35
Iscritto il: 18/12/10 19:18

Re: Excel Convalida di Convalida Ordinata e Doppi

Postdi Anthony47 » 12/02/20 00:37

Ho preso lo spunto da questa richiesta per sviluppare una soluzione di Convalida gerarchica di tipo modulare.

Per utilizzare questo modello:
A) diamo per scontato che sia presente un'area in cui siano elencate le possibili combinazioni dei campi; nel tuo caso corrisponde al contenuto di foglio appoggioC2, colonne B:G
B) inserisci nel tuo file un foglio di servizio che chiami ZConvalide (in realta' il nome potrebbe essere modificato intervenendo sul codice). Questo foglio sara' usato per creare dinamicamente le liste di convalida gerarchiche.
C) sul "modulo vba" del foglio su cui devi usare le convalide gerarchiche (il foglio C2, nel tuo caso) inserisci questo codice:
Codice: Seleziona tutto
'RIGOROSAMENTE IN TESTA AL MODULO

Private Const ConvArea As String = "F8:K8"              '<<< La prima riga sottoposta a convalida gerarchica
Private Const ListaSh As String = "appoggioC2"          '<<< Foglio degli elenchi sorgente
Private Const ListArea As String = "B2:G2"              '<<< Prima riga degli elenchi sorgente
Private Const wSh As String = "ZConvalide"              '<<< Il foglio di servizio
Private Const AutoClear As Boolean = True               '<<< True /False: se True azzera celle a dx di cella variata
Private Const AutoSort As Boolean = True                '<<< True /False; se True gli elenchi di convalida vengono Ordinati


Private Sub Worksheet_Change(ByVal Target As Range)
Dim myC As Range, lRow As Long
'
zConvL = Range(ConvArea).Cells(1, 1).Column
zConvH = zConvL + Range(ConvArea).Columns.Count - 1
If AutoClear Then
    Application.EnableEvents = False
    For Each myC In Target
        If myC.Column >= zConvL And myC.Column < zConvH And myC.Row >= Range(ConvArea).Cells(1, 1).Row Then
            myC.Offset(0, 1).Resize(1, zConvH - myC.Column).ClearContents
        End If
    Next myC
    Application.EnableEvents = True
End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim ListaRan As Range, ZConv As Worksheet
Dim TaRow As Long, TaCol As Long, zConvL As Long, zConvH As Long
Dim wArr, fArr(), fAI As Long, JJ As Long, II As Long, flExit As Boolean
Dim rArr, myMatch
'
If Selection.Count > 1 Then Exit Sub
'
Set ListaRan = Sheets(ListaSh).Range(ListArea)      'set del Foglio e prima riga degli elenchi sorgente
Set ZConv = Sheets(wSh)                             'set del Foglio di servizio usato per creare le covalide
'
TaCol = Target.Column
TaRow = Target.Row
zConvL = Range(ConvArea).Cells(1, 1).Column
zConvH = zConvL + Range(ConvArea).Columns.Count - 1
'
If Target.Column >= zConvL And Target.Column <= zConvH Then             'Check se siamo nell'area di convalida
    rArr = Cells(TaRow, zConvL).Resize(1, 1 - zConvL + TaCol).Value
    wArr = ListaRan.Cells(1, 1).Resize(ListaRan.Cells(1, 1).Offset(10000, 1).End(xlUp).Row, 1 + TaCol - zConvL).Value
    ReDim fArr(1 To UBound(wArr))
    For II = 1 To UBound(wArr)
        For JJ = 1 To UBound(wArr, 2) - 1
            If Len(rArr(1, JJ)) > 0 Then
                If UCase(wArr(II, JJ)) <> UCase(rArr(1, JJ)) Then
                    flExit = True
                    Exit For
                End If
            End If
            If flExit Then Exit For
        Next JJ
        If Not flExit And Len(wArr(II, JJ)) > 0 Then
            myMatch = Application.Match(wArr(II, JJ), fArr, False)
            If IsError(myMatch) Then
                fAI = fAI + 1
                fArr(fAI) = wArr(II, JJ)
            End If
        Else
            flExit = False
        End If
    Next II
    'Scrive nel foglio di servizio le liste di convalida e genera Name:
    fAI = fAI + 1
    ReDim Preserve fArr(1 To fAI)
    ZConv.Cells(1, TaCol - zConvL + 1).Resize(10000, 1).ClearContents
    'Se impostato AutoSort:
    If AutoSort Then
        For II = 1 To UBound(fArr) - 1
            For JJ = II To UBound(fArr)
                If UCase(fArr(II)) > UCase(fArr(JJ)) Then
                    wwk = fArr(II)
                    fArr(II) = fArr(JJ)
                    fArr(JJ) = wwk
                End If
            Next JJ
        Next II
    End If
    'Scrive:
    ZConv.Cells(1, TaCol - zConvL + 1).Resize(fAI, 1) = Application.WorksheetFunction.Transpose(fArr)
    'Genera Nomi:
    ZConv.Cells(1, TaCol - zConvL + 1).Resize(fAI, 1).Name = "Conv" & (TaCol - zConvL + 1)
End If
End Sub

Per accedere rapidamente all'area giusta del vba:
-tasto dx sul tab col nome del foglio (C2, nel tuo caso)
-scegli Visualizza codice
-rimuovi eventuali Sub Worksheet_Change e Sub Worksheet_SelectionChange gia' presenti
-poi copia il codice e incollalo nel frame presumibilmente vuoto, facendo in modo che la riga "RIGOROSAMENTE IN TESTA AL MODULO" sia effettivamente la prima.

Se il modulo vba non fosse vuoto allora devi valutare la compatibilita' tra le macro presenti e quelle da inserire

Le righe in testa servono per impostare i parametri di lavoro; sono compatibili col tuo file di lavoro, ma all'occorrenza possono essere modificati secondo il significato descritto nei commenti di riga.

D) Dopo aver posizionato il codice, torna al foglio C2 e fai una "spazzolata" delle colonne; questo serve a creare gli "intervalli nominati" da usare per la convalida.

E) A questo punto puoi impostare le convalide:
-seleziona l'area della colonna F e imposta una Convalida per Elenco, e come Origine scrivi =Conv1
-ripeti per la seconda colonne, ma come origine scrivi =Conv2
-ripeti per la terza, quarta.... colonna e come origine scrivi =Conv3, =Conv4, =Conv..., =Conv6


Se finalmente pronto per provare: scegli la prima categoria ad esempio in F8, e in G8 avrai a disposizione solo le categorie "figlie" della prima scelta; e cosi' per le altre colonne verso destra

Nei parametri in testa al codice sono presenti le opzioni AutoClear e AutoSort, ambedue impostate su Vero.
AutoClear = Vero provoca la cancellazione di tutte le scelte soggette a convalida eventualmente presenti alla destra di una cella il cui contenuto venga modificato
AutoSort = Vero provoca un ordinamento crescente dell'elenco di Convalida

Se queste impostazioni non ti piacciono (lo capisco per AutoSort, lo capirei meno per AutoClear), le puoi dichiarare =False e l'effetto non sara' applicato

Vedi se quanto presentato fa l'effetto richiesto per la convalida.

Quanto ai risultati che vuoi ottenere in colonna L ed M, puoi ricorrere a una formula; in L8 imposta la formula
Codice: Seleziona tutto
=INDICE(appoggioC2!H$1:H$1111;CONFRONTA($F8&$G8&$H8&$I8&$J8&$K8;appoggioC2!$B$1:$B$1111&appoggioC2!$C$1:$C$1111&appoggioC2!$D$1:$D$1111&appoggioC2!$E$1:$E$1111&appoggioC2!$F$1:$F$1111&appoggioC2!$G$1:$G$1111;0))

Da confermare con la combinazione Contr-Maiusc-Enter, non il solo Enter

Quindi copia L8 i M8; infine copia L8:M8 verso il basso

Prova e fai sapere

Ti avevo detto che avevo lavorato per preparare una soluzione modulare, che voglio inserire nella sezione I Vostri Lavori (viewtopic.php?f=26&t=109375) ti chiederei quindi l'autorizzazione a usare il tuo file, magari deprivato dei valori di costo, come esempio. Ovviamente inserirei anche il link a questa discussione.

Ciao
Avatar utente
Anthony47
Moderatore
 
Post: 19196
Iscritto il: 21/03/06 16:03
Località: Ivrea

Re: Excel Convalida di Convalida Ordinata e Doppi

Postdi boman » 12/02/20 11:58

Fantastico! Rimango ogni volta sbalordito della tua conoscenza, ma soprattutto dalla tua disponibilità nell'aiutare! Grazie.
Sul punto di poter condividere questo lavoro non c'è problema anzi mi fa piacere che qualcun altro possa giovarne. I dati non sono privati ma pubblici. Quindi si, autorizzo alla condivisione!

Venendo al codice, sto facendo delle prime prove e quasi tutto funziona egregiamente!! :D :D
Al momento mi sono accordo di questo problemino, esempio:
nel foglio C2, in F8 (Conv1) seleziono la voce "FABBRICATI INDUSTRIALI CAT D/1-D/7", in G8 (Conv2) mi restituisce "errore di run-time '13': Tipo non corrispondente".
Immagine
Credo di aver capito il problema, il testo all'interno della/e cella/e è troppo lungo. Infatti nel foglio appoggioC2 se vado ad abbreviare il testo corrispondente, nell'esempio "Capannoni ad un piano - struttura prefabbricata o mista ad una o più campate di c.a. (pilastri e travi di copertura in c.a.p.); pareti di tamponatura in elementi prefabbricati in c.a. o muratura ordinaria", l'errore non compare più!
Questo errore l'ho riscontrato anche con altre combinazioni, sempre per lo stesso motivo, testo troppo lungo.
E' un problema risolvibile?
Se non lo è, anima in pace ed abbrevio tutti i testi!

Ultima piccolezza, una finezza non indispensabile, se ad esempio clicco nella cella G8 (Conv2) o in H8 (Conv3) ecc.. senza aver prima selezionato alcun valore nella cella F8 (Conv1), mi restituisce sempre "errore di run-time '13': Tipo non corrispondente". Il rischio è che qualcuno, non io, vada a modificare inconsapevolmente il codice cliccando su debug. Sarebbero interessanti una di due soluzioni o entrambe: la prima, un messaggio di alert tipo "devi priva selezionare Conv1" oppure, la seconda, non quella di non far accedere al codice.
Ripeto, questo non è un problema, ma solo un eccesso di sicurezza.
Grazie davvero per il momento!!
boman
Utente Junior
 
Post: 35
Iscritto il: 18/12/10 19:18

Re: Excel Convalida di Convalida Ordinata e Doppi

Postdi boman » 12/02/20 13:12

Aggiungo un'ulteriore problemino, ma questa volta riguarda le colonne L (Unità) e M (Costo) del foglio C2. Anche in questo caso il problema sembra legato alla lunghezza del testo.
Immagine
Nell'esempio:
- la somma dei caratteri all'interno delle celle F8:K8 (52+29+19+148+0+7) è 255, in questo caso la celle L8 (Unità) e M8 (Costo) mi restituiscono i valori corretti;
- la somma dei caratteri all'interno delle celle F9:K9 (52+29+19+149+0+7) è 256, in questo caso la celle L9 (Unità) e M9 (Costo) non mi trovano il valore corrisponente.
Sembra che la funzione contenuta in quelle celle =INDICE(appoggioC2!H$1:H$1111;CONFRONTA($F9&$G9&$H9&$I9&$J9&$K9;appoggioC2!$B$1:$B$1111&appoggioC2!$C$1:$C$1111&appoggioC2!$D$1:$D$1111&appoggioC2!$E$1:$E$1111&appoggioC2!$F$1:$F$1111&appoggioC2!$G$1:$G$1111;0))
ammetta un stringa lunga al massimo 255 caratteri.
boman
Utente Junior
 
Post: 35
Iscritto il: 18/12/10 19:18

Re: Excel Convalida di Convalida Ordinata e Doppi

Postdi Anthony47 » 13/02/20 00:07

Nel file di prova pubblicato il problema "errore di run-time '13': Tipo non corrispondente" non mi si presenta mai; ma e' vero che la funzione Confronta (Match) accetta stringhe di max 255 caratteri.
Modificare il meccanismo usato per la creazione delle liste di convalida si potrebbe fare ma bisogna evitare di rallentare la macro, che deve lavorare "al volo" a fronte di ogni scelta. Ci dovro' pensare... ma con calma anche perche' ho capito che per te avere colonne con max 255 crt non e' problematico.

Invece l'attuale metodo per la ricerca dei risultati (le formule in colonna L ed M) vanno necessariamente riviste perche' evidentemente e' molto probabile che concatenando 6 celle la stringa risultante possa essere piu' lunga di 255 crt

Avrei optato quindi per creare un "hash" delle stringhe, per avere quindi una "stringa codificata" praticamente intercambiabile con la stringa di origine (le probabilita' che stringhe diverse producano lo stesso hash e' molto bassa).
Per questo scopo, bisogna aggiungere una funzione di conversione; corrisponde a questo codice che va messa in un modulo standard del vba (es Modulo1):
Codice: Seleziona tutto
Dim eNCr As Object    'RIGOROSAMENTE IN TESTA AL MODULO

Function StringToMD5Hex(ByVal s As String) As String
'codice by pgc01, MrExcel MVP
Dim bytes() As Byte
Dim Pos As Long
Dim outStr As String

If eNCr Is Nothing Then
    Set eNCr = CreateObject("System.Security.Cryptography.MD5CryptoServiceProvider")
End If
bytes = StrConv(s, vbFromUnicode)
bytes = eNCr.ComputeHash_2(bytes)

For Pos = LBound(bytes) To UBound(bytes)
   outStr = outStr & LCase(Right("0" & Hex(bytes(Pos)), 2))
Next Pos
StringToMD5Hex = outStr
'Set eNCr = Nothing
End Function

Posiziona il codice in un modulo vuoto, in modo che la riga marcata "RIGOROSAMENTE IN TESTA AL MODULO" sia appunto in cima, prima di ogni Macro o Function

Bisogna ora creare una colonna con le stringhe di hash; ho usato per questo la colonna K del foglio "appoggioC2", dove in K2 ho inserito questa formula:
Codice: Seleziona tutto
=StringToMD5Hex(TESTO.UNISCI("_";FALSO;B2:G2))

Copia poi K2 verso il basso per tutto le combinazioni presenti sul foglio (puoi anche allungarla su righe al momento non popolate, se l'elenco pensi si possa allungare con nuove combinazioni; se anche esageri l'impatto dovrebbe essere irrilevante perche' queste formule non si ricalcolano in continuazione)

Ora siamo pronti per modificare le formule in colonna L ed M, usando il contenuto di questa colonna K invece che il concatenamento delle 6 colonne di convalida.
Su foglio C2, in posizione L8 inserisci la formula
Codice: Seleziona tutto
=INDICE(appoggioC2!H$1:H$1111;CONFRONTA(StringToMD5Hex(TESTO.UNISCI("_";FALSO; $F8:$K8));appoggioC2!$K$1:$K$1111;0))

Copia L8 su M8, infine copia L8:M8 e incolla da L9 in giù

Fai sapere...
Avatar utente
Anthony47
Moderatore
 
Post: 19196
Iscritto il: 21/03/06 16:03
Località: Ivrea

Re: Excel Convalida di Convalida Ordinata e Doppi

Postdi boman » 18/03/20 15:56

Rispondo con grave ritardo,
Nel file di prova pubblicato il problema "errore di run-time '13': Tipo non corrispondente" non mi si presenta mai; ma e' vero che la funzione Confronta (Match) accetta stringhe di max 255 caratteri.
Modificare il meccanismo usato per la creazione delle liste di convalida si potrebbe fare ma bisogna evitare di rallentare la macro, che deve lavorare "al volo" a fronte di ogni scelta. Ci dovro' pensare... ma con calma anche perche' ho capito che per te avere colonne con max 255 crt non e' problematico.

Per facilità di utilizzo ho deciso che la soluzione migliore era abbreviare tutti i testi all'interno della cella.

Invece l'attuale metodo per la ricerca dei risultati (le formule in colonna L ed M) vanno necessariamente riviste perche' evidentemente e' molto probabile che concatenando 6 celle la stringa risultante possa essere piu' lunga di 255 crt

Ho abbreviato così tanto i testi che la stringa di concatenamento delle celle è più corta di 255 caratteri!

Avrei optato quindi per creare un "hash" delle stringhe, per avere quindi una "stringa codificata" praticamente intercambiabile con la stringa di origine (le probabilita' che stringhe diverse producano lo stesso hash e' molto bassa).
Per questo scopo, bisogna aggiungere una funzione di conversione; corrisponde a questo codice che va messa in un modulo standard del vba (es Modulo1):
CODICE: SELEZIONA TUTTO
Dim eNCr As Object 'RIGOROSAMENTE IN TESTA AL MODULO

Function StringToMD5Hex(ByVal s As String) As String
'codice by pgc01, MrExcel MVP
Dim bytes() As Byte
Dim Pos As Long
Dim outStr As String

If eNCr Is Nothing Then
Set eNCr = CreateObject("System.Security.Cryptography.MD5CryptoServiceProvider")
End If
bytes = StrConv(s, vbFromUnicode)
bytes = eNCr.ComputeHash_2(bytes)

For Pos = LBound(bytes) To UBound(bytes)
outStr = outStr & LCase(Right("0" & Hex(bytes(Pos)), 2))
Next Pos
StringToMD5Hex = outStr
'Set eNCr = Nothing
End Function

Posiziona il codice in un modulo vuoto, in modo che la riga marcata "RIGOROSAMENTE IN TESTA AL MODULO" sia appunto in cima, prima di ogni Macro o Function

Bisogna ora creare una colonna con le stringhe di hash; ho usato per questo la colonna K del foglio "appoggioC2", dove in K2 ho inserito questa formula:
CODICE: SELEZIONA TUTTO
=StringToMD5Hex(TESTO.UNISCI("_";FALSO;B2:G2))

Copia poi K2 verso il basso per tutto le combinazioni presenti sul foglio (puoi anche allungarla su righe al momento non popolate, se l'elenco pensi si possa allungare con nuove combinazioni; se anche esageri l'impatto dovrebbe essere irrilevante perche' queste formule non si ricalcolano in continuazione)

Ora siamo pronti per modificare le formule in colonna L ed M, usando il contenuto di questa colonna K invece che il concatenamento delle 6 colonne di convalida.
Su foglio C2, in posizione L8 inserisci la formula
CODICE: SELEZIONA TUTTO
=INDICE(appoggioC2!H$1:H$1111;CONFRONTA(StringToMD5Hex(TESTO.UNISCI("_";FALSO; $F8:$K8));appoggioC2!$K$1:$K$1111;0))

Copia L8 su M8, infine copia L8:M8 e incolla da L9 in giù

Ho provato con questo metodo, ma non riuscivo a farlo funzionare, ma, come detto sopra, abbreviando tutti i testi ho risolto il problema.

Durante lo sviluppo del file, per necessità che sopraggiungevano, ho dovuto ampliare condizioni le da 6 a 8 con le dovute modifiche al codice principale.
Inoltre ho aggiustato lo stesso codice per altri fogli indipendenti, con altre condizioni. Per miglior chiarezza allego il file (pulito di alcune parti)
https://drive.google.com/file/d/1dOCQ_GXUPiE-fUui7gYDsl6FztFd5Ore/view?usp=sharing

Grazie Anthony47 per l'indispensabile aiuto.
boman
Utente Junior
 
Post: 35
Iscritto il: 18/12/10 19:18

Re: Excel Convalida di Convalida Ordinata e Doppi

Postdi Anthony47 » 18/03/20 16:26

E' giusta la mia interpretazione che ora il problema e' risolto, e hai potuto applicare il metodo su piu' fogli, con elenchi di convalida diversi e diverse colonne da convalidare?
Se Si, bravo e grazie per il feedback. Se No, :( e attendo chiarimenti su cosa non ha funzionato

Ciao
Avatar utente
Anthony47
Moderatore
 
Post: 19196
Iscritto il: 21/03/06 16:03
Località: Ivrea

Re: Excel Convalida di Convalida Ordinata e Doppi

Postdi boman » 18/03/20 17:19

Il primo codice che mi hai proposto (post del 12/02/20 00:37), quello per le 6 convalide nel "modulo vba", l'ho adattato alle modifiche che man mano apportato al file excel e funziona egregiamente.

Il secondo codice/metodo che mi hai proposto (post del 13/02/20 00:07), quello per risolvere il problema della ricerca dei risultati con stringhe superiori a 255 caratteri non mi funzionava, ma, come già detto, avendo successivamente deciso di abbreviare tutti i testi l'ho abbandonato perché non più necessario.

In conclusione: sono arrivato al mio obbiettivo!
boman
Utente Junior
 
Post: 35
Iscritto il: 18/12/10 19:18

Re: Excel Convalida di Convalida Ordinata e Doppi

Postdi boman » 08/05/20 10:48

Mi riallaccio al post per un'ulteriore problema/quesito che mi è sorto in questo momento nell'utilizzo di tutta la procedura sviluppata e adattata ad un altro file. Mi sono accorto che digitando nella cella un testo diverso da quello dell'elenco proposto (convalida dati) non mi da nessun messaggio di errore. Il flag "mostra messaggio di errore quando i dati immessi non sono validi" è attivo.
Per brevità e facilità, riporto il codice che utilizzo
Codice: Seleziona tutto
'RIGOROSAMENTE IN TESTA AL MODULO

Private Const ConvArea As String = "H7:J7"              '<<< La prima riga sottoposta a convalida gerarchica
Private Const ListaSh As String = "PRATICHE"          '<<< Foglio degli elenchi sorgente
Private Const ListArea As String = "C5:E5"              '<<< Prima riga degli elenchi sorgente
Private Const wSh As String = "PRATICHEconvalide"              '<<< Il foglio di servizio
Private Const AutoClear As Boolean = True               '<<< True /False: se True azzera celle a dx di cella variata
Private Const AutoSort As Boolean = True                '<<< True /False; se True gli elenchi di convalida vengono Ordinati


Private Sub Worksheet_Change(ByVal Target As Range)
Dim myC As Range, lRow As Long
'
zConvL = Range(ConvArea).Cells(1, 1).Column
zConvH = zConvL + Range(ConvArea).Columns.Count - 1
If AutoClear Then
    Application.EnableEvents = False
    For Each myC In Target
        If myC.Column >= zConvL And myC.Column < zConvH And myC.Row >= Range(ConvArea).Cells(1, 1).Row Then
            myC.Offset(0, 1).Resize(1, zConvH - myC.Column).ClearContents
        End If
    Next myC
    Application.EnableEvents = True
End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim ListaRan As Range, ZConv As Worksheet
Dim TaRow As Long, TaCol As Long, zConvL As Long, zConvH As Long
Dim wArr, fArr(), fAI As Long, JJ As Long, II As Long, flExit As Boolean
Dim rArr, myMatch
'
If Selection.Count > 1 Then Exit Sub
'
Set ListaRan = Sheets(ListaSh).Range(ListArea)      'set del Foglio e prima riga degli elenchi sorgente
Set ZConv = Sheets(wSh)                             'set del Foglio di servizio usato per creare le covalide
'
TaCol = Target.Column
TaRow = Target.Row
zConvL = Range(ConvArea).Cells(1, 1).Column
zConvH = zConvL + Range(ConvArea).Columns.Count - 1
'
If Target.Column >= zConvL And Target.Column <= zConvH Then             'Check se siamo nell'area di convalida
    rArr = Cells(TaRow, zConvL).Resize(1, 1 - zConvL + TaCol).Value
    wArr = ListaRan.Cells(1, 1).Resize(ListaRan.Cells(1, 1).Offset(10000, 1).End(xlUp).Row, 1 + TaCol - zConvL).Value
    ReDim fArr(1 To UBound(wArr))
    For II = 1 To UBound(wArr)
        For JJ = 1 To UBound(wArr, 2) - 1
            If Len(rArr(1, JJ)) > 0 Then
                If UCase(wArr(II, JJ)) <> UCase(rArr(1, JJ)) Then
                    flExit = True
                    Exit For
                End If
            End If
            If flExit Then Exit For
        Next JJ
        If Not flExit And Len(wArr(II, JJ)) > 0 Then
            myMatch = Application.Match(wArr(II, JJ), fArr, False)
            If IsError(myMatch) Then
                fAI = fAI + 1
                fArr(fAI) = wArr(II, JJ)
            End If
        Else
            flExit = False
        End If
    Next II
    'Scrive nel foglio di servizio le liste di convalida e genera Name:
    fAI = fAI + 1
    ReDim Preserve fArr(1 To fAI)
    ZConv.Cells(1, TaCol - zConvL + 1).Resize(10000, 1).ClearContents
    'Se impostato AutoSort:
    If AutoSort Then
        For II = 1 To UBound(fArr) - 1
            For JJ = II To UBound(fArr)
                If UCase(fArr(II)) > UCase(fArr(JJ)) Then
                    wwk = fArr(II)
                    fArr(II) = fArr(JJ)
                    fArr(JJ) = wwk
                End If
            Next JJ
        Next II
    End If
    'Scrive:
    ZConv.Cells(1, TaCol - zConvL + 1).Resize(fAI, 1) = Application.WorksheetFunction.Transpose(fArr)
    'Genera Nomi:
    ZConv.Cells(1, TaCol - zConvL + 1).Resize(fAI, 1).Name = "ConvA" & (TaCol - zConvL + 1)
End If
End Sub
boman
Utente Junior
 
Post: 35
Iscritto il: 18/12/10 19:18

Re: Excel Convalida di Convalida Ordinata e Doppi

Postdi Anthony47 » 10/05/20 18:02

Non ci avevo mai fatto caso: se in un elenco di convalida sono presenti celle vuote e la convalida avviene tramite un "Intervallo Nominato" associato a quell'area allora, quando si inputa un dato non in elenco, il messaggio di errore potrebbe non comparire; bisognerebbe togliere la spunta alla voce "Ignora celle vuote".
Per inciso, se invece la convalida avviene usando il riferimento diretto a quelle celle (quindi, ad esempio: =Foglio3!A1:A10) allora il messaggio compare anche con "Ignora celle vuote" spuntato.

Se le celle con Convalida gia' impostata sono tante allora si puo' usare la seguente macro per settare automaticamente, sulle convalide con Elenco pari a "ConvXX", "Ignora celle vuote" su False:
Codice: Seleziona tutto
Sub IgnBlank()
Dim myC As Range, vType As Long, vForm As String
'
Application.EnableEvents = False
For Each myC In Cells.SpecialCells(xlCellTypeAllValidation)
'myC.Select

    vType = 99
    On Error Resume Next
        vType = myC.Validation.Type
        vForm = myC.Validation.Formula1
    On Error GoTo 0
    If vType = 3 And Left(vForm, 5) = "=Conv" Then
        myC.Validation.IgnoreBlank = False
    End If
    Z = Z + 1
Next myC
Application.EnableEvents = True
End Sub

Va inserito in un Modulo standard del vba e poi eseguito (una sola volta sara' sufficiente, SE per il futuro ti ricorderai di impostare "Ignora celle vuote" = False quando imposti inizialmente le convalide)

Ciao
Avatar utente
Anthony47
Moderatore
 
Post: 19196
Iscritto il: 21/03/06 16:03
Località: Ivrea

Re: Excel Convalida di Convalida Ordinata e Doppi

Postdi boman » 10/05/20 19:16

Genio! Risolto anche questo!
Grazie per avermi proposto anche una macro per correggere tutte le celle (hai guardato avanti!), ma ho flaggato "applica le modifiche a tutte le altre celle con le stesse impostazioni" nella stessa finestra in cui ho tolto il flag a "ignora celle vuote". Il file, per il momento, è definito e non ha possibilità di essere ampliato in celle e righe, pertanto la soluzione di applicare le modifiche alle altre celle mi è bastato! In ogni caso mi appunto la macro nel caso mi servisse in futuro!
Grazie!
boman
Utente Junior
 
Post: 35
Iscritto il: 18/12/10 19:18

Precedente

Torna a Applicazioni Office Windows


Topic correlati a "Excel Convalida di Convalida Ordinata e Doppi":


Chi c’è in linea

Visitano il forum: Nessuno e 51 ospiti