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