Discussion :

[applemilk]

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 :

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

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.

[Philippe Tulliez]

Bonjour,

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 :

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

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

[applemilk]

Bonjour,

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 :

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

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!

[Philippe Tulliez]

Bonjour,

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 :

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

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)

[applemilk]

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 :

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

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

Je l'ai effectivement remplacé par :

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

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

[Philippe Tulliez]

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 :

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

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

[applemilk]

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é!

[Philippe Tulliez]

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 :

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

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

[applemilk]

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 :

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

[Philippe Tulliez]

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)"

[applemilk]

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.

[Philippe Tulliez]

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

Code :

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

   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 :

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

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

[applemilk]

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 :

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

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 & ")) ")

[Philippe Tulliez]

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 :

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

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)

[applemilk]

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

[applemilk]

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 :

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