Private Sub Cmd_genrap_Click()
Compteur_List5 = ListBox5.ListCount
For i_5 = 0 To Compteur_List5 - 1
valSelected_5 = ListBox5.List(i_5)
With ActiveSheet 'Agit sur la feuille active
On Error Resume Next
.Name = "S" & "-" & LblSem.Caption & "|" & valSelected_5
'On inscrit les valeurs dans toutes les cellules du tableau excel
Range("O8") = valSelected_5
Range("AZ8") = LblSem.Caption
Range("BQ8") = LblDu.Caption
Range("CO8") = LblAu.Caption
Range("Z11") = TBox_sec.Value
Range("AY33") = TextBox66.Value
Range("D11") = ComboBox2.Value
'On inscrit les heures du Lundi
Range("AL11") = Application.WorksheetFunction.Substitute(TextBox67, vbCrLf, Chr(10))
'On additionne les heures du Lundi
Range("AL18").Value = Application.WorksheetFunction.Sum(Range("AL11:AL17"))
'On inscrit les heures du Mardi
Range("AU11") = Application.WorksheetFunction.Substitute(TextBox68, vbCrLf, Chr(10))
'On additionne les heures du Mardi
Range("AU18").Value = Application.WorksheetFunction.Sum(Range("AU11:AU17"))
'On inscrit les heures du Mercredi
Range("BD11") = Application.WorksheetFunction.Substitute(TextBox69, vbCrLf, Chr(10))
'On additionne les heures du Mercredi
Range("BD18").Value = Application.WorksheetFunction.Sum(Range("BD11:BD17"))
'On inscrit les heures du Jeudi
Range("BM11") = Application.WorksheetFunction.Substitute(TextBox70, vbCrLf, Chr(10))
'On additionne les heures du Jeudi
Range("BM18").Value = Application.WorksheetFunction.Sum(Range("BM11:BM17"))
'On inscrit les heures du Vendredi
Range("BV11") = Application.WorksheetFunction.Substitute(TextBox71, vbCrLf, Chr(10))
'On additionne les heures du Vendredi
Range("BV18").Value = Application.WorksheetFunction.Sum(Range("BV11:BV17"))
'On inscrit les heures du Samedi
Range("CE11") = Application.WorksheetFunction.Substitute(TextBox72, vbCrLf, Chr(10))
'On additionne les heures du Samedi
Range("CE18").Value = Application.WorksheetFunction.Sum(Range("CE11:CE17"))
'On inscrit les heures du Dimanche
Range("CN11") = Application.WorksheetFunction.Substitute(TextBox73, vbCrLf, Chr(10))
'On additionne les heures du Dimanche
Range("CN18").Value = Application.WorksheetFunction.Sum(Range("CN11:CN17"))
'On additionne les totaux semaine
Range("CW11").Value = Application.WorksheetFunction.Sum(Range("AL11:CN11"))
Range("CW12").Value = Application.WorksheetFunction.Sum(Range("AL12:CN12"))
Range("CW13").Value = Application.WorksheetFunction.Sum(Range("AL13:CN13"))
Range("CW14").Value = Application.WorksheetFunction.Sum(Range("AL14:CN14"))
Range("CW15").Value = Application.WorksheetFunction.Sum(Range("AL15:CN15"))
Range("CW16").Value = Application.WorksheetFunction.Sum(Range("AL16:CN16"))
Range("CW17").Value = Application.WorksheetFunction.Sum(Range("AL17:CN17"))
Range("CW18").Value = Application.WorksheetFunction.Sum(Range("CW11:DF17"))
'On inscrit les heures de voyage Dep/Arr
Range("AL19") = Application.WorksheetFunction.Substitute(TextBox74, vbCrLf, Chr(10))
Range("AU19") = Application.WorksheetFunction.Substitute(TextBox75, vbCrLf, Chr(10))
Range("BD19") = Application.WorksheetFunction.Substitute(TextBox76, vbCrLf, Chr(10))
Range("BM19") = Application.WorksheetFunction.Substitute(TextBox77, vbCrLf, Chr(10))
Range("BV19") = Application.WorksheetFunction.Substitute(TextBox78, vbCrLf, Chr(10))
Range("CE19") = Application.WorksheetFunction.Substitute(TextBox79, vbCrLf, Chr(10))
Range("CN19") = Application.WorksheetFunction.Substitute(TextBox80, vbCrLf, Chr(10))
'On inscrit les heures en ZC
Range("AL20") = Application.WorksheetFunction.Substitute(TextBox81, vbCrLf, Chr(10))
Range("AU20") = Application.WorksheetFunction.Substitute(TextBox82, vbCrLf, Chr(10))
Range("BD20") = Application.WorksheetFunction.Substitute(TextBox83, vbCrLf, Chr(10))
Range("BM20") = Application.WorksheetFunction.Substitute(TextBox84, vbCrLf, Chr(10))
Range("BV20") = Application.WorksheetFunction.Substitute(TextBox85, vbCrLf, Chr(10))
Range("CE20") = Application.WorksheetFunction.Substitute(TextBox86, vbCrLf, Chr(10))
Range("CN20") = Application.WorksheetFunction.Substitute(TextBox87, vbCrLf, Chr(10))
Range("AL21") = Application.WorksheetFunction.Substitute(TextBox88, vbCrLf, Chr(10))
Range("AU21") = Application.WorksheetFunction.Substitute(TextBox89, vbCrLf, Chr(10))
Range("BD21") = Application.WorksheetFunction.Substitute(TextBox90, vbCrLf, Chr(10))
Range("BM21") = Application.WorksheetFunction.Substitute(TextBox91, vbCrLf, Chr(10))
Range("BV21") = Application.WorksheetFunction.Substitute(TextBox92, vbCrLf, Chr(10))
Range("CE21") = Application.WorksheetFunction.Substitute(TextBox93, vbCrLf, Chr(10))
Range("CN21") = Application.WorksheetFunction.Substitute(TextBox94, vbCrLf, Chr(10))
'On additionne les heures en ZC
Range("CW21").Value = Application.WorksheetFunction.Sum(Range("AL21:CN21"))
'On inscrit le nombre d'accès en ZC
Range("AL22") = Application.WorksheetFunction.Substitute(TextBox95, vbCrLf, Chr(10))
Range("AU22") = Application.WorksheetFunction.Substitute(TextBox96, vbCrLf, Chr(10))
Range("BD22") = Application.WorksheetFunction.Substitute(TextBox97, vbCrLf, Chr(10))
Range("BM22") = Application.WorksheetFunction.Substitute(TextBox98, vbCrLf, Chr(10))
Range("BV22") = Application.WorksheetFunction.Substitute(TextBox99, vbCrLf, Chr(10))
Range("CE22") = Application.WorksheetFunction.Substitute(TextBox100, vbCrLf, Chr(10))
Range("CN22") = Application.WorksheetFunction.Substitute(TextBox101, vbCrLf, Chr(10))
'On additionne le nombre d'accès en ZC
Range("CW22").Value = Application.WorksheetFunction.Sum(Range("AL22:CN22"))
'On inscrit les heures de travaux pénibles
Range("AL23") = Application.WorksheetFunction.Substitute(TextBox102, vbCrLf, Chr(10))
Range("AU23") = Application.WorksheetFunction.Substitute(TextBox103, vbCrLf, Chr(10))
Range("BD23") = Application.WorksheetFunction.Substitute(TextBox104, vbCrLf, Chr(10))
Range("BM23") = Application.WorksheetFunction.Substitute(TextBox105, vbCrLf, Chr(10))
Range("BV23") = Application.WorksheetFunction.Substitute(TextBox106, vbCrLf, Chr(10))
Range("CE23") = Application.WorksheetFunction.Substitute(TextBox107, vbCrLf, Chr(10))
Range("CN23") = Application.WorksheetFunction.Substitute(TextBox108, vbCrLf, Chr(10))
'On additionne les heures de travaux pénibles
Range("CW23").Value = Application.WorksheetFunction.Sum(Range("AL23:CN23"))
'On inscrit les primes de postes
Range("AL24") = Application.WorksheetFunction.Substitute(TextBox109, vbCrLf, Chr(10))
Range("AU24") = Application.WorksheetFunction.Substitute(TextBox110, vbCrLf, Chr(10))
Range("BD24") = Application.WorksheetFunction.Substitute(TextBox111, vbCrLf, Chr(10))
Range("BM24") = Application.WorksheetFunction.Substitute(TextBox112, vbCrLf, Chr(10))
Range("BV24") = Application.WorksheetFunction.Substitute(TextBox113, vbCrLf, Chr(10))
Range("CE24") = Application.WorksheetFunction.Substitute(TextBox114, vbCrLf, Chr(10))
Range("CN24") = Application.WorksheetFunction.Substitute(TextBox115, vbCrLf, Chr(10))
'On additopnne les primes de postes
Range("CW24").Value = Application.WorksheetFunction.Sum(Range("AL24:CN24"))
'On inscrit les heures de nuits
Range("AL25") = Application.WorksheetFunction.Substitute(TextBox116, vbCrLf, Chr(10))
Range("AU25") = Application.WorksheetFunction.Substitute(TextBox117, vbCrLf, Chr(10))
Range("BD25") = Application.WorksheetFunction.Substitute(TextBox118, vbCrLf, Chr(10))
Range("BM25") = Application.WorksheetFunction.Substitute(TextBox119, vbCrLf, Chr(10))
Range("BV25") = Application.WorksheetFunction.Substitute(TextBox120, vbCrLf, Chr(10))
Range("CE25") = Application.WorksheetFunction.Substitute(TextBox121, vbCrLf, Chr(10))
Range("CN25") = Application.WorksheetFunction.Substitute(TextBox122, vbCrLf, Chr(10))
'On additionne les heures de nuits
Range("CW25").Value = Application.WorksheetFunction.Sum(Range("AL25:CN25"))
'On inscrit les divers
Range("AL26") = ComboBox5.Value
Range("AU26") = ComboBox6.Value
Range("BD26") = ComboBox7.Value
Range("BM26") = ComboBox8.Value
Range("BV26") = ComboBox9.Value
Range("CE26") = ComboBox10.Value
Range("CN26") = ComboBox11.Value
CopieFeuille
Cache_Matrice
nbfeuille = ThisWorkbook.Sheets.Count
End With
Next i_5
Unload Me
End Sub
Range("BQ8") = LblDu.Caption Range("CO8") = LblAu.Caption
Range("BQ8") = Cdate(LblDu.Caption) Range("CO8") = Cdate(LblAu.Caption)
Application.WorksheetFunction.Substitute
replace
Me.LblDu = LundiSem(semaine, 2014)
Me.LblDu = Format(LblDu, "dd/mm/yyyy")
Me.LblDu = UCase(Left(Me.LblDu, 1)) & Right(Me.LblDu, Len(Me.LblDu) - 1)
Me.LblAu = FinSem(semaine, 2014)
Me.LblAu = Format(LblAu, "dd/mm/yyyy")
Me.LblAu = UCase(Left(Me.LblAu, 1)) & Right(Me.LblAu, Len(Me.LblAu) - 1)
Vous n’avez pas trouvé la réponse que vous recherchez ?
Posez votre questionqu'est ce qui te rend perplexe ? ces code sont encore pour moi assez compliqué à traduire, il m'ont été fournis sur ce forum...
annee=2014 Me.LblDu = Format(LundiSem(semaine, annee), "dd/mm/yyyy") Me.LblAu = Format(FinSem(semaine, annee), "dd/mm/yyyy")
Function LundiSem(semaine As Integer, Optional ANNEE) As Date
If IsMissing(ANNEE) Then ANNEE = Year(Date)
LundiSem = 7 * semaine + DateSerial(ANNEE, 1, 3) - _
Weekday(DateSerial(ANNEE, 1, 3)) - 5
End Function
Dim semaine As Integer
semaine = Application.InputBox(prompt:="Semaine ?", Type:=1)
MsgBox LundiSem(semaine,2014)