Valutazione 4.87/ 5 (100.00%) 5838 voti

Condividi:        

Inserire convalida/elenco con Vba

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

Inserire convalida/elenco con Vba

Postdi Lyke73 » 27/10/14 11:12

Ciao a tutti, all'interno di una macro, tra le altre istruzioni, ho anche l'iserimento di un elenco tramite convalida ( creata con il registratore di macro). Funzionare funziona, però all'interno dell'elenco ci sono anche le celle vuote, per essere sicuro di comprendere tutto i valori di cui ho bisogno, l'intervallo è "a10:a100000". C'è un modo per fare in modo che l'elenco comprenda solo le celle con valore?
Allego anche la macro
Codice:
Range("A2").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=$A$10:$A$100000"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With

Grazie per l'aiuto.
Lyke73
Utente Junior
 
Post: 21
Iscritto il: 27/10/14 11:07

Sponsor
 

Re: Inserire convalida/elenco con Vba

Postdi dipic » 27/10/14 12:42

Se ordini il range, le celle vuote vanno tutte alla fine ... può aiutarti così?
Avatar utente
dipic
Utente Junior
 
Post: 95
Iscritto il: 13/09/08 14:57
Località: Cormòns (I)

Re: Inserire convalida/elenco con Vba

Postdi Lyke73 » 27/10/14 13:50

Nel range dal quale creo l'elenco i valori sono già ordinati, quello che vorrei sapere è se esiste un modo per definire solo le celle che contengono dati, metto come fine range "a:100000" perchè l'elenco viene aggiornato ogni ora, le righe sono variabili, comunque grazie per la celere risposta !
excel 2007
Lyke73
Utente Junior
 
Post: 21
Iscritto il: 27/10/14 11:07

Re: Inserire convalida/elenco con Vba

Postdi dipic » 27/10/14 14:57

inserisci all'inizio della tua macro un ciclo (x=10 to 100000) che cancelli la cella A(x) se uguale ad "" or empty.

Codice: Seleziona tutto
For x = 10 to 100000
if cells(x,1).value = "" or isempty(cells(x,1).value) then
cells(x,1).select
Selection.Delete Shift:=xlUp
end if
next x
Avatar utente
dipic
Utente Junior
 
Post: 95
Iscritto il: 13/09/08 14:57
Località: Cormòns (I)

Re: Inserire convalida/elenco con Vba

Postdi ricky53 » 27/10/14 17:01

Ciao,
è preferibile cancellare le celle partendo dall'ultima riga ... in modo da ottimizzare il ciclo e poi vale la pena di individuare l'ultima cella con dati piuttosto che fissare a 100.000 il ciclo.


Prova questo esempio
Codice: Seleziona tutto
Option Explicit

Sub Cancella_Righe_Vuote()
    Dim UR As Long, I As Long, Cancellate As Long, WS As Worksheet
    Set WS = ActiveSheet
    UR = WS.Range("A" & Rows.Count).End(xlUp).Row
    Cancellate = 0
    For I = UR To 2 Step -1
        If WS.Cells(I, "A") = "" Then
            WS.Cells(I, "A").Delete Shift:=xlUp
            Cancellate = Cancellate + 1
        End If
    Next I
    MsgBox "Sono state cancellate:  " & Cancellate & "   celle"
    Set WS = Nothing
End Sub


Non sono entrato nel merito della richiesta ... mi spiace ma non avevo tempo.
Ho solo voluto dare il mio piccolo contributo però, come commento, dico che si poteva utilizzare il filtro avanzato per produrre un elenco senza ripetizioni e senza celle vuote .. in un'altra colonna e poi impostare la convalida ...
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. W7; Office 2003-10-13-16
Avatar utente
ricky53
Utente Senior
 
Post: 4223
Iscritto il: 11/04/09 19:29
Località: Italia

Re: Inserire convalida/elenco con Vba

Postdi Lyke73 » 27/10/14 18:30

Grazie dell'aiutoad entrambi,
e poi vale la pena di individuare l'ultima cella con dati piuttosto che fissare a 100.000 il ciclo.

E' proprio quello che vorri fare...sto provando ad usare e modificare i vostri codici, ma con scarsi risultati.
Per trovare il fine elenco, uso questo codice
Codice: Seleziona tutto
ActiveSheet.Range("a11", ActiveSheet.Range("a11").End(xlDown)).Select

ma al momento di indicare il range da cui creare l'elenco mi fermo ( sono un autodidatta per le poche cosa che conosco ).
Se si potesse sostituire quedto pezzo di codice
Codice: Seleziona tutto
Formula1:="=$A$10:$A$50000

con questo
Codice: Seleziona tutto
ActiveSheet.Range("a11", ActiveSheet.Range("a11").End(xlDown)).Select


io sarei a posto.
excel 2007
Lyke73
Utente Junior
 
Post: 21
Iscritto il: 27/10/14 11:07

Re: Inserire convalida/elenco con Vba

Postdi ricky53 » 27/10/14 19:36

Sicuro di avere letto bene il mio intervento precedente? Ti ho proposto il codice che fa proprio quello che hai chiesto!
Rileggi con maggiore attenzione e prova il codice ma prima fai una copia del tuo file.
La convalida la vediamo successivamente
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. W7; Office 2003-10-13-16
Avatar utente
ricky53
Utente Senior
 
Post: 4223
Iscritto il: 11/04/09 19:29
Località: Italia

Re: Inserire convalida/elenco con Vba

Postdi Lyke73 » 27/10/14 20:14

Come vedi capisco ancora poco di Vba, comunque la macro l'avevo lanciata e ho visto che cancella le righe vuote in colonna A.
Allego un file di esempio, con le tua macro e il pezzo di codice che uso per creare l'elenco tramite convalida.

http://www.filedropper.com/esempio1_1
excel 2007
Lyke73
Utente Junior
 
Post: 21
Iscritto il: 27/10/14 11:07

Re: Inserire convalida/elenco con Vba

Postdi ricky53 » 28/10/14 00:13

Ciao,
prova questa macro: è la tua con alcune modifiche e aggiunte
Codice: Seleziona tutto
Sub Imposta_Convalida()
    Dim UR As Long, WS As Worksheet ' <<===== Aggiunta
    Set WS = ActiveSheet ' <<===== Aggiunta
   
    UR = WS.Range("A" & Rows.Count).End(xlUp).Row ' <<===== Aggiunta
    WS.Range("A2").Select ' <<==== Modificata
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=$A$10:$A$" & UR ' <<==== Modificata
        .IgnoreBlank = False
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
   
    Set WS = Nothing ' <<===== Aggiunta
End Sub
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. W7; Office 2003-10-13-16
Avatar utente
ricky53
Utente Senior
 
Post: 4223
Iscritto il: 11/04/09 19:29
Località: Italia

Re: Inserire convalida/elenco con Vba

Postdi Lyke73 » 28/10/14 12:52

Funziona perfettamente, grazie! Abuso ancora della tua competenza, esiste una regola generale ( o sai dirmi dove trovarla ) per usare lo stesso metodo con le altre formule ? Ho provato ad adattare le tua macro con la formula =conta.se, ma ovviamente il risultato è stato scarso, questi sono gli obrobri che ho cercato di scrivere
Codice: Seleziona tutto
Sub Macro2()
Dim UR As Long, WS As Worksheet ' <<===== Aggiunta
        Set WS = ActiveSheet ' <<===== Aggiunta
       
        UR = WS.Range("b" & Rows.Count).End(xlUp).Row ' <<===== Aggiunta
        WS.Range("b6").Select ' <<==== Modificata

    ActiveCell.FormulaR1C1 = "=COUNTIF($b$11:$b$ & UR,R5C)"
End Sub

Codice: Seleziona tutto
Sub tre()
Dim UR As Long, WS As Worksheet ' <<===== Aggiunta
        Set WS = ActiveSheet ' <<===== Aggiunta
       
        UR = WS.Range("A" & Rows.Count).End(xlUp).Row ' <<===== Aggiunta
        WS.Range("A2").Select '
Range("B3").Select
ActiveCell.FormulaR1C1 = "=COUNTIF($b$11:$f$" & UR ,R5C)"
For i = 11 To UR
Next i
End Sub

Codice: Seleziona tutto
Public Sub contase()
ActiveSheet.Range("b11", ActiveSheet.Range("f11").End(xlDown)).Select
UR = Selection
'Range("b6").Formula = "=$b$11:$f$ & UR;R5C"
Range("b6").FormulaR1C1 = "=b11:f & UR;R5C"
End Sub

Ho provato anche a cercare in rete ma gli esempi che ho trovato non sono stato capace ad adattarli.
excel 2007
Lyke73
Utente Junior
 
Post: 21
Iscritto il: 27/10/14 11:07

Re: Inserire convalida/elenco con Vba

Postdi ricky53 » 28/10/14 13:28

Ciao,
non facci interpretare quello che volevi fare ...

Facciamo prima se dici cosa vorresti fare e, insieme, troveremo il modo di arrivare ad una soluzione.
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. W7; Office 2003-10-13-16
Avatar utente
ricky53
Utente Senior
 
Post: 4223
Iscritto il: 11/04/09 19:29
Località: Italia

Re: Inserire convalida/elenco con Vba

Postdi Lyke73 » 28/10/14 13:41

Vorrei capire come fare ad adattare queste formule
Codice: Seleziona tutto
=SE(VAL.NON.DISP(CERCA.VERT($A$2;$A$11:$F$91622;2;FALSO));0;CERCA.VERT($A$2;$A$11:$F$91622;2;FALSO))

Codice: Seleziona tutto
=CONTA.SE($B$11:$F$50000;B$2)


Invece di scrivere un range fisso vorrei che il range da esaminare fosse variabile come per la convalida oggetto delle mia prima domanda.
Tutto questo sempre chiesto con la massima gentilezza possibile, so che sei ( siete ) qua per passione e non per lo stipendio.
excel 2007
Lyke73
Utente Junior
 
Post: 21
Iscritto il: 27/10/14 11:07

Re: Inserire convalida/elenco con Vba

Postdi ricky53 » 28/10/14 14:47

Ciao,
iniziamo con la più semplice:
Codice: Seleziona tutto
    UR = Range("B" & Rows.Count).End(xlUp).Row
    Range("D2").FormulaLocal = "=CONTA.SE($B$11:$F$" & UR & ";B$2)"


Per il valore di "UR" ho previsto di calcolare le righe dell'intervallo sulla colonna "B", in caso cambia.
LA formula viene scritta in "D2", se accorre cambia la cella.
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. W7; Office 2003-10-13-16
Avatar utente
ricky53
Utente Senior
 
Post: 4223
Iscritto il: 11/04/09 19:29
Località: Italia

Re: Inserire convalida/elenco con Vba

Postdi ricky53 » 28/10/14 14:50

Ciao,
per l'altra formula valgono le stesse regole e condizioni:
Codice: Seleziona tutto
UR = Range("A" & Rows.Count).End(xlUp).Row
Range("E2").FormulaLocal = "=SE(VAL.NON.DISP(CERCA.VERT($A$2;$A$11:$F$" & UR & ";2;FALSO));0;CERCA.VERT($A$2;$A$11:$F$" & UR & ";2;FALSO))


Calcolo di UR su colonna "A"
Scrittura formula in "E2"
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. W7; Office 2003-10-13-16
Avatar utente
ricky53
Utente Senior
 
Post: 4223
Iscritto il: 11/04/09 19:29
Località: Italia

Re: Inserire convalida/elenco con Vba

Postdi Lyke73 » 28/10/14 17:56

Mi ero quasi avvicinato...grazie dell'aiuto !
excel 2007
Lyke73
Utente Junior
 
Post: 21
Iscritto il: 27/10/14 11:07

Re: Inserire convalida/elenco con Vba

Postdi ricky53 » 29/10/14 00:17

Ciao,
è quel "QUASI" che fa la differenza ... Eh, Eh !!!

Buon proseguimento
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. W7; Office 2003-10-13-16
Avatar utente
ricky53
Utente Senior
 
Post: 4223
Iscritto il: 11/04/09 19:29
Località: Italia


Torna a Applicazioni Office Windows


Topic correlati a "Inserire convalida/elenco con Vba":


Chi c’è in linea

Visitano il forum: alfrimpa, patel e 5 ospiti