Application Vlookup/Application Match

**jeremy75018** · 19/12/2014, 21h03

Bonjour à tous,

Je reviens vers vous car mon code ne fonctionne pas....

Voici mon problème :

contexte :

J'ai deux onglets avec un tableau dans chaque qui sont dans le même classeur,

Dans la premiere feuille "BaseOPE"
en colonne A: Ordre
en colonne B: Type (colonne à remplir)

Dans la deuxième feuille "ListeOT"
en colonne A: Ordre
en colonne B: Type

J'aimerais en VBA importer le type d'Ordre dans le 1er tableau en fonction de la première Colonne (A) du second onglet

Je souhaiterai que le code se répète pour toute la colonne du second onglet

Les deux tableaux n'ont pas le même nombre de lignes

Pourriez vous y jeter un oeil

En vous remerciant d'avance

Voici mon code qui est en échec à la ligne

Code :

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

TK(R) = Join$(Application.Index(Sheets("listeOT").Columns("A:A").Value, R), "¤")

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
 With Sheets("listeOT").Cells(1).CurrentRegion.Rows
        ReDim TK$(2 To .Count)
              TD = Application.Index(.Offset(1).Resize(.Count - 1), , 2)
        For R& = 2 To .Count
            TK(R) = Join$(Application.Index(Sheets("listeOT").Columns("A:A").Value, R), "¤")
        Next
    End With
 
    With Sheets("BaseOPE").Cells(1).CurrentRegion.Rows
        ReDim TP$(2 To .Count, 0)
              TS = .Columns("A:A").Value
        For R = 2 To .Count
            v = Application.Match(Join$(Array(TS(R, 1)), "¤"), TK, 0)
            If Not IsError(v) Then TP(R, 0) = TD(v, 1)
        Next
              .Cells(2, 2).Resize(.Count - 1).Value = TP
    End With
End Sub

**Philippe Tulliez** · 20/12/2014, 09h11

Bonjour,

J'aimerais en VBA importer le domaine technique dans le 2sd tableau en fonction de la première Colonne (A) du premier onglet

Et où se trouve ce domaine technique dans ce 2ème tableau ?
La seule explication que tu donnes, c'est

Dans la deuxième feuille "ListeOT"
en colonne A: Ordre
en colonne B: Type

soit deux colonnes.

**jeremy75018** · 20/12/2014, 09h26

Bonjour Philippe,

J'ai modifié le post, effectivement après relecture, ce n'était pas très claire.

Merci

**Philippe Tulliez** · 20/12/2014, 10h34

Bonjour,

J'aimerais en VBA importer le type d'Ordre dans le 1er tableau en fonction de la première Colonne (A) du second onglet
Je souhaiterai que le code se répète pour toute la colonne du second onglet

Ce n'est toujours pas très clair.
Doit on en conclure que tu souhaites copier les cellules de la première colonne du 2ème tableau dans la première colonne du 1er tableau à condition qu'elles ne s'y trouvent pas déjà ?

**Marc-L** · 20/12/2014, 11h39

Bonjour,

je vois Jérémy que tu es parti de ma solution de ta précédente discussion
servant à utiliser une clef concaténée sur plusieurs colonnes mais cela n'a pas l'air d'être le cas ici d'où l'erreur !
Déjà juste consulter depuis une feuille de calculs l'aide de la fonction INDEX …
C'est du LEGO mais il faut savoir emboîter !

Si la feuille "ListeOT" n'a pas de doublon, une simple formule de calculs dans l'autre feuille suffit via RECHERCHEV.
Une fois qu'elle fonctionne, si vraiment un code est nécessaire, publie - la alors ici afin de t'indiquer une astuce
pour la convertir efficacement en VBA …

**jeremy75018** · 20/12/2014, 13h44

Bonjour Marc-L,

Et encore Merci!

Effectivement j'ai bien essayé d'adapter ton code....

voici le nouveau code

si vous pouviez jeter un oeil....

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
Sub BaseOPE1()
Dim oRng As Excel.Range
Dim v As Variant, lng As Long, LigFin As Variant, LigFin2 As Variant
 
LigFin = Sheets("BaseOPE").Cells(Rows.Count, 1).End(xlUp).Row
LigFin2 = Sheets("listeOT").Cells(Rows.Count, 1).End(xlUp).Row
 
Set oRng = ThisWorkbook.Worksheets("BaseOPE").UsedRange
 
v = oRng.Value
For lng = 2 To UBound(v, 1)
 
Sheets("BaseOPE").Range("B2:B" & LigFin).ClearContents
 
Sheets("BaseOPE").Range("B2:B" & LigFin).Select
 
v(lng, 2) = Application.VLookup(Range("A2:A" & LigFin), Sheets("listeOT").Range("A2:A" & LigFin2), 2, faux)
Next lng
 
oRng.Value = v
Set oRng = Nothing
v = Empty
 
 
End Sub

**Marc-L** · 20/12/2014, 13h54

Et ? Il fonctionne ou pas ? Sans explication claire et détaillée (ligne de titre ou pas, etc) …

Dans la ligne n°17, faux n'existe pas en VBA car il comprend uniquement l'anglais : False !

Edit : Et quelle est ta formule de calculs fonctionnant manuellement ?

Car à partir de la formule, moins d'une dizaine de lignes de code est nécessaire et ce, sans boucle …

**jeremy75018** · 21/12/2014, 10h49

Bonjour Marc-L,

J'ai oublié de préciser que le temps d'éxécution de ce code était interminable et plantait systématiquement le fichier...

(même en ayant modifié le faux par false)....

La formule de calcul fonctionnant correctement pour la cellule B2 est recherchev(A2,listeOT!$A$1:$D$137659,2,0),
mon souhait est de convertir cette formule en VBA et de créer une boucle pour répéter le code pour toute la colonne...

auriez-vous une solution ???

En vous remerciant d'avance

j'ai essayé de convertir la recherchev en code mais hélas sans succès

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
Sub BaseOPE1()
Dim oRng As Excel.Range
Dim v As Variant, lng As Long, LigFin As Variant, LigFin2 As Variant
 
LigFin = Sheets("BaseOPE").Cells(Rows.Count, 1).End(xlUp).Row
LigFin2 = Sheets("listeOT").Cells(Rows.Count, 1).End(xlUp).Row
 
Set oRng = ThisWorkbook.Worksheets("BaseOPE").UsedRange
 
v = oRng.Value
For lng = 2 To UBound(v, 1)
 
Sheets("BaseOPE").Range("B2:B" & LigFin).ClearContents
 
Sheets("BaseOPE").Range("B2:B" & LigFin).Select
 
v(lng, 2) = Application.VLookup(Range("A2:A" & LigFin), Sheets("listeOT").Range("A2:B" & LigFin2), 2, faux)
Next lng
 
oRng.Value = v
Set oRng = Nothing
v = Empty
 
 
End Sub

**Marc-L** · 21/12/2014, 13h28

Commencer par corriger la formule car pas besoin de quatre colonnes pour extraire les données de la deuxième !
Donc en B2 - j'en déduis donc qu'il y a une ligne de titre, est-ce si compliqué de l'expliquer ?! - nouvelle formule :

=RECHERCHEV(A2,listeOT!$A:$B,2,FAUX) …

En recopiant cette formule en B3, la référence A2 devient logiquement A3 pour que cela fonctionne.
Donc pour ne pas effectuer une formule par cellule de la colonne B, il faut passer par une formule en notation R1C1 :
sélectionner la cellule B2 puis dans le VBE - l'éditeur du VBA - dans la fenêtre Exécution (CTRL G)
entrer ? activecell.FormulaR1C1 et valider : la conversion de la formule en notation R1C1
apparait nativement en anglais en dessous.

Cette conversion est directement exploitable dans la plage de la feuille "BaseOPE" et donc sans boucle !
Deux méthodes pour calculer cette plage.

La première utilise la propriété CurrentRegion comme dans la précédente discussion :

Code :

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

1
2
3
4
5
6
7
8
Sub Demo1()
    With Worksheets("BaseOPE").Cells(1).CurrentRegion.Columns(1)
        With .Offset(1, 1).Resize(.Rows.Count - 1)
             .FormulaR1C1 = "formule convertie ici"
                 .Formula = .Value
        End With
    End With
End Sub

Pointer la première colonne évite une erreur quand la seconde est vide …

La deuxième méthode se sert de la propriété Range.End :

Code :

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

1
2
3
4
5
6
7
8
Sub Demo2()
    With Worksheets("BaseOPE")
        With Range(.Cells(2, 1), .Cells(1).End(xlDown)).Offset(, 1)
            .FormulaR1C1 = "formule convertie ici"
                .Formula = .Value
        End With
    End With
End Sub

Lorsqu'il s'agit d'une colonne discontinue - avec des trous dans les saisies - partir de la dernière cellule
de la colonne pour remonter avec la propriété End (cf l'aide VBA intégrée) sur sa dernière saisie …

Possibles améliorations en cas de lenteur :

• désactiver au préalable l'affichage via la propriété ScreenUpdating.

• Calculer la plage exacte pour la formule RECHERCHEV : il y a ce qu'il faut dans cette discussion
et consulter aussi l'aide de la propriété Range.Address, c'est comme les LEGO !

_________________________________________________________________________________________________

Merci de cliquer sur

pour chaque message ayant aidé puis sur

pour clore cette discussion …

**jeremy75018** · 22/12/2014, 13h45

Bonjour Marc-L,

Votre code fonctionne à la perfection

Encore merci pour votre aide très précieuse

Bien cordialement

**Marc-L** · 22/12/2014, 14h40

Merci du retour !

Comme quoi il est bien plus efficace de penser d'abord à une solution Excel (ici dans ce cas moins de dix lignes de code)
avant d'envisager une solution généraliste par code (une boucle s'éternisant) …

Application Vlookup/Application Match [XL-2010]

Macros et VBA Excel

Discussions similaires

Partager

Partager