XL 2010 Sommeprod en VBA

[CISCO]

Bonjour à tous


Je voudrais dans une macro mettre dans une variable x la valeur correspondant à un SOMMEPROD, la plage utilisée étant un tableau (constitué d'une seule ligne, et de 32 colonnes) et pas un Range. J'ai fait avec

x = Evaluate(" Sum(((Weekday(Tabjour) = 1) + (Weekday(Tabjour) = 7)) * 2 + ((Weekday(Tabjour) <> 1) * (Weekday(Tabjour) <> 7)) * 10)")

mais cela ne fonctionne pas (j'obtiens x=erreur 2029).

Cela doit additionner des 2 pour les samedis et les dimanches contenus dans le tableau Tabjour, et des 10 pour les autres jours de ce tableau.

Si je fais le calcul dans une cellule avec une formule, cela me donne

SOMMEPROD(((JOURSEM(D5:AH5)=1)+(JOURSEM(D5:AH5)=7))*2+((JOURSEM(D5:AH5)<>1)*(JOURSEM(D5:AH5)<>7))*10)

mais mon but n'est pas d'avoir le résultat dans une cellule mais dans la variable x.

Merci d'avance

@ plus

Je ne met pas le fichier car cela ne me semble pas indispensable, mais, s'il le faut vraiment... il est là, post #29
https://www.excel-downloads.com/threads/planning-de-garde.20011007/page-2

 

[pierrejean]

Bonjour Cisco

A tester:

For Each cel In Range("D5:Ah5")
 If Weekday(cel.Value) = 1 Then x = x + 1
 If Weekday(cel.Value) = 7 Then x = x + 2
 If Weekday(cel.Value) <> 1 And Weekday(cel.Value) <> 7 Then x = x + 10
Next

 

[Lolote83]

Salut Cisco.

x = Evaluate(" Sum(((Weekday(Tabjour) = 1) + (Weekday(Tabjour) = 7)) * 2 + ((Weekday(Tabjour) <> 1) * (Weekday(Tabjour) <> 7)) * 10)")

Peut être un signe = oublié

x = Evaluate("=Sum(((Weekday(Tabjour) = 1) + (Weekday(Tabjour) = 7)) * 2 + ((Weekday(Tabjour) <> 1) * (Weekday(Tabjour) <> 7)) * 10)")

Perso, j'utilise un sommeprod en vba et voici mon code

xResult = Evaluate("=SUMPRODUCT((Datas!TableauAnnuel=$C" & xLig & ")*(Datas!TableauSemaine=" & xLettreColonne & "$3)*($D" & xLig & ")*($F" & xLig & "))")

Je n'ai pas testé
@+ Lolote83

 

[job75]

Bonjour CISCO, Pierre, Lolote83

Si le tableau Tabjour ne contient que des dates ceci fonctionnera bien :

ThisWorkbook.Names.Add "Tabjour", Tabjour 'création du nom défini dans le classeur
x = Evaluate("Sum((Weekday(Tabjour,2)>5)*2+(Weekday(Tabjour,2)<6)*10)")

A+

 

[job75]

Re,

Si Tabjour ne contient pas que des dates compléter comme ceci :

ThisWorkbook.Names.Add "Tabjour", Tabjour 'nom défini dans le classeur
x = Evaluate("Sum(If(IsNumber(Day(Tabjour)),(Weekday(Tabjour,2)>5)*2+(Weekday(Tabjour,2)<6)*10))")

Edit 1 : avec un seul test sur WeekDay(Tabjour,2) c'est un peu plus simple :

ThisWorkbook.Names.Add "Tabjour", Tabjour 'nom défini dans le classeur
x = Evaluate("Sum(If(IsNumber(Day(Tabjour)),If(Weekday(Tabjour,2)>5,2,10)))")

Il est intéressant de noter les 2 manières de définir Tabjour :

- avec Set Tabjour = [D5:AH5] les cellules vides sont comptées comme des samedis (00/01/1900)

- avec Tabjour = [D5:AH5] les cellules vides ne sont pas comptées (elles donnent des #N/A dans le nom défini).

Edit 2 : ce code ne compte pas les cellules vides quelle que soit la manière de definir Tabjour :

ThisWorkbook.Names.Add "Tabjour", Tabjour 'nom défini dans le classeur
x = Evaluate("Sum(If(IsNumber(1/Day(Tabjour)),If(Weekday(Tabjour,2)>5,2,10)))")

A+

 

[job75]

Re,

Avec des boucles, un code très général :

Dim Tabjour, ncol%, i&, j%, x
Tabjour = [D5:AH5] 'plage à adapter
If IsArray(Tabjour) Then
  ncol = UBound(Tabjour, 2)
  For i = 1 To UBound(Tabjour)
    For j = 1 To ncol
      If IsDate(Tabjour(i, j)) Then x = x + IIf(Weekday(Tabjour(i, j), 2) > 5, 2, 10)
  Next j, i
Else
  If IsDate(Tabjour) Then x = IIf(Weekday(Tabjour, 2) > 5, 2, 10)
End If

A+

 

[CISCO]

Bonsoir

Bonjour Cisco

A tester:

For Each cel In Range("D5:Ah5")
If Weekday(cel.Value) = 1 Then x = x + 2
If Weekday(cel.Value) = 7 Then x = x + 2
If Weekday(cel.Value) <> 1 And Weekday(cel.Value) <> 7 Then x = x + 10
Next

Effectivement, j'avais aussi pensé à cette possibilité, à savoir passer par un For each cel... en changeant au fur et à mesure la valeur de x. Mais j'espèrai pouvoir faire avec l'équivalent de SOMMEPROD...

Merci et @ plus

 

[job75]

Re,

Tu pousses un peu CISCO...

Et j'ai fait 2 Edits intéressants à mon post #5.

A+

 

[CISCO]

Bonsoir

Salut Cisco.

Peut être un signe = oublié

@+ Lolote83

J'ai lu sur le net que justement, il ne fallait pas de signe = dans ce cas. Je vais tester.

Merci et @ plus

P.S : Je viens de tester. Cela ne passe pas avec le signe égal. J'obtiens, en passant la souris sur le code, le message x = erreur 2015.

 

[CISCO]

Bonsoir

Re,

Si Tabjour ne contient pas que des dates compléter comme ceci :

Edit 1 : avec un seul test sur WeekDay(Tabjour,2) c'est un peu plus simple :

ThisWorkbook.Names.Add "Tabjour", Tabjour 'nom défini dans le classeur
x = Evaluate("Sum(If(IsNumber(Day(Tabjour)),If(Weekday(Tabjour,2)>5,2,10)))")

Merci pour ces différentes possibilités, avec les nombreux petits plus habituels. 🙂. Je vais tester de ce pas...

@ plus

 

[job75]

Re CISCO,

Ah bon, je me disais...

A+

 

[job75]

Re,

Peut être un signe = oublié
Je n'ai pas testé

Pourtant pas bien difficile :

Sub a()
x = Evaluate("=1") 'Evaluate("1")
MsgBox x
End Sub

P.S : Je viens de tester. Cela ne passe pas avec le signe égal. J'obtiens, en passant la souris sur le code, le message x = erreur 2015.

Tu as dû tester n'importe quoi.

A+

 

[CISCO]

Bonsoir

: avec un seul test sur WeekDay(Tabjour,2) c'est un peu plus simple :

ThisWorkbook.Names.Add "Tabjour", Tabjour 'nom défini dans le classeur
x = Evaluate("Sum(If(IsNumber(Day(Tabjour)),If(Weekday(Tabjour,2)>5,2,10)))")

Et si le nom Tabjour n'est pas défini par l'intermédiaire du gestionnaire de noms, mais dans le code, sous la forme

.....
Dim Tabjour ()
....
Redim Tabjour (1, 1 to 32)
.......
Tabjour = Range("C5:AH5").Value

@ plus

 

[job75]

Re CISCO,

Tâche de comprendre mes codes...

Dans mes solutions bien sûr que Tabjour est une variable VBA.

Mais dans l'évaluation de la formule on ne peut pas utiliser cette variable, par contre on peut y utiliser le nom d'un nom défini dans le classeur.

C'est bien pour ça que je crée ce nom.

Si je lui donne le même nom que la variable c'est parce que c'est plus amusant, ce n'est pas obligatoire...

A+

 

[job75]

Re,

Avant d'aller dormir, je reviens sur mon post #5 au sujet des cellules vides.

1) Si Tabjour est défini avec le Set (objet Range) ma dernière formule :

Set Tabjour = [D5:AH5]
ThisWorkbook.Names.Add "Tabjour", Tabjour 'nom défini dans le classeur
x = Evaluate("Sum(If(IsNumber(1/Day(Tabjour)),If(Weekday(Tabjour,2)>5,2,10)))")

fonctionne bien avec le calendrier 1900 mais pas avec le calendrier 1904.

Toi qui es spécialiste des formules tu comprendras facilement pourquoi.

Pour que cela fonctionne quel que soit le calendrier utilisé :

Set Tabjour = [D5:AH5]
ThisWorkbook.Names.Add "Tabjour", Tabjour 'nom défini dans le classeur
x = Evaluate("Sum(If(IsNumber(Day(""""&Tabjour)),If(Weekday(Tabjour,2)>5,2,10)))")

2) Si Tabjour est défini sans le Set (tableau VBA), comme déjà dit, les cellules vides donnent des #N/A dans le nom défini, elles ne sont donc jamais prises en compte.

Je pense que maintenant tu devrais mieux comprendre.

Bonne nuit.