Macro Remplir Tableau avec référence critere "fixe"

**fla5h182** · 10/12/2014, 11h09

Bonjour à tous,
Avant tout, félicitations pour ce forum qui m'a beaucoup apporté en tant que débutant en VBA !
J'aimerais vous poser une question,
J'essaie (désespéramment) de remplir automatiquement un tableau avec une formule.
Mon problème est que je n’y arrive pas

Ma formule est une SOMME.SI.ENS, et les critères se trouvent sur la première colonne (des références) et la première ligne (des dates)..
Je vous avoue que ma formule est un peu "lourde" mais fonctionne dans mon tableaux "manuelles".
Elle ressemble à :

Code :

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

1
2
Cells(3, 2).FormulaR1C1 = _
        "=SUMIFS(Feuil2!R2C6:R47C6,Feuil2!R2C1:R47C1,""<""&R[-1]C[1],Feuil2!R2C1:R47C1,"">""&R[-1]C,Feuil2!R2C3:R47C3,R3C1)"

C'est le mieux que j'ai pu en tirer, car en faisant un AutoFill sur une ligne, le résultat est nickel, les critères "<""&R[-1]C[1] et "">""&R[-1]C se déplacent aussi et la ref R3C1 est fixe.
Mais comment automatisé sur l'ensemble du tableau, car si j'étire vers le bas, les critères <""&R[-1]C[1] et "">""&R[-1]C seront au dessus de la cellule activent et ne correspondront pas à mes dates en haut de tableau et la cellule fixe R3C1 devrait etre R4C1.... :'(:'(:'(
J’espère être clair, même moi je m'y perds depuis deux jours...
Merci d'avance pour vos réponses, je pourrais vous joindre un fichier test si nécessaire.

Dans l'impatience de vous lire!!!
merci,
ben

**Kimy_Ire** · 10/12/2014, 15h27

Salut fla5h182,

Alors, je n'ai pas tout compris avec tes formules.
Mais quoi qu'il en soit, si tu veux avoir une cellule référence fixe, il te suffit d'ajouter le signe "$".

Par exemple, si tu fais référence à une cellule qui contient une valeur pour l'ensemble des cellules d'une colonne voici ce qu'il faut faire :

	A	B	C
1	5	=A1/$C$1	5
2	10
3	15
4	20

Si tu étires la formule vers le bas de la colonne B, tu conserveras ta division par la cellule C1.
Je ne sais pas ça aide, mais je te propose donc d'ajouter des "$" dans ta formule afin que tu puisses accéder conserver cette cellule fixe.

N'hésite pas à revenir vers moi su j'suis à côté de la plaque !

Cordialement
Kimy

**fla5h182** · 10/12/2014, 15h41

Merci Kimy pour ta réponse

En fait j'ai utiliser le $ lorsque j'ai fais ma version "manuelles" mais en VBA je ne parviens pas à fixer mes cellules "conditions".
Un petit fichier pourrais être plus explicite.

Dans mon tableau j'ai les mois en haut et des réf à gauche.
Dans une autre feuille des quantités de ref en fonction de date.

J'aimerais, grâce à ma formule qui additionne les quantités de la référence voulue(à gauche) en fonction des mois de l'année (en haut) remplir mon tableau en VBA.

peut être que je suis aussi complétement à coté avec ma formule qui ne convient peut être pas....
Je ne sais plus quoi faire!! =)
Merci de ton aide en tout cas

**Kimy_Ire** · 10/12/2014, 16h30

Re fla5h182,

Essaye peut-être ceci :

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
Option Explicit
 
Sub tablo()
Dim rng_in As Range
Dim rng_out As Range
Dim dte As Date
Dim offst As Integer
Dim i As Integer, j As Integer
 
With Worksheets("Feuil2")
    Set rng_in = .Rows(1).Find("Lilly ID", LookIn:=xlValues, LookAt:=xlWhole).Offset(1, 0)
    For i = 0 To .Columns(rng_in.Column).Find("*", , , , , xlPrevious).Row - 2
        With Worksheets("Feuil1")
            If rng_in.Offset(i, 0) <> "" Then
                Set rng_out = .Columns(1).Find(rng_in.Offset(i, 0), LookIn:=xlValues, LookAt:=xlWhole)
 
                If Not rng_out Is Nothing Then
                    dte = rng_in.Offset(i, -2)
 
                    offst = 0
                    For j = 1 To .Rows(2).Find("*", , , , , xlPrevious).Column
                        If year(.Range("A2").Offset(0, j)) = year(dte) And Month(.Range("A2").Offset(0, j)) = Month(dte) Then
                            offst = j
                            Exit For
                        End If
                    Next j
 
                    If offst <> 0 Then
                        rng_out.Offset(0, offst) = rng_out.Offset(0, offst) + rng_in.Offset(i, 3)
                    End If
                Else
                    MsgBox "La référence """ & rng_in.Offset(i, 0) & """ n'est pas présente dans le tableau."
                End If
            End If
        End With
    Next i
End With
End Sub

J'ai fait des tests rapides et ça semble ok.
J'espère que c'est bon...
A savoir : code non-optimisé mais qui, j'espère, répond au besoin.

Je te laisse checker et revenir vers moi pour me dire si ça te convient !

On peut facilement ajouter un "reset" des valeurs quand on lance la macro, mais ne connaissant pas tout le contexte fonctionnel, j'attends ton retour.

Cordialement,
Kimy

**fla5h182** · 10/12/2014, 16h58

Excellent !!!!
Maintenant je vais essayer de comprendre tout ça et l'intégrer à mon fichier global... Ce qui risque d’être folklo!! et j'aurais peut etre besoin d'infos

Je vais regarder ça de plus prés!

En tout merci de ton aide

!!

**Kimy_Ire** · 10/12/2014, 17h18

Plus simple, peut-être comme ça.

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
47
48
49
50
51
52
53
54
55
56
57
58
Option Explicit
 
Sub tablo()
'Initialisation des variables
Dim rng_in As Range
Dim rng_out As Range
Dim dte As Date
Dim offst As Integer
Dim i As Integer, j As Integer
 
'Avec la "Feuil2"...
With Worksheets("Feuil2")
    '... on set "rng_in" sur la cellule de la ligne 1 sur laquelle on trouve "Lilly ID"
    Set rng_in = .Rows(1).Find("Lilly ID", LookIn:=xlValues, LookAt:=xlWhole).Offset(1, 0)
 
    'Pour i = 0 au nombre de lignes (non-vide) de la feuille...
    For i = 0 To .Columns(rng_in.Column).Find("*", , , , , xlPrevious).Row - 2
 
        '... avec la "Feuil1"...
        With Worksheets("Feuil1")
            '... on vérifie si "rng_in" décalé de l ligne(s) contient quelque chose.
            If rng_in.Offset(i, 0) <> "" Then
                'Si oui, on set "rng_out" sur la référence de la "Feuil1".
                Set rng_out = .Columns(1).Find(rng_in.Offset(i, 0), LookIn:=xlValues, LookAt:=xlWhole)
 
                'Si on trouve bien la référence sur la "Feuil1"...
                If Not rng_out Is Nothing Then
                    '... alors on définie "dte" comme étant la date de rng_in décalé de i ligne(s) et de -2 colonnes (colonne A de la "Feuil2")
                    dte = rng_in.Offset(i, -2)
 
                    'On définie offst ) 0 pour chaque itération de i.
                    offst = 0
 
                    'On boucle maintenant sur j de 1 au nombre de colonnes non-vides de la ligne 2 (de la "Feuil1")
                    For j = 1 To .Rows(2).Find("*", , , , , xlPrevious).Column
                        'Si l'année ET le mois de [A2] (de la "Feuil1") déclalé de j colonne(s) sont égaux à la date "dte"...
                        If year(.Range("A2").Offset(0, j)) = year(dte) And Month(.Range("A2").Offset(0, j)) = Month(dte) Then
                            '... alors on définie "offst" égal à ce décalage.
                            offst = j
                            'et on a pas besoin de continuer, parce qu'on a trouvé le mois correspondant, donc on sort de la boucle j.
                            Exit For
                        End If
                    Next j
 
                    'Il se peut qu'on ne trouve pas de date (exemple année = 2013). Or, si offst est différent de 0, on en aura trouvé une.
                    If offst <> 0 Then
                        'Donc, si on trouve une date, alors on ajoute dans "rng_out" décalé de "offst" colonnes la valeur de "rng_in" pour l'itération i sur laquelle on se trouve.
                        rng_out.Offset(0, offst) = rng_out.Offset(0, offst) + rng_in.Offset(i, 3)
                    End If
                Else
                    'Et si jamais on ne trouve pas de référence dans la "Feuil1", une MsgBox s'affiche.
                    MsgBox "La référence """ & rng_in.Offset(i, 0) & """ n'est pas présente dans le tableau."
                End If
            End If
        End With
    Next i
End With
End Sub

Cordialement,
Kimy

**fla5h182** · 11/12/2014, 10h04

Merci infiniment Kimy, j'ai réussi à l'intégrer dans mon tableaux !! Le résultat est parfait!
J'ai eu un peu de mal à déchiffrer tout cela n'étant pas spécialiste! Mais je pense avoir compris grossièrement le fonctionnement du code!

Merci encore

a+

Macro Remplir Tableau avec référence critere "fixe" [XL-2010]

Macros et VBA Excel

Discussions similaires

Partager

Partager