Discussion :

[rogerlette]

Bonjour à tous les sauveurs du forum !


J'essaye de faire un fonction qui à chaque chargement d'une feuille remplie les 2000 première cellules de la première colonne avec un liste de validation qui se trouve sur une autre feuille.

Et à force d'essayer j'ai réussi ! Et ça donne ça :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
Private Sub Worksheet_Activate()
Application.Calculate
 
plage = "=" & "'" & "Base de données" & "'" & "!" & Range("A6:A" & Sheets("Base de données").Range("A65536").End(xlUp).Row).Address
 
With Sheets("Etude de prix")
 
 
    With Range("A6:A2000").Validation
    .Delete 'suppression de l'ancienne plage de validation puis création d'une nouvelle avec les dernieres valeurs
    .Add Type:=xlValidateList, Operator:=xlBetween, Formula1:= plage
    .IgnoreBlank = True
    .InCellDropdown = True
    .InputTitle = ""
    .ErrorTitle = ""
    .InputMessage = ""
    .ErrorMessage = ""
    .ShowInput = True
    .ShowError = False
    End With
End With
 
End Sub

Mais.... car il y a toujours un problème ! Je voudrai ajouter à ma liste de validation une saisie semi automatique des données.
J'ai donc remplacé

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Formula1:= plage

par

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Formula1:="=DECALER(plage;EQUIV(A1&""*"";plage;0)-1;0;NB.SI(plage;A1&""*""))"

Sauf que ce code trouvé sur le net marche très bien pour la cellule A1, mais moi je dois l'adapter pour que ça s'applique à A6 j'usqu'à A2000. Et je n'y arrive pas....

J'ai essayé de remplacer le With Range("A6:A2000") par une boucle avec un variable i qui s'incrémente et une formule comme ca:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Formula1:="=DECALER(plage;EQUIV("A"&"i"&""*"";plage;0)-1;0;NB.SI(plage;"A"&"i"&""*""))"

Mais impossible d'arriver à un truc qui fonctionne après de nombreuses heures d'essais... grrr

Merci de votre aide

[mercatog]

Sans aucune garantie, j'ai fais entrer la variable i à la place du 1.
Je doute que ça va donner le résultat

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Formula1:= "=DECALER(plage;EQUIV(A" & i & """*"";plage;0)-1;0;NB.SI(plage;A" & """*""))"

[rogerlette]

Merci pour la réponse,

J'avais déjà essayé diverses choses sur le principe de ce que vous m'avez répondu, j'ai essayé de persévérer un peu plus mais voilà ou j'en suis ;

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
Private Sub Worksheet_Activate()


Application.Calculate

plage = "=" & "'" & "Base de données" & "'" & "!" & Range("A6:A" & Sheets("Base de données").Range("A65536").End(xlUp).Row).Address

With Sheets("Etude de prix")



   For i = 6 To 2000
    Cells(i, 1).Validation
    .Delete 'suppression de l'ancienne plage de validation puis création d'une nouvelle avec les dernieres valeurs
    .Add Type:=xlValidateList, Operator:=xlBetween, Formula1:= _
        "=DECALER(plage;EQUIV(A&i&""*"";plage;0)-1;0;NB.SI(plage;A&i&""*""))"
    .IgnoreBlank = True
    .InCellDropdown = True
    .InputTitle = ""
    .ErrorTitle = ""
    .InputMessage = ""
    .ErrorMessage = ""
    .ShowInput = True
    .ShowError = False
    'End With
    Next
End With

End Sub

J'ai donc fait une boucle For au lieu de With.

Mais

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Cells(i, 1).Validation

mais j'ai une erreur ici car la méthode validation ne marche pas avec Cells. Quand j'essaye de le faire avec Range je n'arrive pas à trouver une syntaxe qui fonctionne....


Je suis un peu perdu...

[mercatog]

essaies ceci

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
Dim Plage As String, Frml As String
Dim i As Integer
 
Plage = "'Base de données'!" & Sheets("Base de données").Range("A6:A" & Sheets("Base de données").Range("A65536").End(xlUp).Row).Address
With Sheets("Etude de prix")
    For i = 6 To 10
        Frml = "=DECALER(" & Plage & ";EQUIV(A" & i & "&""*"";" & Plage & ";0)-1;0;NB.SI(" & Plage & ";A" & i & "&""*""))"
        MsgBox Frml
        With .Cells(i, 1).Validation
            .Delete
            .Add Type:=xlValidateList, Operator:=xlBetween, Formula1:=Frml
        End With
    Next i
End With

sur le msgbox, vérifie la valeur de Frml

[rogerlette]

Merci mercatog,

J'ai testé ton code mais il y a une erreur d'exécution 1004 : erreur définie par l'application ou par l'objet sur la ligne :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

.Add Type:=xlValidateList, Operator:=xlBetween, Formula1:=Frml

Pourtant la msgbox semble afficher la bonne formule (Voici exactement ce qu'elle affiche j'ai vérifié 3 fois !) :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=DECALER('Base de données'!$A$6:$A$20;EQUIV(A6&"*";Base de données'!$A$6:$A$20;0)-1;0;NB.SI('Base de données'!$A$6:$A$20;A6&"*"))

[rogerlette]

Bonjour,


Alors je m'excuse, le début de mon post est pas bon, désespéré j'ai repris du début et je me suis aperçu que le code cité dans le premier post avec simplement le remplacement de

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Formula1:= plage

par

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Formula1:="=DECALER(plage;EQUIV(A1&""*"";plage;0)-1;0;NB.SI(plage;A1&""*""))"

ne fonctionne pas.

Je sais pas ou est l'erreur car je suis sur de l'avoir déjà fait fonctionner au moment ou j'ai posté ce message. Peut être une simple erreur de copier coller. Je vais donc essayer de refaire marcher ça avant tout.

Et avec ce que tu m'a mis mercatog j'espère pouvoir ensuite faire ce que je veu.

Dans tous les cas je vous tiens au courant.

[rogerlette]

Bonjour à tous,


Je viens de passer la matinée sur mon problème et n'arrivant à pas à mon but je vous décris ce qui ne vas pas ;

J'ai enregistré un macro qui créer une liste de validation avec saisie semi auto depuis une autre feuille, ça donne ça :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
Sub Macro3()
 
' Macro3 Macro
 
    Sheets("Base de données").Select
    Range("B2:B30").Select
    ActiveWorkbook.Names.Add Name:="Liste", RefersToR1C1:= _
        "=Données!R2C2:R30C2"
    Sheets("Etude de prix").Select
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:= _
        "=DECALER(Liste;EQUIV(A1&""*"";Liste;0)-1;0;NB.SI(Liste;A1&""*""))"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = False
    End With
End Sub

Je l'ai mise dans le code de ma feuille "Etude de prix" sur l'évènement worksheet_activate comme ceci :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
Private Sub Worksheet_Activate()
 
    Sheets("Base de données").Select
    Range("B2:B30").Select
    ActiveWorkbook.Names.Add Name:="Liste", RefersToR1C1:= _
        "=Données!R2C2:R30C2"
    Sheets("Etude de prix").Select
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:= _
        "=DECALER(Liste;EQUIV(A1&""*"";Liste;0)-1;0;NB.SI(Liste;A1&""*""))"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = False
    End With

Ça ne marchais pas ... alors j'ai fait ;

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
Private Sub Worksheet_Activate()
 
' PARTIE 1 : Nomme "Liste" la plage qui contient la liste des noms de la liste de validation
    ActiveWorkbook.Names("Liste").Delete
    ActiveWorkbook.Worksheets("Base de données").Range("B2:B30").Name = "Liste"
 
'PARTIE 2 : Création de la liste déroulante avec saisie semi auto sur la case A1 contenant "Liste"
    Sheets("Etude de prix").Select
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:= _
        "=DECALER(Liste;EQUIV(A1&""*"";Liste;0)-1;0;NB.SI(Liste;A1&""*""))"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = False
    End With
 
End Sub

La PARTIE 1 qui nomme "Liste" la plage contenant des données fonctionne bien (Si je sélectionne ma plage dans la feuille elle porte bien le nom "Liste".
Je l'ai complété en mettant ceci :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
'Nomme "Liste" la plage qui contient la liste des noms de la liste de validation
    ActiveWorkbook.Names("Liste").Delete
    ActiveWorkbook.Worksheets("Base de données").Range("B2:B" & Sheets("Base de données").Range("B65536").End(xlUp).Row).Name = "Liste"

Ce qui marche très bien aussi.

En revanche j'ai toujours une erreur d’exécution 1004 sur la PARTIE 2 plus précisément sur cette ligne ;

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:= _
        "=DECALER(Liste;EQUIV(A1&""*"";Liste;0)-1;0;NB.SI(Liste;A1&""*""))"

Je comprend pas car elle est donnée par l'enregistreur... ?? (Ca c'est ma question )

Et j'aimerai ensuite modifier LA PArTIE 2 par la proposition de mercatog (adaptée) pour pouvoir appliquer ca pas seulement à la cellule A1 mais à une plage en la remplaçant par ceci;

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
Dim Plage As String, Frml As String
Dim i As Integer
 
With Sheets("Etude de prix")
    For i = 6 To 10
        Frml = "=DECALER(Liste;EQUIV(A" & i & "&""*"";Liste;0)-1;0;NB.SI(Liste;A" & i & "&""*""))"
         With .Cells(i, 1).Validation
            .Delete
            .Add Type:=xlValidateList, Operator:=xlBetween, Formula1:=Frml
        End With
    Next i
End With

Voilà je suis au bord du gouffre, prêt à me jeter tout en me pendant à une corde et après m’être taillé les veines,

Merci d'avance.

[rogerlette]

Bonjour,


Je reviens à la charge avec mon problème! Tous les jours je trouve un petit truc de plus mais sans que cela contribue au succès de l'affaire.

Alors voici un fichier qui présente deux méthodes de saisie semi auto à la main (Si ça peut aider...). Il y a la formule que j'utilise mais aussi celle là qui semble marcher (manuellement);

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

"=SI(A1<>"";DECALER(Liste;EQUIV(A1;GAUCHE(Liste;NBCAR(A1));0)-1;;SOMME((GAUCHE(Liste;NBCAR(A1))=A1)*1));)"

J'ai donc essayé d'obtenir le code à l'aide de l'enregistreur.

Mais que neni ! Je sais pas ou est le problème mais comme pour l'autre formule j'ai toujours une erreur à ce niveau là :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Formula1:="=SI(A1<>"";DECALER(Liste;EQUIV(A1;GAUCHE(Liste;NBCAR(A1));0)-1;;SOMME((GAUCHE(Liste;NBCAR(A1))=A1)*1));)"

Je me demande si il existe un SuperVBA, ou une VBAgencetousrisques ?

[mercatog]

Sans aucune garantie
par hasard si tu essaies de traduire en anglais

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Formula1:="=IF(A1<>"",OFFSET(..."

Edit:
Effectivement: la formule doit être en anglais

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
Private Sub Worksheet_Activate()
Dim i As Integer
Dim frml As String
 
For i = 2 To 10
    With Range("A" & i)
        frml = "=OFFSET(No,MATCH(" & .Address(0, 0) & "&""*"",No,0)-1,0,COUNTIF(No," & .Address(0, 0) & "&""*""))"
        With .Validation
            .Delete
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=frml
        End With
    End With
Next i
End Sub

[rogerlette]

Good morning mercatog,

Un très grand merci c'étais donc bien le fait que la formule soit en français qui causais le problème.

Voici le code complet pour les gens qui voudraient faire la même chose :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
Private Sub Worksheet_Activate()
 
'Nomme "Liste" la plage qui contient la liste des noms de la liste de validation
    ActiveWorkbook.Names("Liste").Delete
    ActiveWorkbook.Worksheets("Données").Range("B2:B" & Sheets("Données").Range("B65536").End(xlUp).Row).Name = "Liste"
 
 
'PARTIE 2 : Création de la liste déroulante avec saisie semi auto sur la case A1 contenant "Liste"
Dim i As Integer
Dim frml As String
For i = 1 To 15
    With Range("A" & i)
        frml = "=OFFSET(Liste,MATCH(" & .Address(0, 0) & "&""*"",Liste,0)-1,0,COUNTIF(Liste," & .Address(0, 0) & "&""*""))"
        With .Validation
            .Delete
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=frml
            .IgnoreBlank = True
            .InCellDropdown = True
            .InputTitle = ""
            .ErrorTitle = ""
            .InputMessage = ""
            .ErrorMessage = ""
            .ShowInput = True
            .ShowError = False
        End With
    End With
Next i
 
 
End Sub

Ce code fonctionne avec le fichier fournit plus haut, il me reste à l'adapter dans le code de mon projet global en espérant que cela fonctionne aussi bien.

En tout cas tu m'enlève une grosse épine du pied et je vais pouvoir dormir tranquille !

Merci, merci et encore merci.