Opérations avec WorksheetFunction.WeekNum

Le 13/07/2017 à 16:35

Bonjour à tous.

Je suis nouveau ici et débutant en VBA. J'ai eu 2 formations par mon boulot, et me voilà parti à essayer de faire quelques macros VBA sous Excel 2007 pour me faciliter le travail (même pas peur!)

J'en ai fait 5 ou 6 sans difficultés majeures, mais là je sèche un peu. Je voulais faire une boucle: en colonne j'ai des quantités par date, et je voulais regrouper ces quantités par semaine. En cherchant un peu j'ai trouvé que je pouvais utiliser WorksheetFunction.WeekNum mais ma macro bloque, j'ai une erreur. Voici ce que j'ai fait:

Sub EtatDeblocage()
    Rows(2).Delete
    Range("A1").Value = "IFLS"
    Range("B1").Value = "DESIGNATION"
    NbColonnes = Range("C1").CurrentRegion.Columns.Count
    NbLignes = Range("C1").CurrentRegion.Rows.Count
    Range("D1").Select
    Do While ActiveCell <> ""
        If WorksheetFunction.WeekNum(ActiveCell, 2) <> WorksheetFunction.WeekNum(ActiveCell.Offset(0, -1), 2) Then
            Selection.EntireColumn.Insert
            ActiveCell.Value = "ENG S" & WorksheetFunction.WeekNum(ActiveCell.Offset(0, -1), 2) - 1

            For i = 1 To NbLignes
                r = 0
                j = 1
                   Do While WorksheetFunction.WeekNum(ActiveCell.Offset(0, -j), 2) = WorksheetFunction.WeekNum(ActiveCell.Offset(0, -j - 1), 2)
                    r = r + ActiveCell.Offset(i, -j).Value
                    j = j + 1
                    Loop
                ActiveCell.Offset(i, 0).Value = r
            Next

            ActiveCell.Offset(0, 2).Select
                Else
            ActiveCell.Offset(0, 1).Select
        End If
    Loop
    ActiveCell.Value = "ENG S" & WorksheetFunction.WeekNum(ActiveCell.Offset(0, -1), 2) - 1

End Sub

Ça bloque à : Do While WorksheetFunction.WeekNum(ActiveCell.Offset(0, -j), 2) = WorksheetFunction.WeekNum(ActiveCell.Offset(0, -j - 1), 2)

J'ai une erreur 1004: "Impossible de lire la propriété WeekNum de la classe WorkSheetFunction"

Je ne comprends pas où est le probleme, mais ça doit être un truc tout bête à tous les coups...

Pourriez-vous m'éclairer?

Mais allez-y doucement avec moi, je suis débutant hein!

Merci par avance!

Le 13/07/2017 à 17:57

De toute façon,

WorksheetFunction.WeekNum(ActiveCell.Offset(0, -j), 2)

donnera une erreur de semaine (ce n'est pas "ISO")

Remplace par

Format(ActiveCell.Offset(0, -j), "ww", , vbFirstFourDays)

et vérifie au passage que ton offset ne t'envoie pas en dehors des clous. Selon ActiveCell et -j ...

Un essai comparatif

17semaine-macro.xlsm (14.02 Ko)
Le 13/07/2017 à 23:46

Ok, merci de ta réponse. Oui effectivement j'ai remarqué que la semaine renvoyée n'est pas la bonne, c'est une semaine plus tard, c'est pour ça que pour nommer ma colonne j'ai retranché 1 à la valeur renvoyée et là ça fonctionne bien:

ActiveCell.Value = "ENG S" & WorksheetFunction.WeekNum(ActiveCell.Offset(0, -1), 2) - 1

Mais ce n'est pas très important pour ce que je cherche à faire, je voulais additionner les valeurs en ligne de chacune de mes références tant que ces valeurs sont dans la même semaine, peu importe le numéro de la semaine (tant pis s'il est pas bon).

Je ne sais pas trop si c'est très clair ce que je raconte... j'essaye d'expliquer autrement:

Je récupère ma requête dans un tableau croisé (non dynamique) Excel, à gauche j'ai mes références, et en haut j'ai mes dates et à l'intersection référence/date j'ai la quantité commandée par mes clients pour cette référence à cette date. Mais en fait la date ne m'interesse pas, je voudrais la quantité globale commandée par semaine, pour chacune de mes refs.

Par exemple mon tableau je le récupère sous cette forme:

Ref ---- Désignation -------- 24/07/2017 - 25/07/2017 - 28/07/2017 - 31/07/2017 - 02/08/2017

123456 - Stylos bleus -------- 146 ------------- 124 ------------ 188 ------------ 172 ------------- 108

543957 - Craies blanches ------ 82 ------------ 76 -------------- 104 ------------- 68 ------------- 96

etc...

Et je voudrais obtenir quelque chose comme ça à la place:

Ref ---- Désignation ----------- ENG S30 ----------- ENG S31

123456 - Stylos bleus ------------- 458 --------------- 280

543957 - Craies blanches --------- 262 --------------- 164

Donc dans ma macro que j'ai posté précédemment, si j'enlève ma boucle For... Next, ça me créé bien mes colonnes ENG S30, ENG S31, etc. juste après les colonnes dates de la S30, de la S31, etc. (que je supprimerai à la fin - pas encore écrit, je procède par étapes). Et ça, ça marche bien.

Ensuite j'ai voulu rajouter la boucle For... Next pour faire le total de mes quantités, par semaine, mais c'est la que ça bloque. J'ai voulu faire: "tant que cette date est dans la même semaine que la précédente, il faut ajouter la quantité à ma variable r".

Et VBA me donne une erreur au moment où je compare ma date à la précédente:

Do While WorksheetFunction.WeekNum(ActiveCell.Offset(0, -j), 2) = WorksheetFunction.WeekNum(ActiveCell.Offset(0, -j - 1), 2)

erreur 1004: "Impossible de lire la propriété WeekNum de la classe WorkSheetFunction"

Voilà... Alors après, je répète que je suis débutant, je me suis peut-être compliqué la tâche, il y a peut-être plus simple!

Merci par avance de m'aiguiller soit sur comment corriger mon erreur, soit sur une méthode plus simple!

Bon week-end!

Le 14/07/2017 à 05:57
Steelson a écrit :

et vérifie au passage que ton offset ne t'envoie pas en dehors des clous. Selon ActiveCell et -j ...

    
Range("D1").Select
'...                
j = 1
'...

Do While WorksheetFunction.WeekNum(ActiveCell.Offset(0, -j), 2) = WorksheetFunction.WeekNum(ActiveCell.Offset(0, -j - 1), 2)

ActiveCell.Offset(0, -j - 1) égal donc Range("D1").Offset(0, -2), soit Range("B1") : y a t'il une date en B1 ? et en C1 ?

Le 14/07/2017 à 06:55
Prock78 a écrit :

Bonjour à tous.

Je suis nouveau ici et débutant en VBA.

ton code est déjà bien structuré, c'est pas mal

n'hésite pas à ajouter une ligne comme ceci :

debug.print ActiveCell.Offset(0, -j) & " - " & ActiveCell.Offset(0, -j -1)

en ouvrant la fenêtre de débuggage (Ctrl + G) quand une ligne plante et juste avant celle-ci, cela te donnera une bonne idée de la cause

Prock78 a écrit :

Oui effectivement j'ai remarqué que la semaine renvoyée n'est pas la bonne, c'est une semaine plus tard, c'est pour ça que pour nommer ma colonne j'ai retranché 1 à la valeur renvoyée et là ça fonctionne bien:

Mais ce n'est pas très important pour ce que je cherche à faire, je voulais additionner les valeurs en ligne de chacune de mes références tant que ces valeurs sont dans la même semaine, peu importe le numéro de la semaine (tant pis s'il est pas bon).

Et je voudrais obtenir quelque chose comme ça à la place:

Ref ---- Désignation ----------- ENG S30 ----------- ENG S31

123456 - Stylos bleus ------------- 458 --------------- 280

Oui et NON

En effet ta question ne portait pas là-dessus et puisque tu compares cela peut ne pas avoir d'importance.

Mais :

  • tu ajoutes 1 donc c'est que tu souhaites quand même avoir la bonne valeur
  • et tu l'utilises dans les en-tête de tes colonnes

Ajouter 1 n'est pas la solution car cela donnera un mauvais résultat 3 fois sur 7.

La semaine 1 (ISO) est celle comportant une majorité de jours de la nouvelle année, donc 4 mini. Les années commençant un vendredi, samedi ou dimanche sont donc rattachées en semaine à l'année précédente. Les ricains s'en fichaient pas mal ! mais microdoux vient de rajouter quand même une fonction =NO.SEMAINE.ISO(____) qui est une fonction qui donne le résultat correct. Par contre WeekNum n'a semble-t-il pas évolué, et c'est pourquoi j'insiste pour que tu changes quand même ne serait-ce que pour l'avenir et les bonnes habitudes à prendre. Je me suis fait piéger un jour en gestion de production par un prestataire pourtant expert en excel !

Le 14/07/2017 à 08:40

Bonjour à tous,

Entièrement d'accord avec toi Steelson.

Cependant Format(dat1, "ww", , vbFirstFourDays) retourne des valeurs erronées à certaines dates de fin d'année.

Par exemple le 29/12/2031

weeknum(dat1 ) semble retourner toujours la bonne valeur, NO.SEMAINE.ISO sans doute aussi.

eric

Le 14/07/2017 à 08:50

Merci pour ce tuyau !

Généralement, je ne fais pas ce genre de calcul par VBA, j'utilise toujours au maximum les formules dans le tableur lui-même et je n'automatise que les taches répétitives (en dehors de quelques fonctions personnalisées parfois).

@Prock78 : voici donc une correction plus simple sur le calcul de la semaine

Cela n'enlève rien au sujet principal qui est le débogage du programme ... cf les pistes évoquées ci-dessus.

Le 14/07/2017 à 11:28

OK, merci beaucoup. Je viens de comprendre ce que vous voulez dire: ma macro avec WeekNum va fonctionner cette année mais pas forcément une autre année, à moins de la modifier à ce moment là. J'essayerai donc de remplacer ça par No.Semaine.Iso que je ne connaissais pas, je regarde ça de plus près lundi au bureau.

ActiveCell.Offset(0, -j - 1)

égal donc Range("D1").Offset(0, -2), soit Range("B1") : y a t'il une date en B1 ? et en C1 ?

Sauf erreur de ma part, mon ActiveCell n'est plus D1, car j'ai sélectionné une colonne pour en insérer une nouvelle et attribuer une valeur (ENG S30 par exemple), ce qui a très bien fonctionné (à part qu'il faudra que je remplace WeekNum par No.Semaine.Iso du coup):

Selection.EntireColumn.Insert
ActiveCell.Value = "ENG S" & WorksheetFunction.WeekNum(ActiveCell.Offset(0, -1), 2) - 1

En fait si je reprends l'exemple que j'ai donné à mon post précédent, mon ActiveCell doit être F1 (en pratique peut aussi être G1 ou H1 selon mon tableau de base).

Mais quand même, ta remarque est pertinente et j'ai peut-être compris d'où vient l'erreur grâce à toi. En effet dans ma boucle For... Next, je compare les dates en en-tête de colonne (plus exactement les semaines qui correspondent à ces dates). Et effectivement ça marche peut-être au début, mais quand ma boucle arrive au point ou elle compare C1 (qui est bien une date) à B1 (qui n'est pas une date) c'est peut-être à cause de ça que l'erreur se produit.

Je pense qu'il faudrait que, à l'intérieur de ma boucle For... Next, je rajoute la condition que les valeurs que je transforme en N° de semaine/compare soient bien de type date.

Alors, comment je peux dire ça en language VBA? Quelque chose comme ça peut-être?

Do While IsDate(ActiveCell.Offset(0, -j) And IsDate(ActiveCell.Offset(0, -j - 1) And WorksheetFunction.WeekNum(ActiveCell.Offset(0, -j), 2) = WorksheetFunction.WeekNum(ActiveCell.Offset(0, -j - 1), 2)
r = r + ActiveCell.Offset(i, -j).Value
j = j + 1
Loop

Je ne peux pas tester avant lundi au bureau, je n'ai pas mes fichiers ici chez moi, et je n'ai même pas Excel (j'ai Open Office à la maison, pas sûr que les macros soient compatibles...).

En tout cas merci beaucoup Steelson et eriiic! J'apprécie beaucoup votre aide pour mon problème!

Le 14/07/2017 à 17:59

Et n'oublie pas de glisser unDebug.Print "quelque chose"avant une erreur ou une ligne pour bien comprendre ce qui se passe !

Ensuite tu reviendras ici pour résoudre des sujets qu'auront postés d'autres excelleurs ...

Le 14/07/2017 à 18:33

A mon avis ça va planter pareil car excel va évaluer systématiquement les 4 tests du Do While.

Il faut d'abord tester les dates et si ok tu peux boucler sur les n° de semaine.

eric

Le 15/07/2017 à 01:08

Ah donc il faudrait que je le fasse en 2 étapes alors?

Bon, j'essaye de voir tout ça lundi si j'ai le temps. J'essayerai de tester le Debug.Print aussi si ça peut m'aider.

Merci en tout cas!

Le 17/07/2017 à 14:51

Bonjour!

Je vous donne un peu la suite de mes aventures après avoir travaillé sur ma macro ce matin.

Déjà, j'ai voulu remplacer mes WeekNum par No.Semaine.Iso -> ça ne fonctionne pas. Après recherche, il semblerait que ça ne marche qu'à partir d'Excel 2013, j'ai le 2007 au boulot.

Idem pour weeknum(dat1 ,21) qui serait aussi incompatible avec Excel 2007.

J'ai testé le "debug.print", ça me donnait bien les valeurs que j'attendais, donc ça ne m'a pas trop aidé au final.

J'ai cherché une solution, et en fait ça m'est venu en simplifiant l'expression dans ma boucle. Je me suis dit que je n'avais pas forcément besoin de savoir si la semaine de telle date en en-tête était égale à celle de la case précédente, il me suffisait juste de savoir si l'en-tête était bien une date, sinon, c'est qu'on changeait forcément de semaine (voir le début de ma macro).

Du coup voilà ce que j'ai écrit:

Sub EtatDeblocage()
    Rows(2).Delete
    Range("A1").Value = "IFLS"
    Range("B1").Value = "DESIGNATION"
    NbColonnes = Range("C1").CurrentRegion.Columns.Count
    NbLignes = Range("C1").CurrentRegion.Rows.Count
    Range("D1").Select
    Do While ActiveCell <> ""
        If WorksheetFunction.WeekNum(ActiveCell, 2) <> WorksheetFunction.WeekNum(ActiveCell.Offset(0, -1), 2) Then
            Selection.EntireColumn.Insert
            ActiveCell.Value = "ENG S" & WorksheetFunction.WeekNum(ActiveCell.Offset(0, -1), 2) - 1
            For i = 1 To NbLignes - 1
                r = 0
                j = 1
                    Do While IsDate(ActiveCell.Offset(0, -j))
                    r = r + ActiveCell.Offset(i, -j).Value
                    j = j + 1
                    Loop
                ActiveCell.Offset(i, 0).Value = r
            Next

            ActiveCell.Offset(0, 2).Select
            Else
            ActiveCell.Offset(0, 1).Select
        End If
    Loop
    ActiveCell.Value = "ENG S" & WorksheetFunction.WeekNum(ActiveCell.Offset(0, -1), 2) - 1

        For i = 1 To NbLignes - 1
            r = 0
            j = 1
                Do While IsDate(ActiveCell.Offset(0, -j))
                    r = r + ActiveCell.Offset(i, -j).Value
                    j = j + 1
                Loop
            ActiveCell.Offset(i, 0).Value = r
        Next

End Sub

Et là ça marche bien! Juste il faudra que je change éventuellement mon histoire de WeekNum -1 ou pas -1 d'une année sur l'autre si besoin, car j'ai pas trouvé comment faire autrement sur Excel 2007 (enfin si, j'ai trouvé une formule quelque part, mais ça me paraissait compliqué pour pas grand chose).

J'ai pas encore fini, il faudra que je mette en forme, que je supprime mes colonnes de dates et que je rajoute des colonnes Stock, etc... mais au moins je n'ai plus d'erreur pour l'instant et ça fait ce que je veux.

Encore merci pour votre aide en tout cas!

Le 17/07/2017 à 15:21

Pour les versions anciennes d'excel, tu peux prendre cette formule pour déterminer le n° de semaine.

Mais oui, tout le monde utilise cette formule

Il faudrait surtout y ajouter l'année de "référence" ... car le 1er janv 2017 c'est la semaine 52 de l'année 2016.

Je n'aime pas trop mettre des formules en VBA, et je te conseille de rajouter une ligne en dessous de la date ou à droite pour justement en donner la valeur.

8semaine.zip (6.98 Ko)
Le 17/07/2017 à 17:33

Merci. A la limite ce qui serait pratique faudrait que j'en fasse une fonction sous VBA pour pouvoir m'en resservir quand je veux. Je vais étudier ça.

Rechercher des sujets similaires à "operations worksheetfunction weeknum"