VBA + Validation de donnees avec formule

Delux · 8 Octobre 2012

Bonjour a tous,

(je m'excuse pour les accents mais je suis sur un clavier QWERTY)

Quelqu'un pourrait'il m'aider a incorporer cette formule dans mon code VBA de validation de donnees?

Code:

IF(C5<>"",OFFSET($D$204:$D$459,MATCH(C5,LEFT($D$204:$D$459,LEN(C5)),0)-1,,SUM((LEFT($D$204:$D$459,LEN(C5))=C5)*1)),)

Code:

Private Sub Worksheet_Change(ByVal Target As Range)

   With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="IF(C5<>"",OFFSET($D$204:$D$459,MATCH(C5,LEFT($D$204:$D$459,LEN(C5)),0)-1,,SUM((LEFT($D$204:$D$459,LEN(C5))=C5)*1)),)"      
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = False
        .ShowError = False
    End With

Cette formule me permet de tapper ma recherche en C5 et ensuite de filtrer directement sur ce que je viens de tapper (ex: si je tappe "b", cela me sortira tous les mots commencant par "b").

J'ai essaye en passant par l'enregistreur de macro et ca me donne:

Code:

"=IF(R[4]C[2]<>"""",OFFSET(R204C4:R459C4,MATCH(R[4]C[2],LEFT(R204C4:R459C4,LEN(R[4]C[2])),0)-1,,SUM((LEFT(R204C4:R459C4,LEN(R[4]C[2]))=R[4]C[2])*1)),)"

Cependant, j'obtiens une erreur "Run-TIme Error'50290' (Application-Defined or object-defined error)

Je suis perdu la 🙁

Merci pour votre future aide precieuse

Cordialement,

Delux

job75 · 8 Octobre 2012

Re : VBA + Validation de donnees avec formule

Bonjour Delux,

Delux à dit:
Je suis perdu la 🙁

Et sur les posts #17 et #18 de ce fil aussi sans doute :

https://www.excel-downloads.com/thr...ne-si-couleur-de-la-cellule-est-rouge.193183/

Chaque chose en son temps.

A+

Delux · 8 Octobre 2012

Re : VBA + Validation de donnees avec formule

job75 à dit:
Bonjour Delux,

Et sur les posts #17 et #18 de ce fil aussi sans doute :

https://www.excel-downloads.com/thr...ne-si-couleur-de-la-cellule-est-rouge.193183/

Chaque chose en son temps.

A+

Bonjour Job75,

Non je ne suis plus perdu pour ce code la, meme si j'ai encore du mal a decortiquer ce code ^^'

Sur ce nouveau post, j'essaye une solution pour contourner mon probleme : la validation de donnee ne se lance pas au demarrage de mon fichier excel :/
Il faut que j'aille dans le menu excel et que je clique sur "OK" (dans l'option validation de donnee) pour qu'elle se lance et apres elle fonctionne correctement.

C'est pour cela que j'ai pense au VBA pour la lancer au demarrage, ou en cliquant sur la cellule.
Si vous avez une solution sur ce probleme, je suis preneur 😉

Encore merci pour l'autre post 😉

Cordialement,

Delux

job75 · 8 Octobre 2012

Re : VBA + Validation de donnees avec formule

Re,

job75 à dit:
Chaque chose en son temps.

Je commencerai donc à m'intéresser à ce fil quand vous aurez pris le temps d'indiquer, sur l'autre fil, quel est l'impact de ma dernière macro sur la durée du traitement de votre fichier réel.

Quand on a fait un travail, les non-retours sont frustrants.

A+

Delux · 8 Octobre 2012

Re : VBA + Validation de donnees avec formule

Re,

Je n'avais pas compris que vous desiriez un feedback concernant le temps de traitement.
Maintenant c'est chose faite 😉
En tout cas, cette macro est parfaite pour mon tableau. J'aurais bien aime un peu plus d'explications pour chaque ligne de code histoire de pouvoir la maitriser.

Mais encore une fois, un grand MERCI a vous pour votre temps et votre aide.

Cordialement,

job75 · 8 Octobre 2012

Re : VBA + Validation de donnees avec formule

Re,

Merci pour le retour sur l'autre fil 🙂

Quant à ce fil, voici une solution :

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
'fonctionne correctement si D204:D459 a été triée
Dim cel As Range, t$, F1$, F2$
Set cel = [C5] 'à adapter éventuellement
If Not Intersect(Target, cel) Is Nothing Then
  t = cel.Address & "&""*""" 'adresse et caractère générique *
  F1 = "OFFSET(D204:D459,MATCH(" & t & ",D204:D459,0)-1,,COUNTIF(D204:D459," & t & "))"
  On Error Resume Next 'si Evaluate renvoie une valeur d'erreur
  F2 = "=" & Evaluate(F1).Address 'formule plus simple...
  With cel.Validation
    .Delete
    If cel = "" Or F2 = "" Then Exit Sub
    cel.Select 'pour qu'on voit que la liste est disponible
    .Add xlValidateList, xlValidAlertStop, xlBetween, F2
    .ShowError = False
  End With
End If
End Sub

Remarques :

- votre formule était une formule matricielle, la formule F1 ne l'est pas : la recherche (MATCH) et le comptage (COUNTIF) utilisent le caractère générique *

- la formule de validation utilisée est simplement l'adresse de la plage déterminée par Evaluate(F1)

- pour que la liste obtenue soit correcte il faut bien entendu que la plage D204: D459 soit en ordre alphabétique (triée) mais ça c'est à vous de voir.

A+

job75 · 8 Octobre 2012

Re : VBA + Validation de donnees avec formule

Re,

Voici une manière plus classique (en VBA) pour créer les formules de validation.

En outre une boucle permet de traiter les cellules d'une plage en cas d'entrées ou effacements multiples :

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
'fonctionne correctement si [Reference] a été triée
Dim plage As Range, r As Range, cel As Range
Dim ref As Range, t$, lig As Variant, h&, F$
Set plage = [C5:C30] 'à adapter
Set r = Intersect(Target, plage)
If Not r Is Nothing Then
  For Each cel In r
    With cel.Validation
      .Delete
      If Not IsEmpty(cel) Then
        Set ref = [Reference] 'à adapter éventuellement
        t = cel & "*" 'caractère générique *
        lig = Application.Match(t, ref, 0)
        If IsNumeric(lig) Then
          h = Application.CountIf(ref, t)
          F = "=" & ref(lig).Resize(h).Address
          .Add xlValidateList, xlValidAlertStop, xlBetween, F
          .ShowError = False
        End If
      End If
    End With
  Next
  r.Select 'facultatif
End If
End Sub

Fichier joint.

A+

job75 · 8 Octobre 2012

Re : VBA + Validation de donnees avec formule

Re,

Eh eh la plage Reference n'était pas triée alphabétiquement.

Fichier (2).

Edit : Je ne sais pas pourquoi le fichier s'était vérolé, je l'ai refait.

A+

job75 · 8 Octobre 2012

Re : VBA + Validation de donnees avec formule

Re,

Si h = 1 la liste de validation est inutile.

Fichier (3).

Bonne nuit.

Delux · 9 Octobre 2012

Re : VBA + Validation de donnees avec formule

Bonjour et Merci pour votre implication 🙂

Votre fichier test est une veritable merveille.
Cependant lorsque j'applique ce code + les Macros 2 et 3 dans mon fichier, une erreur se produit :

"Compile error : Variable not defined" et me surligne en jaune

Code:

Private Sub Worksheet_Change(ByVal Target As Range)

J'ai pu faire un fichier exemple un peu plus petit pour que vous puissiez y jeter un coup d'oeil 😉
J'ai renomme la plage [Reference] par [Packages].
J'ai egalement modifie set plage = [C5:C30] par [C5].

En tout cas encore un grand merci 😉

Cordialement,

Delux

Delux · 9 Octobre 2012

Re : VBA + Validation de donnees avec formule

Bon j'ai reussi a modifier le code par celui-ci:

Code:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
'fonctionne correctement si [Reference] a été triée

Dim plage As Range, r As Range, cel As Range
Dim ref As Range, t$, lig As Variant, h&, F$
Set plage = Range("C5") 'à adapter
Set r = Intersect(Target, plage)
If Not r Is Nothing Then
  For Each cel In r
    With cel.Validation
      .Delete
      If Not IsEmpty(cel) Then
        Set ref = [Packages] 'à adapter éventuellement
        t = cel & "*" 'caractère générique *
        lig = Application.Match(t, ref, 0)
        If IsNumeric(lig) Then
          h = Application.CountIf(ref, t)
          If h > 1 Then
            F = "=" & ref(lig).Resize(h).Address
            .Add xlValidateList, xlValidAlertStop, xlBetween, F
            .ShowError = False
          End If
        End If
      End If
    End With
  Next
  r.Select 'facultatif
End If

 
'Macro that hide blanks lines for the Master Spec section

Application.ScreenUpdating = False


  If Target.Address <> "$C$5" And Len(Target.Value) > 1 Then Exit Sub
  For lig = 15 To 18 'modification de l par lig
     Rows(lig).Hidden = Cells(lig, "C") = "" 'modification de l par lig
  Next
  Range("C5").Select
End Sub

Cependant, il persiste une erreur:

"Run-Time error '13': Type Mismatch"

Cette erreur me souligne

Code:

Rows(lig).Hidden = Cells(lig, "C") = "" 'modification de l par lig

La chose etrange est que ce code fonctionne a merveille sur le fichier joint exemple mais pas sur mon fichier original 🙁

Cordialement,

Delux · 9 Octobre 2012

Re : VBA + Validation de donnees avec formule

Re,

En fait j'ai reussi a trouver l'erreur de mon fichier original.
Une formule appliquer une valeur #N/A ce qui ne pouvait pas aller avec

Code:

Rows(lig).Hidden = Cells(lig, "C") = "" 'modification de l par lig

J'ai donc juste modifie la formule avec un IFERROR et cela fonctionne.

Je ne marque pas encore ce poste resolu tant que je ne suis pas sur que tout fonctionne a merveille.

En tout cas, encore un grand merci a vous Job75.

Pourriez-vous me faire une derniere faveur et me mettre la description de chaque ligne de code pour que je comprenne la logique et l'enchainement de ce code afin de le maitriser a la perfection?

Merci 🙂

Cordialement

job75 · 9 Octobre 2012

Re : VBA + Validation de donnees avec formule

Bonjour Delux,

Sur votre fichier joint j'ai adapté/corrigé les macros

1) Même avec h = 1 il faut garder la liste de validation si son texte est différent (casse comprise) du texte en C5 :

Code:

If h > 1 Or cel <> ref(lig) Then

2) Quand on efface C5 la flèche de liste ne disparaissait pas immédiatement, il faut déplacer la sélection :

Code:

'mise à jour pour la flèche
cel.Offset(1).Select: cel.Select

3) Votre macro simplifiée :

Code:

'Macro that hide blanks lines for the Master Spec section
For Each cel In [C15:C18]
  cel.EntireRow.Hidden = cel.Text = ""
Next

4) Ma macro n'est pas très difficile à comprendre, utilisez au maximum l'aide VBA.

Et si une expression reste mystérieuse, dites-le moi.

A+

Delux · 9 Octobre 2012

Re : [RESOLU] VBA + Validation de donnees avec formule

Job75,

Merci a vous pour ce magnifique Code VBA.
Effectivement, je vais me servir de l'aide pour en savoir plus 😉

En tout cas tout fonctionne tres bien, le temps d'execution est parfait et la simplification de ma macro pour cacher les lignes fonctionne a merveille.

J'ai encore besoin de pratique, mais j'espere arriver un jour a votre niveau.

Encore merci pour votre aide 😉

Cordialement,

Delux

Effectuez une recherche sur Excel Downloads...

VBA + Validation de donnees avec formule

Delux

XLDnaute Occasionnel

job75

XLDnaute Barbatruc

Delux

XLDnaute Occasionnel

job75

XLDnaute Barbatruc

Delux

XLDnaute Occasionnel

job75

XLDnaute Barbatruc

job75

XLDnaute Barbatruc

Pièces jointes

job75

XLDnaute Barbatruc

Pièces jointes

job75

XLDnaute Barbatruc

Pièces jointes

Delux

XLDnaute Occasionnel

Pièces jointes

Delux

XLDnaute Occasionnel

Pièces jointes

Delux

XLDnaute Occasionnel

job75

XLDnaute Barbatruc

Pièces jointes

Delux

XLDnaute Occasionnel

Discussions similaires

Nous accordons de l'importance à votre vie privée