Formule index sous VBA

Bonjour à tous,

Après des heures de recherches sur les différents sites et sur l'aide VBA je ne trouve toujours pas de solution à mon problème (qui j'en suis sure n'est pas bien compliqué).

J'ai une formule matricielle qui fonctionne très bien, je n'arrive juste pas à la faire continuer sur les lignes suivantes.
Je veux dire que là, ça m'affiche le meme résultat dans toutes les cellules correspondant à ma recherche puisque mes cellules recherchées ($A15, etc.) sont figées.

J'ai cru comprendre qu'il fallait utiliser les &" pour rendre les cellules variables mais je n'arrive pas à traduire la formule.

J'ai choisi d'utiliser la commande formula parce que je ne sais pas comment écrire la formule index en code VBA.

Si quelqu'un pouvait me donner une piste ce serait super!!

Merci d'avance

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12

Dim nbrow As Long nbrow = Range("A65536").End(xlUp).row For j = 2 To nbrow   'Copy 2nd layer Name If Sheet4.Cells(j, 5) = "" And Sheet4.Cells(j, 2) = "2" Then Sheet4.Cells(j, 5).FormulaArray = "=INDEX(List!$A$2:$Q$1600,SUMPRODUCT((List!$A$2:$A$1600=$A15)*(List!$E$2:$E$1600=$F14)*(List!$F$2:$F$1600=$G14)*(List!$L$2:$L$1600=$B15),ROW(List!$H$2:$H$1600)-1),8)"   End If   Next j

---

Cela correspond au bouton INSERT dans mon fichier.

Bonjour,

Citation:

---

J'ai choisi d'utiliser la commande formula parce que je ne sais pas comment écrire la formule index en code VBA.

---

En VBA, pour utiliser la fonction Index ainsi que bien d'autres fonctions d'ailleurs. Utiliser WorksheetFunction suivi du nom de la fonction (en anglais)
Pour la syntaxe de la fonction, c'est identique qu'avec Excel

Code:

---

1 2 3

With ThisWorkbook.Worksheets(1) MsgBox Application.WorksheetFunction.Index(.Range("A2:K30"), 9, 1) End With

---

Bonjour,

Citation:

---

Pour la syntaxe de la fonction, c'est identique qu'avec Excel

---

J'ai essayé d'intégrer la formule SUMPRODUCT mais il semble que la syntaxe soit differente d'excel au niveau des *.
Je n'arrive pas à trouver comment les remplacer.
J'ai vu sur certains forums que la fonction SUMPRODUCT était moins efficace sur VBA que sur Excel en formule.
Quelqu'un aurait-il une idée de ce que je pourrais utiliser à la place?
index match ?

Voici mon code qui ne fonctionne pas:

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12

Dim nbrow As Long nbrow = Range("A65536").End(xlUp).row For j = 2 To nbrow   'Copy 2nd layer Name If Sheet4.Cells(j, 5) = "" And Sheet4.Cells(j, 2) = "2" Then Sheet4.Cells(j, 5).Activate ActiveCell.Value = Application.WorksheetFunction.Index(Sheet1.Range("A2:Q1600"), WorksheetFunction.SumProduct((Sheet1.Range("A2:A1600") = Sheet4.Cells(1, 15)) * (Sheet1.Range("E2:E1600") = Sheet4.Cells(6, 14) * (Sheet1.Range("F2:F1600") = Sheet4.Cells(7, 14)) * (Sheet1.Range("L2:L1600") = Sheet4.Cells(2, 15)),(sheet1.Range("H2:H1600")-1), 8) End If   Next j

---

Merci d'avance!

Bonjour,

Citation:

---

Voici mon code qui ne fonctionne pas:

---

C'est un peu vague, qu'est-ce qui ne fonctionne pas ?
As-tu un message d'erreur, une valeur erronée ?
As-tu essayé la même formule en Excel pour voir si tu avais la bonne valeur ?

[EDIT]
Je vois que tu précises partout l'objet feuille sauf dans la ligne

Code:

---

nbrow = Range("A65536").End(xlUp).row

---

As-tu vérifié que tu es sur la bonne feuille et que ta variable nbrow à la bonne valeur.
As-tu fais tourner ton programme au pas à pas (Touche F8)

Ce qui ne fonctionne pas c'est la formule en elle meme, VBA n'accepte pas les * entre chaque condition comme la formule excel suivante le fait:

"=INDEX(List!$A$2:$Q$1600,SUMPRODUCT((List!$A$2:$A$1600=$A15)*(List!$E$2:$E$1600=$F14)*(List!$F$2:$F$1600=$G14)*(List!$L$2:$L$1600=$B15),ROW(List!$H$2:$H$1600)-1),8)"

Un message d'erreur "syntax error" s'affiche lorsque j'utilise F8.

Quand j'essaye cette formule directement dans les cellules j'obtiens tout à fait le bon résultat, c'est pour ça qu'au début j'avais utilisé le code FormulaArray=

Mon problème c'est que je n'arrive pas à boucler/incrementer pour chaque cellule = "" ce qui fait que j'obtiens le même résultat dans toutes les cellules vides (voir fichier joint).

Pour ce qui est de la ligne

Code:

---

1 2	Sheet4.Cells(j, 5).Activate nbrow = Range("A65536").End(xlUp).row

---

Je l'ai effectivement remplacé par :

Citation:

---

nbrow = sheet1.Range("A65536").End(xlUp).row

---

La variable nbrow a bien la bonne valeur et j'utilise bien F8 à chaque fois...

Bonjour,
Tu dois regarder du côté de la formule Evaluate.
Je n'ai pas le temps maintenant de l'adapter à un cas de SOMMEPROD mais je regarderai tout à l'heure.
Petit exemple ci-dessous

Code:

---

1 2 3 4 5 6

Dim plage1 As String, plage2 As String With ThisWorkbook.Worksheets("Feuil2") plage1 = .Range("A2:A7").Address: plage2 = .Range("C2:C7").Address End With Debug.Print Evaluate("=Sum(" & plage2 & ")") ' Somme de la plage C2:C7 Debug.Print Evaluate("=Sum(" & plage2 & " * " & 2 & ")") ' Somme de la même plage * 2

---

Ok, j'ai trouvé un site qui explique comment passer d'une formule excel à une formule VBA avec evaluate.

J'essaye et reviendrais vers vous en dernier recours, merci beaucoup en tout cas pour votre réactivité!

Bonjour,
Nous avons dans les plages E2:E9 des noms d'élèves, F2:F9 les formations, G2:G9 les points obtenus.
En H1 le nom de l'élève cherché, en I1 le nom de la formation cherchée
Elève Cours Toto Excel 25
Toto Excel 10
Toto Word 9
Titi Excel 7
Toto Excel 6
Jojo Excel 8
Toto Word 8
Titi Access 6
Toto Excel 9

Avec une formule Excel cela donnerait
=SOMMEPROD( ($E$2:$E$9=$H$1) * ($F$2:$F$9=$I$1) * ($G$2:$G$9))
ou
=SOMMEPROD( ($E$2:$E$9="Toto") * ($F$2:$F$9="Excel") * ($G$2:$G$9))
En VBA, 3 exemples illustré à l'aide de la variable formule

Code:

---

1 2 3 4 5 6 7 8

Sub SumProductEvaluate() Dim formule$, Eleve$, Cours$ Eleve = Chr(34) & "Toto" & Chr(34): Eleve = Chr(34) & Range("H1") & Chr(34) Cours = Chr(34) & "Excel" & Chr(34): Cours = Chr(34) & Range("I1") & Chr(34) formule = "SUMPRODUCT((E2:E9=""Toto"")*(F2:F9=""Excel"") * (G2:G9))" formule = "SUMPRODUCT((E2:E9=" & Eleve & ")*(F2:F9=" & Cours & ") * (G2:G9))" formule = "SUMPRODUCT((E2:E9=H1)*(F2:F9=I1) * (G2:G9))" Debug.Print "Nombre de points " & Evaluate(formule)

---

End Sub

En essayant d'adapter ma formule à Evaluate je me retrouve avec un message d'erreur "#VALUE!" dans mes cellules sans pouvoir savoir ou est l'erreur puisque F8 ne fonctionne pas à l'interieur de la fonction.

j'ai également essayé avec Index(plage, match(...), match(...), column) mais ca n'a pas fonctionné, il semble qu'on ne puisse imbriquer match que 2 fois alors que moi j'ai 4 conditions à remplir.
Seul sumproduct fonctionne.


Pouvez vous me dire quelles sont les erreurs dans mon 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

Dim nbrow As Long nbrow = Sheet1.Range("A65536").End(xlUp).row For j = 2 To nbrow   Dim R0 As Range Set R0 = Sheet1.Range("A2:Q2000")   Dim R1 As Range Set R1 = Sheet1.Range("A2:A2000")   Dim R5 As Range Set R5 = Sheet1.Range("E2:E2000")   Dim R6 As Range Set R6 = Sheet1.Range("F2:F2000")   Dim R8 As Range Set R8 = Sheet1.Range("H2:H2000")   Dim R12 As Range Set R12 = Sheet1.Range("L2:L2000")   'Copy 2nd layer Name If Sheet4.Cells(j, 5) = "" And Sheet4.Cells(j, 2) = "2" Then Sheet4.Cells(j, 5).Activate ActiveCell.Value = Evaluate("index(R0, sumproduct((R1 = ActiveCell.Offset(-4, 0).Value) * (R5 = ActiveCell.Offset(-1, 1).Value) * (R6 = ActiveCell.Offset(-1, 2).Value) * (R12 = ActiveCell.Offset(-3, 0).Value), row(R8-1), 8")   End If Next j

---

Bonjour,
Je ne crois pas que tu aies lu la réponse que j'ai faite et testé le code qui l'accompagnait.
Si tu code Evaluate("index(R0 , 1 ,3)"), tu obtiendras Index(R0, 1, 3), qui sera interprété comme une plage nommée R0
Alors que tu dois obtenir Index($A$2:$Q$2000, 1, 3) soit "Index(" & R0.Address & ", 1, 3)"

Sisi bien sur que je l'ai lu et que je l'ai essayé et ça fonctionne très bien quand on cherche des critères bien particuliers comme "Toto" et "Excel" mais ce n'est pas tout à fait ce que je cherche à faire.

J'ai 2 tableaux avec des données identiques mais pas dans le même ordre et je veux dire:
Si A:(tableau1)=A15(tableau2)
et L:(tableau1)=B15(tableau2)
et E:(tableau1)=F14(tableau2)
et F:(tableau1)=G14(tableau2)

alors copier H:(tableau1) sur E15(tableau2)
Et cela pour toutes les cellules vides que ma colonne E(tableau2)
C'est tout.
J'ai essayé avec des If then mais ça ne marche pas parce qu'il compare les mêmes lignes des 2 tableaux alors que mes critères sont sur 2 lignes différentes.

Bonjour,
Ma réponse précédente faisait référence à ta syntaxe qui n'est pas bonne.

Code:

---

ActiveCell.Value = Evaluate("index(R0, sumproduct((R1 = ActiveCell.Offset(-4, 0).Value) * (R5 = ActiveCell.Offset(-1, 1).Value) * (R6 = ActiveCell.Offset(-1, 2).Value) * (R12 = ActiveCell.Offset(-3, 0).Value), row(R8-1), 8")

---

INDEX(R0, ligne, Colonne) doit être "Index(" & R0.Address & ", 1, 3)" et ainsi dans toute ta formule.
Tu peux aussi évidemment ne pas déclarer R0 comme un range mais comme un string ce qui réduirait la longueur du string Formule
Alors le code comme ceci

Code:

---

1 2	Dim R0 As String: R0 = Sheet1.Range("A2:Q2000") Resultat = evaluate("Index(" & R0 & ", 1, 3)")

---

Mais pas : formule = evaluate(INDEX(R0, ligne, Colonne))

Ok, merci beaucoup tout ce que vous m'avez donné fonctionne parfaitement!
(Je n'avais pas vu qu'en selectionnant la plage ou en laissant le curseur sur la plage on pouvait visualiser le résultat obtenu et donc les erreurs, désolée je débute)

Il me reste un dernier point, et là du coup je sais que c'est ce qui provoque mon erreur, c'est la fonction row dans ma formule:
"=INDEX(List!$A$2:$Q$1600,SUMPRODUCT((List!$A$2:$A$1600=$A15)*(List!$E$2:$E$1600=$F14)*(List!$F$2:$F$1600=$G14)*(List!$L$2:$L$1600=$B15),ROW(List!$H$2:$H$1600)-1),8)

Code:

---

ActiveCell.Value = Evaluate("Index(" & R0.Address & "), (Sumproduct((" & R1.Address & " = ActiveCell.Offset(0, -4).Value) * (" & R5.Address & " = ActiveCell.Offset(-1, 1).Value) * (" & R6.Address & " = ActiveCell.Offset(-1, 2).Value) * (" & R12.Address & " = ActiveCell.Offset(0, -3).Value)),(row(" & R8.Address & ")-1), (" & R8.Address & ")) ")

---

Bonjour,
Pour une raison que je ne m'explique pas, chez moi EVALUATE semble ne pas fonctionner avec la formule ROW. J'ai pensé que c'était dû au fait que c'est une fonction volatile mais j'ai testé d'autre formule du même type et cela fonctionne. Même Rows fonctionne.
Je suis peut-être très fatigué et quelqu'un d'autres aura une explication.
Voici le code testé

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

Dim R As String: R = "A5" Dim R0 As String: R0 = "A2:Q2000" Dim R1 As String: R1 = "A2:A2000" Dim R5 As String: R5 = "E2:E2000" Dim R6 As String: R6 = "F2:F2000" Dim R8 As String: R8 = "H2:H2000" Dim R12 As String: R12 = "L2:L2000" formule = "Index(" & R0 & ", 1, 3)" Debug.Print formule & vbTab & Evaluate(formule) formule = "ROWS(" & R8 & ")" Debug.Print formule & vbTab & Evaluate(formule) formule = "OFFSET(" & R & ", 1,1)" Debug.Print formule & vbTab & Evaluate(formule) formule = "ROW(" & R8 & ")" Debug.Print formule & vbTab & Evaluate(formule)

---

Ok, merci beaucoup en tout cas pour le reste, je vais poster ça sur un autre forum pour voir si quelqu'un a une solution.

Bonne fine de journée

Bonjour,

J'ai essayé sur d'autres forums mais personne n'a su trouver de solution.

J'ai essayé en l'écrivant différemment mais ça ne fonctionne pas et une personne de mon entourage pense aussi que cela devrait marcher, pouvez vous me donner votre avis??

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

Sub insert() Dim nbrow As Long nbrow = Sheet1.Range("A65536").End(xlUp).Row For j = 2 To nbrow   Dim R0 As Range Set R0 = Sheet1.Range("A2:Q2000")   Dim R1 As Range Set R1 = Sheet1.Range("A2:A1600")   Dim R5 As Range Set R5 = Sheet1.Range("E2:E1600")   Dim R6 As Range Set R6 = Sheet1.Range("F2:F1600")   Dim R8 As Range Set R8 = Sheet1.Range("H2:H1600")   Dim R12 As Range Set R12 = Sheet1.Range("L2:L1600")     'Copy 2nd layer Name If Sheet4.Cells(j, 5) = "" And Sheet4.Cells(j, 2) = "2" Then Sheet4.Cells(j, 5).Activate   If R1.Address = ActiveCell.Offset(0, -4).Value And R12.Address = ActiveCell.Offset(0, -3).Value And R5.Address = ActiveCell.Offset(-1, 1).Value And R6.Address = ActiveCell.Offset(-1, 2).Value Then ActiveCell.Value = R8.Address   End If End If Next j End Sub

---

Merci!!