Aide pour la création d'une fonction VBA Résolu/Fermé

Question

Bonjour à tous,

Novice en VBA je suis un peu perdu quant à la création de fonctions. 

En fait j'ai une feuille Excel n1 ou j'entre un numéro de carte de travail dans la colonne "A". J'aimerais voir si ce numéro se trouve dans la base de donnée qui se trouve sur la feuille Excel n2  et qui contient le numéro de carte de travail et des infos supplémentaires comme les Man Hours, le materiel nécessaire, l’effectivité…… (dans cette feuille n2 la colonne "A" correspond également au numéro de carte de travail, la colonne “B” au materiel, la “C” au Man hours,……). J'ai réussi à créer une fonction VLOOKUP qui me permet uniquement de retrouver le numéro de carte et de le copier sur la feuille n1. 

Je voudrais une fonction VBA qui me permette d’extraire de la base de donnée non pas seulement le numéro de carte mais toutes les infos qui lui sont attribuées. 

Merci d’avance pour votre aide.

+++

Ivan-hoe · Answer

Bonjour Estelle, 
a priori, si tu as réussi à retrouver le n° de carte avec une fonction VLookUp, tu as fait le plus dur ! 
Pour retrouver les autres éléments, tu n'as plus qu'à faire varier le 3è argument de ta fonction (celui qui correspond au n° de la colonne dans la plage de données) : 1 pour le n° de carte, 2 pour le matériel, 3 pour les Man Hours, etc.
 
Si c'est plus compliqué que ça, peux-tu re-préciser ce qui te pose problème ? 
A suivre...

Estelle 68* · Answer

Bonjour,

Merci pour vos conseils j'ai réussi à créer la fonction: Le problème avec la fonction VLOOKUP c'est qu'il faut étendre les cellules pour qu'elle affecte toute la feuille Excel et la ou il n'y a pas de numéro de carte j'obtiens la réponse N/A. J'aurais donc encore des questions car je souhaite automatiser cela de telle sorte que l'utilisateur ait uniquement besoin de rentrer le numéro de carte et que les infos liées apparaissent sans avior besoin de copier a chak fois la ligne de formule vers le bas.

1. Me serait il possible d'automatiser à l'aide d'une macro cad plus de VLOOKUP dans chaque case  mais une fonction VBA?

2. Sinon comment faire pour éviter que l'indication N/A apparaisse?  

Merci de votre soutient et bon ap.

Ivan-hoe · Answer

Salut Estelle, 
à ce stade-là de la discussion, est-il possible que tu nous montres ton fichier (au moins, une copie sans donnée confidentielle) ? Tu peux le mettre sur http://www.cijoint.fr/
Que veux-tu dire par "automatiser à l'aide d'une macro cad plus de VLOOKUP dans chaque case mais une fonction VBA" ? Tu voudrais avoir directement le résultat dans ta cellule, sans qu'apparaisse la formule, c'est ça ? 
Qu'as-tu écrit comme code jusqu'ici ? 
Je te demande tout ça parce que c'est plus facile de corriger à partir de ce que tu as déjà fait que de répondre dans le vide. 
A suivre...

Estelle 68* · Answer

Voila c'est fait je viens de déposer le fichier dont l'intitulé est :  Material Planning Tool.xls 

"Tu voudrais avoir directement le résultat dans ta cellule, sans qu'apparaisse la formule, c'est ça ? 
Qu'as-tu écrit comme code jusqu'ici ?" 

La réponse est Oui je veux ke le résultat apparaisse sans la formule.. Tu peu voir le code sur le fichier .xls.
En fait la 1ère feuille du fichier contient la base de donnée etsur  la 2ème les réponses avec en colonne C la case a compléter. J'aimerais donc ke l'utilisateur rentre le numéro de carte en colonne C et que que toutes les infos correspondantes contenues dans la base de données s'affichent dans les colonnes qui suivent.

Ivan-hoe · Answer

Chère Estelle, 
quand tu as déposé ton fichier sur cijoint.fr, on a dû te donner un lien avec un nom affeux du genre cijoint.fr/cjlink.php?file=cj240726/cij8ZZ4VDTR.xls
C'est ce lien qu'il nous faudrait pour retrouver ton fichier.
I.

Estelle 68* · Answer

Voici le lien

http://www.cijoint.fr/cjlink.php?file=cj200806/cijH19WWD0.xls.

Ivan-hoe · Answer

Merci Estelle, c'est beaucoup plus clair avec le fichier sous les yeux (bien que je ne comprenne absolument rien à son contenu !)
Je te suggère les quelques modifications suivantes, pour que ça soit (beaucoup) plus pratique : 
1° - tu donnes un nom à la plage A:A de ta feuille "MDP Section...". Par exemple, "CODES"
2° - tu donnes un nom à la plage A:K de ta feuille "MDP Section...". Par exemple, "DONNEES"

3° - on va restreindre le contenu de la colonne C de la feuille résultat. 
Données/Validation
Autoriser : Liste (ici, tu peux ou non cocher Liste déroulante dans la cellule)
Source : =CODES
D'une part, ça permet de retrouver les codes dans une liste déroulante (ça évite de les saisir) ; 
d'autre part, ça évitera les #N/A puisqu'on ne pourra pas saisir un code qui n'existe pas. 
A suivre...

Ivan-hoe · Answer

4° - avec VBA, la fonction RECHERCHEV s'écrit presque comme dans Excel : 
 =Application.WorksheetFunction.VLookup(LaValeur, LaPlage, N°Colonne, False)
Comme on doit l'écrire 10 fois de suite dans 10 colonnes différentes, on va faire une boucle en faisant varier le n° de la colonne. En plus, comme tu le souhaites, on va l'écrire de manière automatique chaque fois que tu saisiras un code dans la colonne C (la colonne 3). 
Clique droit sur ton onglet "Résultats"/ Visualiser le code et colle la procédure ci-dessous : 
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Target.Column = 3 Then Exit Sub
On Error Resume Next
Application.EnableEvents = False
Application.ScreenUpdating = False

With Target
    For i = 1 To 10
        .Offset(0, i).Value = Application.WorksheetFunction.VLookup _
                    (.Value, Sheets(1).Range("DONNEES"), i + 1, False)
    Next i
End With

Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
On utilise ici la plage DONNEES, c'est plus pratique que de taper ses coordonnées. 
Pour que ça marche, il faudra évidemment Activer les Macros à chaque ouverture de ton classeur.
Est-ce que ça te convient ?  
A suivre...

Estelle 68* · Answer

Bonjour Ivan Hoe,

Oui cela me convient tout a fait ca fonctionne a merveille... Maintenant j'aimerais bien comprendre + en profondeur la manière de formuler les fonctions VBA. Pourrais tu me passer un lien ?

Merci.

Ivan-hoe · Answer

Chouette ! 
je ne connais pas trop les sites qui traitent de la question. Sur CMM, on recommande souvent www.excel-vba.com
Tu peux aussi taper "VBA Excel" dans un moteur de recherche. 
Moi je te recommande l'aide en ligne (touche F1) et tu tapes worksheetfunction dans la rubrique d'aide. 
I.

Estelle 68* · Answer

Re Ivan-hoe,

Help! Help! 
J'ai presk fini mais j'aurais encore 1 dernière question. J'ai ajouté kk champs supplémentaires dans la base de données (Material, QTY, Unit,...) mais ils ne s'affichent pas tous dans les résultats, juste ceux de la 1ère ligne... 

Voici le lien pour accéder au fichier 

http://www.cijoint.fr/cjlink.php?file=cj200806/cijkgjam72.xls. 


Merci d'avance pour ton aide.

Ivan-hoe · Answer

Ah ! ça c'est normal, avec la fonction Recherche, on ne peut aller chercher que la ligne qui correspond au code d'entrée (en l'occurence, la première ligne). 
Ce que tu peux faire, c'est rassembler toutes tes données dans une seule ligne :  
dans la feuille "MDP...", au lieu d'avoir Cleaner en L51 et Compound NeverSeez en L52, tu mets tout en L51, avec un retour à la ligne (Alt + Entrée) : 
Cleaner Alt+Entrée Compound NeverSeez Alt+Entrée Coalescer Bag Alt+Entrée O-Ring
Dans la feuille "Résultats", si tu ajustes bien la largeur de la colonne N, tu devrais retrouver tes données avec les retours à la ligne au bon endroit.
A suivre...

Estelle 68* · Answer

N'y a t il pas un moyen de conserver ces paramètres. En fait si je veux uploader le matériel dans SAP il faut absolument que chaque numéro de pièce corresponde à 1 ligne donc je ne peux pas tout regrouper dans 1 cellule.

Peut etre peut on procéder avec une autre formule que RECHERCHEV??

Ivan-hoe · Answer

Puisque, dans la feuille "résultats", on ne fait que reporter dans l'ordre toutes les colonnes correspondant à une donnée d'entrée, alors oui, tu as raison, on peut très bien ne pas utiliser RechercheV et se contenter de recopier les valeurs. 
Ainsi, s'il y a 4 lignes (par exemple, pour le 21-BJJ-02), on recopie dans "Résultats" les 4 lignes de "MPD..."
 
Modifications apportées : 
- on compte le nombre de lignes pour une référence (grâce aux cellules fusionnées)
- quand il y a plusieurs lignes pour une référence, on insère des lignes dans "Résultats" pour ne pas écraser les lignes en-dessous. Par contre, ça fiche le bazar si tu remplaces une référence à 4 lignes par une référence à 1 ligne (fais des essais, tu verras)
 - j'ai mis le nombre de colonnes en constante (16), pour éviter de faire une boucle jusqu'à 100 par défaut comme tu l'avais fait. Si le nombre de colonnes change, il faut changer cette constante. Si tu le souhaites, sache que c'est aussi possible de le calculer automatiquement. 
J'espère que ça te convient mieux. Je n'ai pas tous les tenants et aboutissants entre les mains mais ce qu'on a fait jusqu'ici, on aurait très bien pu le faire simplement avec un filtre dans la feuille "MDP Section..." !!!!
Option Explicit
Const NbCol As Integer = 16

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Target.Column = 3 Then Exit Sub

Dim Reference As Range, NbPiece As Integer, i As Integer, j As Integer
On Error GoTo FIN
Application.EnableEvents = False
Application.ScreenUpdating = False

With Target
    Set Reference = Sheets(1).Cells.Find(what:=.Value)
    If Reference Is Nothing Then
        MsgBox "erreur sur la référence " & .Value
    Else
        Sheets(1).Activate: Reference.Select
        NbPiece = Selection.Rows.Count
    End If
        
    Me.Activate
    If NbPiece > 1 Then
        Range(.Offset(1, 0), .Offset(NbPiece - 1, 0)).EntireRow.Insert (xlDown)
    End If
    
    For i = 0 To NbPiece - 1
        .Offset(i, 0).Value = .Value
        For j = 1 To NbCol - 1
            .Offset(i, j).Value = Sheets(1).Cells _
                        (Reference.Row + i, Reference.Column + j).Value
            .EntireRow.AutoFit
        Next j
    Next i
    
End With

FIN:
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

Aide pour la création d'une fonction VBA

14 réponses

Discussions similaires