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

Version imprimable

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:

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:

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:

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.

Bonjour,

Je pense que ça devrait ressembler à ça

Code:

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...

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

Citation:

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:

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

11/03/2018, 15h21
parmi
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:

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

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:

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:

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

une idée du problème ?

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

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

11/03/2018, 18h02
roch2a
Avec ta formule comme avec la mienne ci dessous toujours une erreur ....
Code:

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

Citation:

erreur d'exécution 13
incompatibilité de type

encore merci de ton aide
11/03/2018, 18h45
parmi

Et si tu passais par une RechercheV ?
11/03/2018, 18h54
roch2a

Citation:

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 .
11/03/2018, 19h30
parmi

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

Code:

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.