.Replace - comment remplacer une partie de formule en VBA?

Version imprimable

Bonjour à toutes et tous,

Me voilà avec un autre souci. J'ai une formule Excel que je transforme (via l'enregistreur de macro) et il me donne une "erreur 1004 : erreur définie par l'application ou l'objet". Après recherche, si je ne me trompe, il est impossible d'avoir plusieurs critères pour un "COUNTIFS" en VBA. Du coup, je ne sais pas du tout comment je peux écrire ma formule en VBA. Pourriez-vous, svp, m'aider?

Mon code actuel en VBA :
Code:

1 2 3 Range("J2").FormulaR1C1 = _ "=IF(AND(COUNTIFS(C[-5],""VAC"",C[-2],RC[-2],C[-9],RC[-9])<>(SUM(COUNTIFS(C[-5],""LFO"",C[-2],RC[-2],C[-9],RC[-9]),COUNTIFS(C[-5],""LFT"",C[-2],RC[-2],C[-9],RC[-9]))),AND(RC[-5]=""VAC"",AND(RC[-4]>2.99,RC[-4]<4.01))),""VAC (1/2j)"",IF(AND(COUNTIFS(C[-5],""LFT"",C[-2],RC[-2],C[-9],RC[-9])<>(SUM(COUNTIFS(C[-5],""LFO"",C[-2],RC[-2],C[-9],RC[-9]),COUNTIFS(C[-5],""VAC"",C" & _ "-2],C[-9],RC[-9]))),AND(RC[-5]=""LFT"",AND(RC[-4]>2.99,RC[-4]<4.01))),""LFT (1/2j)"",IF(AND(COUNTIFS(C[-5],""LFO"",C[-2],RC[-2],C[-9],RC[-9])<>(SUM(COUNTIFS(C[-5],""VAC"",C[-2],RC[-2],C[-9],RC[-9]),COUNTIFS(C[-5],""LFT"",C[-2],RC[-2],C[-9],RC[-9]))),AND(RC[-5]=""LFO"",AND(RC[-4]>2.99,RC[-4]<4.01))),""LFO (1/2j)"","""")))"
Ma formule Excel que je désire appliquer en VBA :
Code:

1 2 3 =SI(ET(NB.SI.ENS(E:E;"VAC";H:H;H2;A:A;A2)<>(SOMME(NB.SI.ENS(E:E;"LFO";H:H;H2;A:A;A2);NB.SI.ENS(E:E;"LFT";H:H;H2;A:A;A2)));ET(E2="VAC";ET(F2>2,99;F2<4,01)));"VAC (1/2j)"; SI(ET(NB.SI.ENS(E:E;"LFT";H:H;H2;A:A;A2)<>(SOMME(NB.SI.ENS(E:E;"LFO";H:H;H2;A:A;A2);NB.SI.ENS(E:E;"VAC";H:H;H2;A:A;A2)));ET(E2="LFT";ET(F2>2,99;F2<4,01)));"LFT (1/2j)"; SI(ET(NB.SI.ENS(E:E;"LFO";H:H;H2;A:A;A2)<>(SOMME(NB.SI.ENS(E:E;"VAC";H:H;H2;A:A;A2);NB.SI.ENS(E:E;"LFT";H:H;H2;A:A;A2)));ET(E2="LFO";ET(F2>2,99;F2<4,01)));"LFO (1/2j)";"")))
Les critères intérieurs sont, comme vous le pourrez le constater, les mêmes. Il n'y a que le LFT, VAC, LFO qui change.

Un grand merci d'avance pour votre aide.

Florent.

09/02/2017, 18h38
unparia

Bonjour
Tu dépasses à nouveau la limite de 255 caractères.
Il me semble pourtant bien que cela t'a déjà été exposé (pour une autre formule) il y a moins de 48 heures.

Bonjour,

Effectivement, on m'a parlé de FormulaArray, je ne savais pas que cela concernait toutes les codes VBA. D'ailleurs, voici la réponse que j'avais eu :

Citation:

Envoyé par joe.levrai

Bonjour,

256 caractères est la limite de FormulaArray

pour la dépasser, on peut ruser

de mon côté, j'injecte via le FormulaArray le "squelette" de la formule avec des briques de remplacement
ensuite, je fais mes remplacement

...

ainsi, après les remplacements, je retrouve ma formule matricielle intégrale

Aussi, le message d'erreur rencontré n'est pas le même qu'il y a moins de 48h. Merci pour l'information.

Donc, je suppose que je dois contourner mais je ne sais toujours pas comment et cette fois-ci, je n'aurai pas le choix.

Voici la réponse qui a l'air d'être la solution mais je n'arrive pas à l'adapter. Je ne demande pas la réponse toute faite mais j'essaie de comprendre comment cela fonctionne.
Citation:
exemple : j'ai une formule d'environ 500 caractères, où j'ai découpé 6 briques de remplacement
Code:

1 2 3 4 5 6 7 8 9 With Feuille_Masque.Cells(1, 15) .FormulaArray = "=AVERAGE(IF((1111)*(2222)*(3333)*(4444)*(5555),6666))" .Replace 1111, Raccourci_BDD & "[DATE]=" & CDbl(CDate(LesJours(i))), lookat:=xlPart .Replace 2222, Raccourci_BDD & "[MASTER_DOMAINE]=""" & LePartenaire(2) & """", lookat:=xlPart .Replace 3333, Raccourci_BDD & "[FLUX]=""STOCK""", lookat:=xlPart .Replace 4444, Raccourci_BDD & "[ITEM]<>""TRANS RC""", lookat:=xlPart .Replace 5555, Raccourci_BDD & "[ITEM]<>""FAX""", lookat:=xlPart .Replace 6666, Raccourci_BDD & "[PLUS_ANCIEN]", lookat:=xlPart End With
Merci d'avance pour toute aide apportée.

Re bonjour,

J'ai essayé ceci mais cela ne fonctionne pas :(
J'ai essayé avec parenthèses également, avec les "*", toujours la même erreur.

Code:

1
2
3
4
5
6
With ActiveSheet.Range("J2")
    .FormulaR1C1 = "=IF(AND(1111,2222,3333)"
    .Replace 1111, "IF(AND(COUNTIFS(C[-5],""VAC"",C[-2],RC[-2],C[-9],RC[-9])<>(SUM(COUNTIFS(C[-5],""LFO"",C[-2],RC[-2],C[-9],RC[-9]),COUNTIFS(C[-5],""LFT"",C[-2],RC[-2],C[-9],RC[-9]))),AND(RC[-5]=""VAC"",AND(RC[-4]>2.99,RC[-4]<4.01))),""VAC (1/2j)""", lookat:=xlPart
    .Replace 2222, "IF(AND(COUNTIFS(C[-5],""LFT"",C[-2],RC[-2],C[-9],RC[-9])<>(SUM(COUNTIFS(C[-5],""LFO"",C[-2],RC[-2],C[-9],RC[-9]),COUNTIFS(C[-5],""VAC"",C[-2],C[-9],RC[-9]))),AND(RC[-5]=""LFT"",AND(RC[-4]>2.99,RC[-4]<4.01))),""LFT (1/2j)""", lookat:=xlPart
    .Replace 3333, "IF(AND(COUNTIFS(C[-5],""LFO"",C[-2],RC[-2],C[-9],RC[-9])<>(SUM(COUNTIFS(C[-5],""VAC"",C[-2],RC[-2],C[-9],RC[-9]),COUNTIFS(C[-5],""LFT"",C[-2],RC[-2],C[-9],RC[-9]))),AND(RC[-5]=""LFO"",AND(RC[-4]>2.99,RC[-4]<4.01))),""LFO (1/2j)"",""""))", lookat:=xlPart
End With

J'ai essayé ceci également :

Code:

1
2
3
4
5
6
7
8
9
With ActiveSheet.Range("J2")
    .FormulaR1C1 = "=IF(AND(1111,2222,3333,4444,5555,6666)"
    .Replace 1111, "IF(AND(COUNTIFS(C[-5],""VAC"",C[-2],RC[-2],C[-9],RC[-9])<>(SUM(COUNTIFS(C[-5],""LFO"",C[-2],RC[-2],C[-9],RC[-9])", lookat:=xlPart
    .Replace 2222, "COUNTIFS(C[-5],""LFT"",C[-2],RC[-2],C[-9],RC[-9]))),AND(RC[-5]=""VAC"",AND(RC[-4]>2.99,RC[-4]<4.01))),""VAC (1/2j)""", lookat:=xlPart
    .Replace 3333, "IF(AND(COUNTIFS(C[-5],""LFT"",C[-2],RC[-2],C[-9],RC[-9])<>(SUM(COUNTIFS(C[-5],""LFO"",C[-2],RC[-2],C[-9],RC[-9])", lookat:=xlPart
    .Replace 4444, "COUNTIFS(C[-5],""VAC"",C[-2],C[-9],RC[-9]))),AND(RC[-5]=""LFT"",AND(RC[-4]>2.99,RC[-4]<4.01))),""LFT (1/2j)""", lookat:=xlPart
    .Replace 5555, "IF(AND(COUNTIFS(C[-5],""LFO"",C[-2],RC[-2],C[-9],RC[-9])<>(SUM(COUNTIFS(C[-5],""VAC"",C[-2],RC[-2],C[-9],RC[-9])", lookat:=xlPart
    .Replace 6666, "COUNTIFS(C[-5],""LFT"",C[-2],RC[-2],C[-9],RC[-9]))),AND(RC[-5]=""LFO"",AND(RC[-4]>2.99,RC[-4]<4.01))),""LFO (1/2j)"",""""))", lookat:=xlPart
End With

Il bloque à la ligne FormulaR1C1 avec l'erreur 1004.

Quelques heures plus tard, j'ai essayé en modifiant la formule par des SOMMEPROD et en macro j'ai ceci :

Code:

1
2
3
4
5
6
With ActiveSheet.Range("J2")
    .FormulaR1C1 = "=IF(AND(1111)<>(SUM(2222),3333))),AND(RC[-5]=""VAC"",AND(RC[-4]>2.99,RC[-4]<4.01))),""VAC (1/2j)"",IF(AND(3333)<>(SUM(2222),1111))),AND(RC[-5]=""LFT"",AND(RC[-4]>2.99,RC[-4]<4.01))),""LFT (1/2j)"",IF(AND(2222)<>(SUM(1111),3333))),AND(RC[-5]=""LFO"",AND(RC[-4]>2.99,RC[-4]<4.01))),""LFO (1/2j)"","""")))"
    .Replace 1111, "SUMPRODUCT((R2C5:R300C5=""VAC"")*(R2C8:R300C8=RC[-2])*(R2C1:R300C1=RC[-9])", lookat:=xlPart
    .Replace 2222, "SUMPRODUCT((R2C5:R300C5=""LFO"")*(R2C8:R300C8=RC[-2])*(R2C1:R300C1=RC[-9])", lookat:=xlPart
    .Replace 3333, "SUMPRODUCT((R2C5:R300C5=""LFT"")*(R2C8:R300C8=RC[-2])*(R2C1:R300C1=RC[-9])", lookat:=xlPart
End With

Mais cela ne va toujours pas :(

Allez, j'avance un peu :

Code:

1
2
3
4
5
6
7
With ActiveSheet.Range("J2")
    .FormulaR1C1 = "=IF(AND(SUMPRODUCT(1111)<>(SUM(SUMPRODUCT(2222),SUMPRODUCT(3333))),AND(RC[-5]=""VAC"",AND(4444))),""VAC (1/2j)"",IF(AND(SUMPRODUCT(3333)<>(SUM(SUMPRODUCT(2222),SUMPRODUCT(1111))),AND(RC[-5]=""LFT"",AND(4444))),""LFT (1/2j)"",IF(AND(SUMPRODUCT(2222)<>(SUM(SUMPRODUCT(1111),SUMPRODUCT(3333))),AND(RC[-5]=""LFO"",AND(4444))),""LFO (1/2j)"","""")))"
    .Replace 1111, "(R2C5:R300C5=""VAC"")*(R2C8:R300C8=RC[-2])*(R2C1:R300C1=RC[-9])", lookat:=xlPart
    .Replace 2222, "(R2C5:R300C5=""LFO"")*(R2C8:R300C8=RC[-2])*(R2C1:R300C1=RC[-9])", lookat:=xlPart
    .Replace 3333, "(R2C5:R300C5=""LFT"")*(R2C8:R300C8=RC[-2])*(R2C1:R300C1=RC[-9])", lookat:=xlPart
    .Replace 4444, "RC[-4]>2.99,RC[-4]<4.01", lookat:=xlPart
End With

Plus de message d'erreur mais il ne me remplace pas les 1111,2222,3333 et 4444 dans ma formule...

Merci pour votre aide.