FormulaArray avec + de 255 caractères

[dieu08]

Bonjour,

Meilleurs voeux à tous accros d'Excel....!

J'ai un petit souci avec une macro (voir ci-dessous) dans laquelle je dois intégrer un calcul matriciel dans différents tableaux. J'obtiens une erreur 1004 avec l'utilisation de FormulaArray et la classe Range.

Mes recherches m'ont menées sur le site de Microsoft où il était mentionné qu'il n'était pas possible en VB d'utiliser FormulaArray avec une formule de plus de 255 caractères.

Mais je ne vois pas trop comment aménager ma formule pour que cela puisse coller avec ma macro.

Sub tranche_age2()

Mois = 11
Mois2 = 10
Datation = 30112009
Datation2 = 31102009
nbcount1 = 8571
nbcount2 = 8300
expr1 = "'[" & Mois2 & "-toutes_aides_asg_" & Datation2 & ".xls]toutes_aides_asg_" & Datation2 & "'"
expr2 = "toutes_aides_asg_" & Datation

FileCopy "S:\F.VARALLI\Projets\TdBord\PHV\Tranche_agePHV_2009.xls", "C:\temp\Tranche_agePHV_2009.xls"

MsgBox ("Ouverture du fichier tranche d'âge")

ChDrive "C:\"
ChDir "C:\temp"
fichinfo5 = Application.GetOpenFilename("Tous les fichiers,*.xls")

Workbooks.Open Filename:=fichinfo5

FileCopy "S:\F.VARALLI\Projets\TdBord\PHV\synthesePHV_2009.xls", "C:\temp\synthesePHV_2009.xls"

MsgBox ("Ouverture du fichier synthese")

ChDrive "C:\"
ChDir "C:\temp"
fichinfo6 = Application.GetOpenFilename("Tous les fichiers,*.xls")

Workbooks.Open Filename:=fichinfo6

With Application
.Calculation = xlManual
.MaxChange = 0.001
End With

MsgBox ("Ouverture de la trame")

ChDrive "C:\"
ChDir "C:\temp"
fichinfo7 = Application.GetOpenFilename("Tous les fichiers,*.xls")

Workbooks.Open Filename:=fichinfo7

With Application
.Calculation = xlManual
.MaxChange = 0.001
End With


Windows("trame_vierge.xls").Activate
Worksheets("T-AgeXX09").Activate

Cells(3, 2).Select
Selection.FormulaArray = _
"=SUM(([synthesePHV_2009.xls]toutes_aides_asg_" & Datation & "!R2C3:R" & nbcount1 & "C3=""1"")*([synthesePHV_2009.xls]toutes_aides_asg_" & Datation & "!R2C4:R" & nbcount1 & "C4=" & Cells(1, 1).Value & ")*([synthesePHV_2009.xls]toutes_aides_asg_" & Datation & "!R2C5:R" & nbcount1 & "C5=""A"")*([synthesePHV_2009.xls]toutes_aides_asg_" & Datation & "!R2C10:R" & nbcount1 & "C10=" & Cells(2, 2).Value & ")*(([synthesePHV_2009.xls]toutes_aides_asg_" & Datation & "!R2C14:R" & nbcount1 & "C14=" & Cells(3, 1).Value & ")*1)"

With Application
.Calculation = xlAutomatic
.MaxChange = 0.001
End With

MsgBox ("Macro terminée")

Cells(1, 1).Activate

End Sub

Merci de votre aide.

Dieu08

 

[dieu08]

Re : FormulaArray avec + de 255 caractères

Voilà ce que j'ai pu trouver comme contournement mais je vous avoue que ça ne me convient pas particulièrement (à titre d'exemple) :

Public Sub LongArrayFormula()
Dim theFormulaPart1 As String
Dim theFormulaPart2 As String
theFormulaPart1 = "=IF(MONTH(DATE(YEAR(NOW()),MONTH(NOW()),1))-" & _
"MONTH(DATE(YEAR(NOW()),MONTH(NOW()),1)-" & _
"(WEEKDAY(DATE(YEAR(NOW()),MONTH(NOW()),1))-1)+" & _
"{0;1;2;3;4;5}*7+{1,2,3,4,5,6,7}-1),""""," & _
"X_X_X())"

theFormulaPart2 = "DATE(YEAR(NOW()),MONTH(NOW()),1)-" & _
"(WEEKDAY(DATE(YEAR(NOW()),MONTH(NOW()),1))-1)+" & _
"{0;1;2;3;4;5}*7+{1,2,3,4,5,6,7}-1)"

With ActiveSheet.Range("E2:K7")
.FormulaArray = theFormulaPart1
.Replace "X_X_X())", theFormulaPart2
.NumberFormat = "mmm dd"
End With

End Sub

Merci.

 

[dieu08]

Re : FormulaArray avec + de 255 caractères

Mon problème ne parle à personne ?

 

[PMO2]

Re : FormulaArray avec + de 255 caractères

Bonjour,

Peut-être une piste que je n'ai pu tester n'étant pas en situation.

L'idée est d'utiliser un Nom dans lequel sera stocké une chaîne longue et répétitive se trouvant dans votre formule comme dans l'extrapolation ci-dessous :

Sub xx()
Dim Datation
Dim nbcount1
Dim N As Name
Dim Nom$
Dim Formule$
Datation = 9  'valeur arbitraire
nbcount1 = 7  'valeur arbitraire
Set N = ThisWorkbook.Names.Add(Name:="toto", RefersTo:="([synthesePHV_2009.xls]toutes_aides_asg_", Visible:=True)
Nom$ = N.Name
Formule$ = "=SUM(" & Nom$ & Datation & "!R2C3:R" & nbcount1 & "C3=""1"")*" & Nom$ & Datation & "!R2C4:R" & nbcount1 & "C4=" & Cells(1, 1).Value & ")*" & Nom$ & Datation & "!R2C5:R" & nbcount1 & "C5=""A"")*" & Nom$ & Datation & "!R2C10:R" & nbcount1 & "C10=" & Cells(2, 2).Value & ")*(" & Nom$ & Datation & "!R2C14:R" & nbcount1 & "C14=" & Cells(3, 1).Value & ")*1)"
'Selection.FormulaArray = Formule$
MsgBox Formule$
End Sub

Il vous faudra certainement revoir la construction de la chaîne Formule$.
Je suis curieux de savoir si cela peut fonctionner aussi j'attends de vos nouvelles.

Cordialement.

PMO
Patrick Morange

 

[dieu08]

Re : FormulaArray avec + de 255 caractères

Bonjour,
Avec mes excuses pour le délai de réponse sur votre poste, j'ai de nombreux soucis avec d'autres parties de mon code qui arrivent avant cet autre souci.

Je reviendrai vers vous dès que j'en serai arrivé à ce point.

Désolé.

Dieu08