Formulaarray et networkdays

[nfo]

Bonjour,
Je cherche à l'heure actuelle à calculer le nombre de jours ouvrés entre deux dates, en prenant en compte 2 calendriers de jours fériés (celui du pays de mon client et celui du pays du marché sur lequel il souhaite commercer).
Le choix de ces 2 calendriers étant dynamique (dépendant du marché et du client), je construis la formule en VBA.

Dans la fonction NETWORKDAYS, le paramètre Holiday doit être soit un tableau, soit une liste de valeurs. Dans mon cas, avec 1 calendrier ça marche. Lorsque je veux utiliser 2 calendriers, je suis du coup obligé (enfin je pense) de convertir ces 2 calendriers en une liste de valeurs. Et là, je dois passer par Formulaarray.

J'ai parcouru divers forums qui ont apporté des réponses (taille de la formule, utilisation de la notation R1C1, ...) mais je rencontre encore cette fameuse erreur 1004 "Unable to set the Formulaarray property"
A noter qu'en entrant manuellement la formule (sans la notation R1C1) ça fonctionne.

Voici le code que j'utilise actuellement (c'est la partie en rouge qui pose problème, le reste est la gestion des heures d'ouverture du marché)

l_formula = "=(NETWORKDAYS(trunc(R7C23);trunc(R7C24);LARGE((Markets!R2C2:R241C2;Markets!R4C5:R4C6);ROW(INDIRECT(""1:""&ROWS(Markets!R2C2:R241C2)+ROWS(Markets!R4C5:R4C6)))))-1)*(Markets!R3C5-Markets!R2C5)+(R7C24-trunc(R7C24)-MAX(R7C23-trunc(R7C23);Markets!R2C5))"
sheetConso.Cells(currentRowConso, 27).FormulaArray = l_formula

Help, je désespère. Je viens à penser à aggréger mes calendriers :'(
Si vous voyez une autre solution ou une autre formule pour calculer ce nombre de jours ouvrés sur 2 calendriers, je suis aussi preneur 🙂

Un grand merci d'avance !

 

[Gorfael]

Re : Formulaarray et networkdays

Salut nfo et le forum

A noter qu'en entrant manuellement la formule (sans la notation R1C1) ça fonctionne.

Je ne suis pas sûr de pouvoir répondre.
Juste une question : tu as une formule qui fonctionne et une qui ne fonctionne pas. Pourquoi ne donner que celle qui ne fonctionne pas ?
Il y a une logique qui m'échappe...
A+

 

[nfo]

Re : Formulaarray et networkdays

Salut
La formule que je t'ai donnée fonctionne si on la saisie directement dans Excel (donc sans VBA), sauf que, sauf erreur de ma part, il y a une adaptation à faire : en saisie directe il faut utiliser la notation A1 et en VBA/FormulaArray, il faut utiliser la notation R1C1

En clair : la formule
=(NETWORKDAYS(TRUNC(W7);TRUNC(X7);LARGE((Markets!B2:B241;Markets!E5:E6);ROW(INDIRECT("1:"&ROWS(Markets!B2:B241)+ROWS(Markets!E5:E6)))))-1)*(Markets!E3-Markets!E2)+(X7-TRUNC(X7)-MAX(W7-TRUNC(W7);Markets!E2))
marche en saisie manuelle + shift-ctrl-enter

Elle ne marche pas si je tente de la saisir via VBA
sheetConso.Cells(currentRowConso, 27).FormulaArray = l_formula

Et elle ne marche pas non plus en remplaçant les X7, W7, ... par R7C24, R7C23, ...

A+

 

[Gorfael]

Re : Formulaarray et networkdays

Salut nfo et le forum

=(NETWORKDAYS(TRUNC(W7);TRUNC(X7);LARGE((Markets!B2:B241;Markets!E5:E6);ROW(INDIRECT("1:"&ROWS(Markets!B2:B241)+ROWS(Markets!E5:E6)))))-1)*(Markets!E3-Markets!E2)+(X7-TRUNC(X7)-MAX(W7-TRUNC(W7);Markets!E2))
marche en saisie manuelle + shift-ctrl-enter

Déjà, ça me dit que tu as un excel en anglais : c'est pas inutile de le préciser.

- c'est une formule validée en matricielle => .FormulaArray

- On est en VBA : la notion d'adressage relatif ou absolu d'une formule n'est pas forcément la même : Je m'explique : fais un test :

Sub test()
Dim X As Long
'Range("A1").FormulaArray = "=sum((B1:B5)*(C1:C5))"
For X = 1 To 5
    Range("B" & X).Formula = "=A1"
    Range("C" & X).Formula = "=R1C1"
    Range("D" & X).Formula = "=R[1]C[1]"
Next X
End Sub

en B5, tu as "=A1", en C5 : "=$A$1". en D5 : "=E6"
C'est pour ça qu'excel utilise les référence R1L1.

Ne connaissant pas la cellule où se trouve la formule, difficile de la refaire. alors, ça va être ton boulot 😉

Tu sélectionnes la cellule contenant la bonne formule. Pour lr reste, je te donne la démarche en français, à toi d'adapter :
Menu Outils>>options
Onglet "Général" => style de référence L1C1 coché => tu valides
Menu Outils>>Macro>>Nouvelle macro (tu notes le nom de la macro, "Macrox"), tu valides
<F2> (ou double-clic sur la cellule) sans rien changer à ta formule.
Tu valides par <Ctrl>+<Shift>+<Enter>
Tu arrêtes la macro
Tu enlèves le style R1L1
Tu vérifies que ta cellule donne toujours le bon résultat (y a pas de raison)
Il ne te reste plus qu'à aller des Menu Outils>>Macro>>Macros
Chercher la Macrox et cliquer sur modifier => Miracle : tu as ton code
reste plus qu'à le mettre dans dans macro et le tester, puis nettoyer (suppression module ou/et macro)
A+

 

[nfo]

Re : Formulaarray et networkdays

Super !
L'erreur venait des ";" au lieu de "," dans la fonction.
Je ne comprends pas trop pourquoi dans ce contexte il faut utiliser la virgule, mais tip top !
Un grand merci pour ton aide !!😎

 

[Gorfael]

Re : Formulaarray et networkdays

Salut nfo et le forum
Moi non plus, parce que normalement, en anglais (langage de base du VBA, malheureusement), le séparateur ",".
Tu dois avoir une modification des paramètres régionaux.
Normalement, si tu copies ta formule sur une feuille de calcul, tu peux l'utiliser telle quelle dans VBA en utilisant FormulaLocal.
mais dans ton cas, il aurait fallut extraire les références en adressage relatif, pour les remettre de manière correcte dans la formule. pour reprendre mon exemple

Sub test()
Dim X As Long
For X = 1 To 5
    Range("B" & X).Formula = "=A1"
Next X
End Sub

Deviendrait

Sub test()
 Dim Cel As range
 For each cel in [B1:B5]
     cel.FormulaLocal = "=" & cel.offset(0,-1).address(0,0)
 Next cel
 End Sub

Sur ta formule, sans maîtriser réellement tous les termes, je préfère passer par L1C1.
A+