formule index + equiv en vba et copie sur toutes les lignes non vides

**roch2a** · 09/03/2018, 15h15

Bonjour, une nouvelle fois je viens solliciter votre aide

Voici une formule index equiv que j'ai dans un tableau en feuille1 de mon classeur :

=INDEX(Feuil2!$L$6:$N$16;EQUIV(Feuil1!M2;Feuil2!$L$6:$L$16;0);2) en colonne S

et la même formule avec l'enregistreur de macro :

Code :

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

1
2
 ActiveCell.FormulaR1C1 = _
        "=INDEX(Feuil2!R6C12:R16C14,MATCH(Feuil1!RC[-6],Feuil2!R6C12:R16C12,0),2)"

puis je tire cette formule jusqu'à la dernière ligne non vide mon tableau en feuille 1 :

Code :

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

1
2
3
4
5
6
7
8
Sheets("Feuil1").Activate
Cells(2, 19).Select
 
'    ActiveCell.FormulaR1C1 = _
'        "=INDEX(Feuil2!R6C12:R16C14,MATCH(Feuil1!RC[-6],Feuil2!R6C12:R16C12,0),2)" 'formule enregistrée avec l'enregistreur de macro
'    Range("S2").Select
'    Selection.AutoFill Destination:=Range("S2:S3563") 'copie colle la formule jusqu'à la dernière ligne non vide du tableau
'    Range("S2:S3563").Select

Mon problème réside dans le fait que : ma plage Feuil2!R6C12:R16C14 ou Feuil2!$L$6:$N$16 et ma colonne Feuil2!R6C12:R16C12 ou Feuil2!$L$6:$L$16 doivent être des plages dynamiques.

j'ai donc écris le code suivant avec l'aide de certains d'entre vous et mes très modestes connaissances :

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
Sub test()
 
'
' Macro4 Macro récuperer les montants du tableau croisé dynamique en feuille 2 pour les injecter dans la feuille 1
'=INDEX(Feuil2!$L$6:$N$16;EQUIV(Feuil1!M2;Feuil2!$L$6:$L$16;0);2)
 
Dim Dernlign As Long
DernLigne = Range("A" & Rows.Count).End(xlUp).Row 'jusqu'à la dernière ligne non vide de la feuille 1
 
Dim DernLigne1 As Long
DernLigne1 = Range("A" & Rows.Count).End(xlUp).Row 'jusqu'à la dernière ligne non vide de la feuille 2
 
Dim maPlageDynamiq As Range
Dim DernLigneDynamiq As Long, DernColonneDynamiq As Integer
    'dernière ligne de la colonne L de la feuille 2
DernLigneDynamiq = Sheets("Feuil2").Range("L" & Rows.Count).End(xlUp).Row - 1  'ne selectionne pas la ligne total général ni les entêtes
    'dernière colonne de la ligne 6 de la feuille 2
DernColonneDynamiq = Sheets("Feuil2").Cells(6, Cells.Columns.Count).End(xlToLeft).Column  'première colonne du tableau croisé dynamique en feuille 2
Set maPlageDynamiq = Sheets("Feuil2").Range(Cells(6, 12), Cells(DernLigneDynamiq, DernColonneDynamiq)) 'plage dynamique de référence pour la fonction index/match

mais je suis malgré beaucoup d'efforts incapable d'écrire la suite du code où pour ma formule index/equiv citée plus haut :

maPlagedynamiq doit remplacer Feuil2!R6C12:R16C14.
DerColonneDynamiq doit remplacer Feuil2!R6C12:R16C12.
et recopier la formule sur chaque ligne jusqu'à la dernière ligne non vide de la feuille 1 en colonne S à partir de S2

si une bonne âme pouvait m'aider ce serait super sympa.

Merci à tous.

**parmi** · 09/03/2018, 17h53

Bonjour,

Je pense que ça devrait ressembler à ça

Code :

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

1
2
3
4
5
6
7
    Dim Dernlign As Long
    DernLigne = Sheets("Feuil1").Range("A" & Rows.Count).End(xlUp).Row 'jusqu'à la dernière ligne non vide de la feuille 1
 
    Dim DernLigne1 As Long
    DernLigne1 = Sheets("Feuil2").Range("A" & Rows.Count).End(xlUp).Row 'jusqu'à la dernière ligne non vide de la feuille 2
 
    Sheets("Feuil1").Range("S2:S" & Dernlign).Formula = "=INDEX(Feuil2!$L$6:$N$" & DernLigne1 & ",MATCH(Feuil1!M2,Feuil2!$L$6:$L$" & DernLigne1 & ",0),2)"

Ceci dit, quand tu initialises tes variables pour le nombre de lignes, n'oublie pas de spécifier la feuille...

**roch2a** · 10/03/2018, 22h13

Merci de ta réponse cela m'a permis d'avancer mais j'ai toujours une erreur d'exécution

1004 : erreur définie par l'application ou par l'objet

j'ai essayé de corriger ma macro comme suit mais j'ai toujours la même erreur sur la dernière ligne.

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
31
32
Sub test()
 
'
' Macro4 Macro récuperer les montants du tableau croisé dynamique en feuille 2 pour les injecter dans la feuille 1
'=INDEX(Feuil2!$L$6:$N$16;EQUIV(Feuil1!M2;Feuil2!$L$6:$L$16;0);2)
 
Dim DernLigne1 As Long
    DernLigne1 = Range("A" & Rows.Count).End(xlUp).Row
 
 
Dim maPlageDynamiq As Range
Dim DernLigneDynamiq As Long, DernColonneDynamiq As Integer
    'dernière ligne colonne L
    DernLigneDynamiq = Sheets("Feuil2").Range("L" & Rows.Count).End(xlUp).Row - 1  'ne selectionne pas la ligne total général ni les entêtes du tableau croisé dynamique
    'dernière colonne ligne 6
    DernColonneDynamiq = Sheets("Feuil2").Cells(6, Cells.Columns.Count).End(xlToLeft).Column  'dernière colonne du tableau croisé dynamique
    Set maPlageDynamiq = Range([Feuil2!L6], Sheets("Feuil2").Cells(DernLigneDynamiq, DernColonneDynamiq))
 
'maPlageDynamiq.Select
 
Dim maColonneDynamiq As Range
 
    DernColonneDynamiq = Sheets("Feuil2").Cells(6, Cells.Columns.Count).End(xlToLeft).Column - 2 'dernière colonne du tableau croisé dynamique
    Set maColonneDynamiq = Range([Feuil2!L6], Sheets("Feuil2").Cells(DernLigneDynamiq, DernColonneDynamiq)) 'colonne 1 du tableau croisé dynamique
 
'maColonneDynamiq.Select
 
Sheets("Feuil1").Activate
Cells(2, 19).Select
    Sheets("Feuil1").Range("S2:S" & Dernlign1).Formula = "=INDEX(Feuil2! & maPlageDynamiq ,MATCH(Feuil1!M2,maColonneDynamiq ,0),2)"
 
End Sub

**parmi** · 11/03/2018, 15h21

Sur quelle ligne survient l'erreur?
Passe en pas à pas (F8) et vérifie les valeurs de tes variables

N'oublie pas de spécifier les feuilles, comme ici...

Code :

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

1
2
Dim DernLigne1 As Long
DernLigne1 = Range("A" & Rows.Count).End(xlUp).Row

**roch2a** · 11/03/2018, 15h39

Merci de ton aide,

j'ai bien vérifié mes plages maColonneDynamiq, maPlageDynamiq et DernLigne1 elles correspondent bien aux plages que je souhaite comme tu le verras dans mon code.

J'ai aussi résolu le problème de l'erreur 1004 sur la ligne de la formule index mais maintenant j'ai un autre soucis le résultat me renvoie #nom?

voici le nouveau code:

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
31
32
33
34
35
36
37
38
39
40
41
Sub test()
 
'
' Macro4 Macro récuperer les montants du tableau croisé dynamique en feuille 2 pour les injecter dans la feuille 1
'=INDEX(Feuil2!$L$6:$N$16;EQUIV(Feuil1!M2;Feuil2!$L$6:$L$16;0);2)
 
Dim DernLigne1 As Long
    DernLigne1 = Range("A" & Rows.Count).End(xlUp).Row
 
 
Dim maPlageDynamiq As Range
Dim DernLigneDynamiq As Long, DernColonneDynamiq As Integer
    'dernière ligne colonne L
    DernLigneDynamiq = Sheets("Feuil2").Range("L" & Rows.Count).End(xlUp).Row - 1  'ne selectionne pas la ligne total général ni les entêtes
    'dernière colonne ligne 6
    DernColonneDynamiq = Sheets("Feuil2").Cells(6, Cells.Columns.Count).End(xlToLeft).Column  'dernière colonne du tableau croisé dynamique
    Set maPlageDynamiq = Range([Feuil2!L6], Sheets("Feuil2").Cells(DernLigneDynamiq, DernColonneDynamiq))
 
MsgBox maPlageDynamiq.Parent.Name 'test si la plage est bien sur la feuille 2 et oui
Dim maColonneDynamiq As Range
 
    DernColonneDynamiq = Sheets("Feuil2").Cells(6, Cells.Columns.Count).End(xlToLeft).Column - 2 'dernière colonne du tableau croisé dynamique
    Set maColonneDynamiq = Range([Feuil2!L6], Sheets("Feuil2").Cells(DernLigneDynamiq, DernColonneDynamiq)) 'colonne 1 du tableau croisé dynamique
 
MsgBox maPlageDynamiq.Parent.Name 'test si la plage est bien sur la feuille 2 et oui
 
Sheets("Feuil1").Activate
Cells(2, 19).Select
 
'    ActiveCell.FormulaR1C1 = _
'        "=INDEX(maPlageDynamiq,MATCH(Feuil1!RC[-6],maColonneDynamiq,0),2)"
'    Range("S2").Select
'    Selection.AutoFill Destination:=Range("S2:S" & DernLigne1)
 
    ActiveCell.FormulaR1C1 = _
        "=INDEX(Feuil2!R6C12 & maPlageDynamiq ,MATCH(Feuil1!RC[-6],Feuil2!R6C12 & maColonneDynamiq ,0),2)"
    Range("S2").Select
    Selection.AutoFill Destination:=Range("S2:S" & DernLigne1)
 
 
End Sub

et voici la formule qui s'inscrit dans mon tableau excel à la suite de la macro:

Code :

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

=INDEX(Feuil2!$L$6 & maPlageDynamiq;EQUIV(Feuil1!M2;Feuil2!$L$6 & maColonneDynamiq;0);2)

une idée du problème ?

**parmi** · 11/03/2018, 16h29

Il te manque des apostrophes pour isoler les variables
Essaie comme ceci

Code :

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

1
2
ActiveCell.FormulaR1C1 = _
"=INDEX(" & maPlageDynamiq & ",MATCH(Feuil1!RC[-6]," & maColonneDynamiq & " ,0),2)"

**roch2a** · 11/03/2018, 18h02

Avec ta formule comme avec la mienne ci dessous toujours une erreur ....

Code :

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

1
2
ActiveCell.FormulaR1C1 = _
"=INDEX(" & maPlageDynamiq & ",MATCH(Feuil1!RC[-6]," & maColonneDynamiq & " ,0),2)"

message d'erreur :

erreur d'exécution 13
incompatibilité de type

encore merci de ton aide

**parmi** · 11/03/2018, 18h45

Et si tu passais par une RechercheV ?

**roch2a** · 11/03/2018, 18h54

Envoyé par parmi

Et si tu passais par une RechercheV ?

cela serait moins efficace mais je te remercie de ton aide je vais continuer à chercher une solution .

**parmi** · 11/03/2018, 19h30

Écris ta formule pour qu'elle fonctionne.
Positionne-toi sur cette cellule et dans une Sub écris

Code :

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

Debug.Print Activecell.Formula

Tape Ctrl-G pour afficher la fenêtre Exécution et tu vas voir la formule telle que tu devrais l'écrire en utilisant Formula.

Il te reste à changer la valeur de dernière ligne avec une variable.

formule index + equiv en vba et copie sur toutes les lignes non vides

Macros et VBA Excel

Discussions similaires

Partager

Partager