VBA - Calcul matriciel avec plage variable

[rockball73]

Bonjour forum,

Je me permets de poser une question concernant la création d'une macro.

Mise en situtation :
J'ai dans un tableur de +/- 150.000 lignes (nombre de ligne variable à chaque ouverture du fichier car alimenté par des exports de notre logiciel comptable). Dans la colonne X, je désire faire un test réalisé grâce à une formule matricielle qui est la suivante :

{=SI(Q2="44";SOMME(SI(("70"=$Q$2:$Q$149999)*(U2=$U$2:$U$149999);$G$2:$G$149999;""));"")}  , une fois validée par CTRL+MAJ+Enter

Cependant, d'année en année, le fichier dans lequel se trouve cette formule est supprimé pour laisser place à son successeur, qui est le même export, mais avec les données de l'année suivante. J'aimerais, dès lors que, via une macro exécutée par un fichier de regroupement, cette formule soit retranscrite dans la cellule "X2" et ensuite étirée jusqu'à la dernière cellule non vide.
J'utilise ce bout de code pour étirer la formule et cela fonctionne, lorsque la cellule "X2" est au préalable remplie par la fonction matricielle ci-dessus :

Dim a As Long
    a = Range("G2").End(xlDown).Row
    Range("X2").AutoFill Destination:=Range("X2:X" & a)

Maintenant, j'aimerais que la formule matricielle soit sous cette forme, afin que le calcul porte sur la plage "non vide" de lignes (et pas sur toutes les lignes de 2 à 1.046.546) pour raccourcir les temps de calculs et alléger le fichier.
J'avais donc penser à ce code-ci :

Dim a As Long
    a = Range("G2").End(xlDown).Row
    Range("X2").Formula = "=IF(RC[-7]=""44"",SUM(IF((""70""=R2C17:R" & a & "C17)*(RC[-3]=R2C21:R" & a & "C21),R2C7:R" & a & "C7,"""")),"""")"
    Range("X2").AutoFill Destination:=Range("X2:X" & a)

Cela fonctionne presque, le seul problème est que le code VBA ne fait pas la validation matricielle "CTRL+MAJ+Enter"...

Merci d'avance pour votre aide,

Rockb@ll.

 

[Pierrot93]

Re : VBA - Calcul matriciel avec plage variable

Bonjour,

pour une formule matricielle en vba, remplace :

Range("X2").Formula

par :

Range("X2").FormulaArray

bon après midi
@+

 

[rockball73]

Re : VBA - Calcul matriciel avec plage variable

Bonjour Pierrot,

Tout d'abord merci de ta réponse, et de l'intérêt que tu portes à mon problème.

J'ai essayé déjà avec la le FormulaArray. De plus, lorsque l'on utilise ce code, faut-il quand même mettre les corchets {} indiquant un calcul matriciel, ou alors, de quelle manière faut-il procéder pour indiquer à Excel, via la macro, que l'on procède à une validation matricielle?

Merci encore,

Bon après-midi.

 

[Pierrot93]

Re : VBA - Calcul matriciel avec plage variable

Re,

à priori, dans le code, pas besoin des crochets....

 

[rockball73]

Re : VBA - Calcul matriciel avec plage variable

Merci beaucoup Pierrot,

En effet, FormulaArray fonctionne. Je ne sais pourquoi, lors de toutes mes précédentes tentatives, cela n'a pas fonctionné.

Encore merci, et bonne continuation.