Discussion :

[clorion91]

Bonsoir à tous,

Tout d'abord, je tiens à vous remercier car j'ai plusieurs fois eu l'occasion de parcourir ce forum et les réponses sont toujours pertinentes.

Je vous explique mon problème:

Je cherche à transcrire en VBA une formule que j'utilise très fréquemment dans le cadre de mon travail, à savoir:
index( "tableau1";equiv("cellule colonne"; "colonne recherche";0);equiv("cellule ligne";"ligne recherche";0)

J'ai donc tenté de retranscrire cette formule sous VBA mais cela ne fonctionne pas car je ne connais pas à l'avance le nombre de ligne ni le nombre de colonne de mon tableau où la recherche doit s'effectuer.

Vous trouverez sur ce lien un fichier excel à titre d'exemple: http://cjoint.com/?0EEwyMeIjni

Dans la feuille 1, je cherche à compléter les données des colonnes C, D, E et F présentes dans la colonne D de la feuille 2.

Pouvez-vous m'aider s'il vous plait?

J'ai beau cherché et tenté divers codes, cela ne fonctionne pas.

Merci par avance.

Bonne soirée.

[Invité]

Bonsoir Clorion91

Je ne suis pas certain qu'une simple formule

index( "tableau1";equiv("cellule colonne"; "colonne recherche";0);equiv("cellule ligne";"ligne recherche";0)

te donne le bon résultat (mais je peux me tromper)

Donc voici une formule matricielle, qui pour moi fait l'affaire

Code :

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

=SOMMEPROD((Feuil2!$A$2:$A$14=$A2)*(Feuil2!$C$2:$C$14=C$1)*(Feuil2!$D$2:$D$14))

Qui est utilisée dans ce code de 2 façons

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
42
43
44
45
46
Sub InscrireValeurs()
  Dim ShtS As Worksheet
  Dim DLigS As Long
  Dim ShtD As Worksheet
  Dim Col As Long, DColD As Long, DLigD As Long, Lig As Long
  Dim MaForm As String  ' variable pour la Formule
  Dim sCrit As String ' Variable pour le critère Cdt
  ' Définir la feuille source
  Set ShtS = Worksheets("Feuil2")
  ' Récupérer le numéro de la dernière ligne remplie de la feuille
  DLigS = ShtS.Range("A" & ShtS.Rows.Count).End(xlUp).Row
  ' Définir la feuille de destination
  Set ShtD = Worksheets("Feuil1")
  ' Récupérer le numéro de la dernière colonne et dernière ligne
  DColD = ShtD.Cells(1, ShtD.Columns.Count).End(xlToLeft).Column
  DLigD = ShtD.Range("A" & ShtS.Rows.Count).End(xlUp).Row
 
  ' La formule matricielle
  '=SOMMEPROD((Feuil2!$A$2:$A$14=$A2)*(Feuil2!$C$2:$C$14=C$1)*(Feuil2!$D$2:$D$14))
 
  ' *** PREMIERE POSSIBILITE (peut être supprimée)***
  ' Incrire la formule dans la première cellule
  ShtD.Range("C2").FormulaLocal = _
    "=SOMMEPROD((Feuil2!$A$2:$A$14=$A2)*(Feuil2!$C$2:$C$14=C$1)*(Feuil2!$D$2:$D$14))"
  ' Effectuer une recopie vers le bas
  ShtD.Range("C2:C" & DLigD).FillDown
  ' Effectuer uen recopie vers la droite
  ShtD.Range(ShtD.Cells(2, 3), ShtD.Cells(DLigD, DColD)).FillRight
  ' Copier / coller les valeurs
  With ShtD.Range(ShtD.Cells(2, 3), ShtD.Cells(DLigD, DColD))
    .Copy
    .PasteSpecial Paste:=xlPasteValues
  End With
  Application.CutCopyMode = False
  '
  ' *** DEUXIEME POSSIBILITE (peut être supprimée)***
  ' Ou effectuer le calcul pour chaque cellule du tableau
  For Col = 3 To DColD
    sCrit = ShtD.Cells(1, Col).Address
    For Lig = 2 To DLigD
      MaForm = "SUMPRODUCT((" & ShtS.Name & "!$A$2:$A$14=$A" & Lig & ")*(" _
        & ShtS.Name & "!$C$2:$C$14=" & sCrit & ")*(" & ShtS.Name & "!$D$2:$D$14))"
      ShtD.Cells(Lig, Col).Value = Application.Evaluate(MaForm)
    Next Lig
  Next Col
End Sub

Voilà à toi de voir

[clorion91]

Bonsoir Bruno M91,

Super! Ta macro fonctionne à merveille et j'ai opté pour la première solution

Je te remercie infiniment.

Je rencontre juste un petit problème avec le code :

Effectuer uen recopie vers la droite

Code :

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

1
2
3
4
5
  ShtD.Range(ShtD.Cells(2, 3), ShtD.Cells(DLigD, DColD)).FillRight
  ' Copier / coller les valeurs
  With ShtD.Range(ShtD.Cells(2, 3), ShtD.Cells(DLigD, DColD))
    .Copy
    .PasteSpecial Paste:=xlPasteValues

En effet, dans le tableau dans lequel je paramètre cette macro, j'ai des colonnes qui se trouvent après la colonne PAL de la feuille 1. Du coup, ces cellules se retrouvent écraser par la formule.

Sais-tu comment je pourrais faire pour que la formule copie colle sur la droite en s'arrêtant à la colonne PAL?

Je te remercie par avance et merci encore pour ta rapidité et ton explication détaillée de la macro, c'est toujours plus sympas lorsque l'on comprend ce que l'on paramètre

Bonne soirée.