Appliquer un code VBA à plusieurs colonnes

[webmuster]

Bonjour à toutes et à tous

Une fois de plus, je me permets de solliciter votre aide.

- Je tente de compléter ce code, en imposant le format "2 décimales" au résultat de calcul de la moyenne.
- Sur le même principe, j'ai besoin de faire des sommes hedomadaires, en colonnes C, D E et F, sachant que, comme précédemment et suivant les mois, les lignes "Dim" sont variables :

Sub hebdo()
For n = 1 To Range("A" & Rows.Count).End(xlUp).Row
  If Range("A" & n) = "dim" Then
    If n - 6 < 1 Then
      mm = 1
    Else
      mm = n - 6
    End If
    For m = n To mm Step -1
      If Range("G" & m) <> 0 And Range("G" & m) <> "" Then
        tot = tot + Range("G" & m)
        nb = nb + 1
      End If
    Next m
    If tot <> 0 And nb <> 0 Then Range("G" & n) = tot / nb
  End If
  tot = 0
  nb = 0
Next
End Sub

J'ai fait une tentative en déclarant une variable avec "NumCol", mais sans parvenir à l’intégrer au code existant.
Pouvez-vous m'éclairer ?

Vous trouverez, en pièce jointe, un petit fichier exemple.

Bien cordialement

 

[Staple1600]

Re : Appliquer un code VBA à plusieurs colonnes

Bonjour à tous

webmuster

Avant d'explorer ton code, et toujours dans un but didactique, as-tu une idée concernant le bug que je décrivais dans mon message précédent ?

Personnellement je "Dimerai" tot et nb dans un premier temps
Dim tot As 'je te laisse déviner ce qui va derrière le As
Dim nb As ' idem

PS: Tu peux aussi déclarer les autres variables, ça mange pas pain et c'est indolore.
Bravo pour tes talents de musicien

 

[Jack2]

Re : Appliquer un code VBA à plusieurs colonnes

Bonjour le Fil,

Pour webmuster, ci-joint le code qui place les moyennes sur toutes les colonnes, sauf en M, N et Q où il y a déjà tes formules. Si tu veux des explications, je posterais un webmuster_2.xls.

Toute critique ou amélioration du code est la bienvenue. J'avais pas fait attention au "baseline", je vais faire un saut.

A+ Jack2

 

[webmuster]

Re : Appliquer un code VBA à plusieurs colonnes

Bonjour Jack2, Stapple1600

En creusant, j'ai découvert que mon bug provenait vraisemblablement de la présence d'un "dim" dans la plage("B5:B10").
J'imagine que lorsqu'un dimanche est trouvé dans cette plage, une moyenne doit être calculée, mais en l'absence de 6 jours précédent, le code plante.
En tous cas, lorsque je supprime le "dim" dans la plage, je n'ai plus de bug.
J'ai contourné le problème avec une formule (pas très orthodoxe, je l'avoue) en ("C10") incrémentée jusqu'en ("C5") :

=SI(OU(B11="lun";C11="");"";C11-1)

Ayant une fâcheuse tendance à la ténacité, m’accrochant à mon code comme une mère à son enfant (ou une moule à son rocher), je vous soumets trois questions :
1- comment inscrire la formule de rechercheV du code suivant, "en dur" dans les cellules de la plage ("D5;D10") ?
2- comment tester qu'aucune ligne (de 33 à 41) de la feuille précédente n'est vide ?
3- comment appliquer ce code aux cellules ("D5:L10") et ("O510") pour récupérer les valeurs en ("D33:L41") et ("O3341") dans la feuille précédente (un "&" entre les deux, peut-être ?) ?

Sub sem()

Dim cel As Range

For Each cel In ActiveSheet.Range("D5:D10")
 
  cel.Value = Application.VLookup(CLng(cel.Offset(0, -1)), Worksheets(ActiveSheet.Index - 1).Range("C33:D41"), 2, False)

Next cel

End Sub

Merci pour ces échanges fructueux (un peu à sens unique, c'est vrai ).

Pour Stapple1600

Personnellement je "Dimerai" tot et nb dans un premier temps
Dim tot As 'je te laisse déviner ce qui va derrière le As
Dim nb As ' idem

Euh ... Integer ? (je n'ai trouvé le smiley qui rougit)

PS : Merci pour le compliment

Bien cordialement

 

[Staple1600]

Re : Appliquer un code VBA à plusieurs colonnes

Bonjour à tous


webmuster
J'ai ajouter ceci pour tester ton code (en me basant sur ta PJ)
et j'avoue ne pas comprendre.
Tu fais ton RECHERCHEV sur une plage de cellules vides
(voir tests dans ton code VBA)
(en tout cas c'est le cas sur ta PJ du message#9

Sub sem()
Dim cel As Range
'ajout pour test
With Worksheets(ActiveSheet.Index - 1)
    MsgBox .Name ' ok
    MsgBox .Range("C33:D41").Address 'ok
    MsgBox .Range("C33:D41").Count = Application.CountBlank(.Range("C33:D41"))
'-> La dernière MsgBox renvoie VRAI
 '-> donc ces cellules sont vides !
End With

For Each cel In ActiveSheet.Range("D5:D10")
cel.Value = Application.VLookup(CLng(cel.Offset(0, -1)), _
Worksheets(ActiveSheet.Index - 1).Range("C33:D41"), 2, False)
Next cel
End Sub

 

[webmuster]

Re : Appliquer un code VBA à plusieurs colonnes

Bonsoir Staple1600,

j'avoue ne pas comprendre.
Tu fais ton RECHERCHEV sur une plage de cellules vides

Eclaire-moi, s'il te plait.
Je pensais faire une rechercheV sur la plage ("C33: D41") de la feuille précédente.
Sauf erreur de ma part, je devrais récupérer en plage ("D6: D9") de la nouvelle feuille, les valeurs de la plage ("D37: D40"). Ce n'est pourtant pas le cas.
Je suis perdu !

Je te mets à disposition un fichier un peu plus à jour.

Concernant ma question n°2, et au fil de tes propositions, je me rends subitement compte que j'avais mal ciblé mon besoin.
En effet, mon but est de prévenir l'utilisateur que les données saisies sont incomplètes, et ce avant de rajouter un nouveau mois.
Je dois donc tester en colonne "M", s'il y a des cellules vides (mais contenant une formule), de la cellule ("M11") à la dernière cellule du mois (exemple: ligne du 28 février).
S'il n'y en a aucune, on ajoute l'onglet, s'il manque des données, un message box le signale (avec le nombre de cellules vides ?) et on sort de la macro.

J'ai tenter de rédiger un code avec "nb = Application.WorksheetFunction.CountBlank....", mais sans y parvenir.

Je suis sincèrement désolé du temps que je t'ai fait gaspiller sur cette demande.

Les questions n°1 et n°2 pourront te sembler assez simples, et pourtant, les réponses me font cruellement défaut.

Je continue mes recherches.

PS: je crois qu'après des heures passées sur ce fichier, une bonne nuit de sommeil sera réparatrice.

Bien cordialement

 

[Staple1600]

Re : Appliquer un code VBA à plusieurs colonnes

Re

Je suis sincèrement désolé du temps que je t'ai fait gaspiller sur cette demande.

Ne sois pas désolé.
Je suis ici de mon plein gré.
Et ce temps n'est pas gaspillé puisque c'est du temps libre

Pour revenir à ton RECHERCHEV, la plage de cellules [noparse]C3341[/noparse] dans ta pièce jointe était bien vide non ?
(Comme les MsgBox le montraient)

Je vais voir ce qui change dans ton nouveau fichier joint.

EDITION: Nouvelle PJ ouverte et toujours [noparse]C3341[/noparse]vides sur la feuille REFERENTIEL
(qui est bien la feuille appelée par Worksheets(ActiveSheet.Index - 1))

PS: Je ne vois pas le code que je j'ai ajouté à ton code VBA dans cette nouvelle PJ.
Est-ce à dire que tu ne l'as pas testé??

NB: Ceci n'existe pas dans VBA
Range("D5:L41,O5:O41") = clearcontent
mais cela oui
Range("D5:L41,O5:O41").ClearContents

 

[Jack2]

Re : Appliquer un code VBA à plusieurs colonnes

Bonjour le Fil,

Ca bosse le week-end ! Peut-être un moyen de tester s'il existe des cellules vides dans la colonne M avant d'ajouter un onglet :

Sub VerifRangeVides()
Dim Lig As Integer
Lig = 11
While Range("B" & Lig) <> ""
    If Range("M" & Lig) = 0 And Range("M" & 11 + Lig).HasFormula Then
        MsgBox "La cellule   M" & Lig & Chr(10) & " n'a pas de valeur ou est égale à 0. Macro arrêtée"
        End
    Else
        Lig = Lig + 1
    End If
Wend
End Sub

ou

Sub VerifCellsVides()
Dim Lig As Integer
Lig = 11
While Cells(Lig, 2) <> ""
    If Cells(Lig, 13) = 0 And Cells(Lig, 13).HasFormula Then
        MsgBox "La cellule   M" & Lig & Chr(10) & " n'a pas de valeur ou est égale à 0. Macro arrêtée"
        End
    Else
        Lig = Lig + 1
    End If
Wend
End Sub

Le while sert à tester s'il y a une valeur en B pour s'arrêter en fin de mois.

A+ Jack2

 

[webmuster]

Re : Appliquer un code VBA à plusieurs colonnes

Bonsoir Staple1600, Jack2

Staple1600,

NB: Ceci n'existe pas dans VBA
Range("D5:L41,O5:O41") = clearcontent
mais cela oui
Range("D5:L41,O5:O41").ClearContents

Je crois que ce weekend, j'avais dépassé la limite autorisée (on devrait imposer un "excelotest" sur ce forum).

J'ai testé ton code:
- la dernière MsgBox renvoie FAUX
- puis le code s'arrête sur

cel.Value = Application.VLookup(CLng(cel.Offset(0, -1)), _
Worksheets(ActiveSheet.Index - 1).Range("C33:D41"), 2, False)

EDITION: Nouvelle PJ ouverte et toujours C33: D41vides sur la feuille REFERENTIEL
(qui est bien la feuille appelée par Worksheets(ActiveSheet.Index - 1))

Je pensais que, sem() étant appelée après la création de la nouvelle feuille, celle-ci devenait la feuille active et que la recherchev se faisait sur l'onglet "janvier".
Dans tous les cas, à présent, soit je n'ai aucunes valeurs qui s'inscrivent en D5: D10, soit le code s'arrête sur :

cel.Value = Application.VLookup(CLng(cel.Offset(0, -1)), Worksheets(ActiveSheet.Index - 1).Range("C33: D41"), 2, False)

Je crains d'avoir commis quelques bévues en deux jours. Comme quoi, parfois, le mieux est l'ennemi du bien.
Je suis en train de perdre tous mes repères.

Jack2,
J'ai testé tes deux codes sur la feuille("janvier"), et ils identifient bien la 1ère cellule vide dans la colonne M.
Malheureusement, il s'agit d'un jour férié.
Je pousse plus loin (voire plus profond) mes investigations.

Mille fois merci pour votre aide.
Vous êtes la générosité incarnée (informatisée?).

Bien cordialement

 

[Jack2]

Re : Appliquer un code VBA à plusieurs colonnes

Bonjour à tous,

webmuster Il est possible, lors de la création d'un mois d'identifier un jour férié qui peut ensuite être reconnu soit par une couleur de cellule différente, soit par un formatage différent de celle-ci, ou autre. Cela permettrait d'éviter de la prendre en compte lors de la vérification

prévenir l'utilisateur que les données saisies sont incomplètes, et ce avant de rajouter un nouveau mois.

.
Si tu n'es pas saturé Staple1600 et/ou moi pouvons t'aider.

A+ Jack2

 

[webmuster]

Re : Appliquer un code VBA à plusieurs colonnes

Bonjour Jack2, Staple1600

Je crois que ce weekend, j'avais dépassé la limite autorisée (on devrait imposer un "excelotest" sur ce forum).

J'ai réalisé que cette phrase pouvait prêter à confusion. Je parlais bien d'une forte dose d'Excel.

Ce point étant précisé , c'est avec plaisir et gratitude que j'accepterai votre aide. Même si, et Jack2 l'a visiblement bien ressenti, j'ai traversé un moment de découragement hier soir.
Ce matin, je me suis levé fiévreux et enrhumé. Espérons que cet état fébrile n'altérera en rien mes capacités à saisir la finesse de vos conseils.

Sachant compter sur votre indulgence.

Bien cordialement

 

[Staple1600]

Re : Appliquer un code VBA à plusieurs colonnes

Bonsoir à tous

Ma façon de faire pour tester le vide

Sub TesteSiVide()
Dim dl&, c As Range, t
dl = Cells(Rows.Count, "M").End(xlUp).Row
For Each c In Range("M11").Resize(dl - 10)
If Len(c) = 0 Or c = "0" Then
t = t + 1
End If
Next c
MsgBox "Saisie " & IIf(t > 0, "in", vbNullString) & "valide"
End Sub

Si tu n'es pas saturé Staple1600 et/ou moi pouvons t'aider.

Nous ne sommes pas que deux sur le forum.
J'espère que d'autres membres du forum entreront dans la danse

 

[Jack2]

Re : Appliquer un code VBA à plusieurs colonnes

Bonjour tout le monde,

Procédure et fonction pour tester si une cellule est vide et si elle correspond à un jour férié ou à un dimanche
Fonctions glanées sur le site et sur le Web:

Public Function Paques(An As Integer) As Long
Paques = DateSerial(An, 3, 23) + ((2 * (An Mod 4) + (4 * (An Mod 7) + _
          (6 * (((19 * (An Mod 19)) + 24) Mod 30) + 5))) Mod 7) + ((19 * (An Mod 19) + 24) Mod 30) - 1
End Function

Function EstFerie(D As Date) As Boolean
'Vrai si la valeur courante correspond a un jour férié
Application.Volatile
Dim An As Integer, i As Long, Fin_Moin_Un As Date, SpecD, b As Long
    An = Year(D)
    b = Abs((An Mod 4 = 0 And An Mod 100 <> 0) Or (An Mod 400 = 0))
    Fin_Moin_Un = DateSerial(An, 1, 1) - 1
    i = Paques(An) - Fin_Moin_Un - b
    'Pour la France (1 Mai = 121em jour d'une année NON-bissextile)
    SpecD = Array(1 - b, 121, 128, 195, 227, 305, 315, 359, i + 1, i + 39, i + 50)
    An = D - Fin_Moin_Un - b
    For i = 0 To UBound(SpecD)
        EstFerie = An = SpecD(i)
        If EstFerie Then Exit For
    Next
End Function

Procédure :

Sub VerifRangeVides()
Dim Lig As Integer
Dim DateSt As String
Lig = 11
While Range("B" & Lig) <> ""
    If Range("M" & Lig) = 0 And Range("M" & Lig).HasFormula Then
        DateSt = Left(Range("C" & Lig), 2) & CStr(Mid(Range("A1"), 3))
        If EstFerie(CDate(DateSt)) Or LCase(CStr(Range("B" & Lig))) = "dim" Then GoTo Suite
        MsgBox "La cellule   M" & Lig & Chr(10) & " n'a pas de valeur ou est égale à 0. Macro arrêtée"
        End
    Else
Suite:
        Lig = Lig + 1
    End If
Wend
End Sub

à placer après Onglet = ActiveSheet.Name

Je n'avais retenu le comptage des lignes (dl pour Staple1600) car j'obtenais toujours dl = 41 (toutes les lignes ayant des formules ?). Je vois ça chez moi.

Nous ne sommes pas que deux sur le forum

C'est sûr j'ai noté les deux Identifiants qui suivaient le fil .
PS Si quelqu'un pouvait me dire où trouver l'explication de la notation dl&, St$ et autres. Merci.

EDIT Merci pour le lien J'aivais pas trouvé avec F1 (touche que je recommande)

A+ Jack2

 

[Staple1600]

Re : Appliquer un code VBA à plusieurs colonnes

Bonjour à tous

C'est sûr j'ai noté les deux Identifiants qui suivaient le fil .
PS Si quelqu'un pouvait me dire où trouver l'explication de la notation dl&, St$ et autres. Merci.
A+ Jack2

Jack2:
1) ALT+F11 puis F1 (premier réflexe à prendre quand VBA te pose question )
2) ou voir ici ce qui se dit à ce sujet

 

[webmuster]

Re : Appliquer un code VBA à plusieurs colonnes

Bonsoir Jack2, Staple1600

Dix jours de virus ont eu raison de mon assiduité.
Je refais doucement surface et essaie de reprendre timidement le fil (que j'ai quand même un peu perdu).
Merci pour vos suggestions.
Je vais tester vos solutions et reviendrai vers vous.

Cordialement