Dois-je faire une "Loop" en VBA ou une formule ???

[Eltech]

Bonjour,
Je suis vraiment Nouveau en VBA, pour le pas dire à ma première tentative...
Je coterais mes compétences sur Excel disons à 8/10 dans mon travail mais comparativement au Pro sur ce forum sûrement que je descend à 1-2/10.

Ma question est la suivante (Voir exemple plus bas): J'ai une série de date en colonne de A1 à A5 et une série de chiffres de B1 à B5...(qui vont ensemble)

Date Nbr de congé
2011-05-13 5
2011-05-14 2
2011-05-15 1
2011-05-18 4
2011-05-20 2

Je voudrais recréer une colonne qui me donnerais la liste des dates du 2011-05-13 au 2011-05-20 incluant les dates manquantes, et dans une autre colonne le nombre correspondant et d'y écrire "0" si la date était manquante dans la liste de départ.

J'espère que vous comprendrez ce que je veux dire...car sa me serait d'une grande utilité. Car dans la vrai vie les lignes vont jusqu'à 900 !!!!

Merci à l'avance des conseils !!!

David

 

[david84]

Re : Dois-je faire une "Loop" en VBA ou une formule ???

Re
simplement pour saluer la proposition de staple🙂.

Effectivement le code a Staple est plus rapide
Du fait qu'il n'y a pas de recherche car le code tient compte que la colonne A est trier

Je n'ai pas tout suivi mais si j'ai bien compris ta remarque, pas grand chose à modifier pour pallier ce point :

Sub a_TER()
Dim d As Date, f As Date, nbj&, r As Range, P$

Set r = Range([A2], [A65536].End(3))

'f = [A65536].End(3)
f = Application.WorksheetFunction.Max(r)
'd = [A2]
d = Application.WorksheetFunction.Min(r)
nbj = f - d

'[D2].Value = [A2].Value
[D2].Value = d
[D2].AutoFill [D2].Resize(nbj + 1), 5

P = r.Resize(, 2).Address(4, 4, xlR1C1)

With [E2]
    .Formula = "=IF(ISNA(VLOOKUP(RC[-1]," & P & ",2,0)),0,VLOOKUP(RC[-1]," & P & ",2,0))"
    .AutoFill Destination:=Range("E2").Resize([D65536].End(3).Row - 1), Type:=xlFillDefault
    .Value = .Value
End With
End Sub

A+

 

[Staple1600]

Re : Dois-je faire une "Loop" en VBA ou une formule ???

Re


En tenant des remarques d'everybody
Résultat chez moi:
0,104000000001804

Sub a_QUATRO()
Dim dstart As Double, d As Date, f As Date, nbj&, r As Range, P$
Set r = Range([A2], [A65536].End(3))
With Application
    .ScreenUpdating = False
    dstart = Timer
    f = .WorksheetFunction.Max(r): d = .WorksheetFunction.Min(r)
    nbj = f - d: [D2] = d
    [D2].AutoFill [D2].Resize(nbj + 1), 5
    P = r.Resize(, 2).Address(4, 4, xlR1C1)
    With [E2]
        .Formula = "=IF(ISNA(VLOOKUP(RC[-1]," & P & ",2,0)),0,VLOOKUP(RC[-1]," & P & ",2,0))"
        .AutoFill Destination:=.Resize([D65536].End(3).Row - 1), Type:=xlFillDefault
        With .Resize([D65536].End(3).Row - 1)
            .Value = .Value
        End With
    End With
    dstart = Timer - dstart
    .ScreenUpdating = True
Debug.Print dstart
End With
End Sub

 

[ROGER2327]

Re : Dois-je faire une "Loop" en VBA ou une formule ???

Bonjour à tous

Je me méfie des comparatifs de vitesse sur des codes qui ne font pas la même chose. Et c'est le cas ici. Certains codes n'acceptent pas une liste de dates non ordonnées. D'autres plantent avec une liste ordonnée en décroissant. Quelques uns ne supportent pas une liste vide ou renvoient un résultat erroné. On peut aussi trouver une colonne chargée de formules là où il n'y en avait pas. La plupart nécessite des interventions en de multiples endroits du code dès qu'on veut modifier l'emplacement de la zone de données ou de la zone de destination. Sans parler du "nettoyage" ou non de la zone de destination avant d'y coller des résultats.
Par ailleurs, le traitement des doublons (dont l'éventualité n'est pas exclue a priori) diffère d'une solution à l'autre, etc.
Bref, il faudrait que notre ami précisât ses hypothèses (liste triée ou non, existence de doublon, absence possible de donné pour certaines dates, …)
Attendons…​

ROGER2327
#5270

Mercredi 11 Merdre 138 (Saint Memnon, vidangeur - fête Suprême Quarte)
9 Prairial An CCXIX, 6,4792h - serpolet
2011-W21-6T15:33:00Z

 

[Eltech]

Re : Dois-je faire une "Loop" en VBA ou une formule ???

Mes dates sont le résultats d'une extraction dans PI system dans laquelle je demande "le nombre de casse par jour sur une machine à papier".
J'obtiens alors une liste de date (en colonne a), accompagné d'une valeur (en colonne B). L'extraction ne me donne pas de date s'il n'y a pas eu d'évènement. C'est pourquoi il manque des dates dans la liste.
Donc, si je veux faire la moyenne des casses/jr, il me faut aussi les dates manquantes (date ou le nombre est "0")

Est-ce que ça éclaire vos lanternes ??

Merci à tous de travailler fort pour ma cause !!!

 

[Habitude]

Re : Dois-je faire une "Loop" en VBA ou une formule ???

Non pas plus !
3 questions poser par Roger
Tu réponds à aucune d'entre elle

 

[Eltech]

Re : Dois-je faire une "Loop" en VBA ou une formule ???

Oui les dates sont en ordre chronologique (tri sur colonne "A"). Si un doublon est une répétition des dates, alors non pas de doublons. non Il n'y a pas d'absence de donnés, il y a juste les dates manquantes qu'on doit faire apparaitre et lui donné la valeur "0"

 

[ROGER2327]

Re : Dois-je faire une "Loop" en VBA ou une formule ???

Re-bonjour à tous

En clair, j'ai tout faux !

ROGER2327
#5272

Jeudi 12 Merdre 138 (Saintes Miches, catéchumènes - fête Suprême Quarte)
10 Prairial An CCXIX, 0,2967h - faux
2011-W21-7T00:42:43Z

 

[ROGER2327]

Re : Dois-je faire une "Loop" en VBA ou une formule ???

Suite…

Ceci dit, s'il s'agit seulement de…

…faire la moyenne des casses/jr (…)

et en supposant que les données sont dans la plage A1:B100, une simple formule comme

=SOMME(B1:B100)/(1+MAX(A1:A100)-MIN(A1:A100))

ne suffirait-elle pas ?​

ROGER2327
#5273

Jeudi 12 Merdre 138 (Saintes Miches, catéchumènes - fête Suprême Quarte)
10 Prairial An CCXIX, 0,3633h - faux
2011-W21-7T00:52:19Z

 

[Habitude]

Re : Dois-je faire une "Loop" en VBA ou une formule ???

Suite…

Ceci dit, s'il s'agit seulement de… et en supposant que les données sont dans la plage A1:B100, une simple formule comme

=SOMME(B1:B100)/(1+MAX(A1:A100)-MIN(A1:A100))

ne suffirait-elle pas ?​

ROGER2327
#5273

Jeudi 12 Merdre 138 (Saintes Miches, catéchumènes - fête Suprême Quarte)
10 Prairial An CCXIX, 0,3633h - faux
2011-W21-7T00:52:19Z

Je crois en effet que l'utilisation de la formule Moyenne sur la colonne C est superflus
Pas besoin d'afficher des dates pour considérer qu'elles existes.
je simplifierais ...

=SOMME(B:B)/(1+MAX(A:A)-MIN(A:A))

 

[Eltech]

Re : Dois-je faire une "Loop" en VBA ou une formule ???

Oui pour avoir la moyenne seulement ton idée semble OK.

Sauf que si je veux créer un graphique en histogramme et sélectionné A1 à A100 et B1 à B100, il faut qu'il y ait les dates manquantes si je veux obtenir des "0" au jours ou il n'y avait pas d'évènement (casse ds mon cas), c'est pourquoi je crois qu'il me faut absolument un code comme vous avez mentionné précédemment qui fera apparaitre mes dates manquantes dans une colonne quelconque avec la valeur "0" à la colonne suivante.

Me suivez-vous ??

 

[ROGER2327]

Re : Dois-je faire une "Loop" en VBA ou une formule ???

Re…

(…)
je simplifierais ...

=SOMME(B:B)/(1+MAX(A:A)-MIN(A:A))

C'est effectivement plus simple ! Et d'ici aujourd'hui, on sera peinard jusque vers 2190 avec Excel2003…​

Bonne nuit,

ROGER2327
#5274

Jeudi 12 Merdre 138 (Saintes Miches, catéchumènes - fête Suprême Quarte)
10 Prairial An CCXIX, 0,6161h - faux
2011-W21-7T01:28:43Z

 

[ROGER2327]

Re : Dois-je faire une "Loop" en VBA ou une formule ???

Re…

(…)

Me suivez-vous ??

De mieux en mieux.
Quand on sait exactement les tenants et les aboutissants, les choses sont plus simples.

Voyez une proposition dans le classeur joint.​

ROGER2327
#5275

Jeudi 12 Merdre 138 (Saintes Miches, catéchumènes - fête Suprême Quarte)
10 Prairial An CCXIX, 1,1537h - faux
2011-W21-7T02:46:08Z

 

[Eltech]

Re : Dois-je faire une "Loop" en VBA ou une formule ???

Vous êtes trop fort Roger 2327 !!!! C'est en plein ce que je veux...

Comment avez-vous fait pour passer des données en colonne A et B et en aboutir avec les colonne C et D avec les fameuse valeur "0" au date manquante en colonne "A" Pour la colonne "C" c,est sûr que j'aurais pu écrire la première date en A1 et faire A1+1 et étirer jusquà la dernière voulu...Mon problème était d'obtenir les valeur correspondante en colonne "D", provenant de la col "B"

Pour vous c'est peut-être basic comme calcul ou code, mais pour moi qui ne connais rien encore au VBA c'est extraordinaire voir un miracle!!! Je vais faire des recherches et me tenir sur ce site afin de m'apprivoisé au jargon et au code. Je ne veux pas venir vous voir juste quand je suis dans le trouble...mais pour dépanner des gens un de ces jours...

***Concernant votre fichier joint, comment faire pour voir les lignes et colonnes, je voudrais enlever les # en élargissant les colonnes...

Merci encore des conseils !!!

 

[ROGER2327]

Re : Dois-je faire une "Loop" en VBA ou une formule ???

Re…

Vous êtes trop fort Roger 2327 !!!! C'est en plein ce que je veux...

Comment avez-vous fait pour passer des données en colonne A et B et en aboutir avec les colonne C et D avec les fameuse valeur "0" au date manquante en colonne "A" Pour la colonne "C" c,est sûr que j'aurais pu écrire la première date en A1 et faire A1+1 et étirer jusquà la dernière voulu...Mon problème était d'obtenir les valeur correspondante en colonne "D", provenant de la col "B"

Pour vous c'est peut-être basic comme calcul ou code, mais pour moi qui ne connais rien encore au VBA c'est extraordinaire voir un miracle!!! Je vais faire des recherches et me tenir sur ce site afin de m'apprivoisé au jargon et au code. Je ne veux pas venir vous voir juste quand je suis dans le trouble...mais pour dépanner des gens un de ces jours...

***Concernant votre fichier joint, comment faire pour voir les lignes et colonnes, je voudrais enlever les # en élargissant les colonnes...

Merci encore des conseils !!!

Pour le remplissage des colonnes C et D j'ai écrit quelques lignes de code dans les modules Feuil1 et Module1.

Pour élargir les colonnes, le plus simple est de ré-afficher leurs en-têtes. Avec Excel2003, aller dans le menu Outils/Options…, onglet Affichage et cocher En-têtes de lignes et de colonnes.
Avec Excel2010, je ne sais pas.​

Bon courage.

ROGER2327
#5278

Vendredi 13 Merdre 138 (Sainte Lunette, solitaire - fête Suprême Quarte)
11 Prairial An CCXIX, 6,6390h - fraise
2011-W22-1T15:56:01Z

 

[Eltech]

Re : Dois-je faire une "Loop" en VBA ou une formule ???

Excusez-moi...dernière question, Est-ce le Code "TOTO2" ou "EXTRACTION1" qui donne les colonnes C et D ?

Merci encore...