VBA : userform et remplissage de dates sur des plages différentes

dionys0s · 29 Octobre 2010

Rebonjour le forum

J'ai un userform tout prêt et initialisé dans mon fichier joint, (pour lequel j'avais reçu l'aide de XLD !) mais que j'ai du mal à adapter à cette trame :

J'ai mis le résultat attendu dans le fichier lorsqu'on appuie sur le bouton insérer du userform

En gros il faut que ça remplisse les plages B1:T1 des 10 premières dates du mois/année sélectionnées dans le userform, B33:T33 des 10 dates suivantes, et B65:V65 des 8 à 11 dernières dates du mois concerné.
Et enfin il faudrait que ça remplisse également la cellule V1 telle qu'elle est dans l'exemple.

D'avance merci pour votre aide

Gorfael · 29 Octobre 2010

Re : VBA : userform et remplissage de dates sur des plages différentes

Salut dionys0s et le forum
Il me semble plus simple de travailler par formule pour remplir les cellules dépendant de B1 :
D1 :

Code:

=B1+1

Il suffit de recopier D1 sur F1:T1, D33:T33, D65😛65
B33 :

Code:

=B1+10

à recopier sur B65
Reste les 3 dernières cellules :
R65 :

Code:

=SI(MOIS(P65+1)<>MOIS(P65);"";P65+1)

T65 :

Code:

=SI(MOIS(P65+2)<>MOIS(P65);"";P65+2)

V65 :

Code:

=SI(MOIS(P65+3)<>MOIS(P65);"";P65+3)

Pas la peine de faire une formule savante avec colonne(), c'est plus facile comme ça.

Avantage : On peut protéger ces cellules. La modification de B1 entraîne la modification de toutes les dates. On peut ajouter/supprimer des lignes sans avoir besoin de revoir la macro.
Inconvénient : J'en vois pas, mais je ne suis pas utilisateur du fichier 😀.
Et ça nous donne une super macro, pour le bouton :

Code:

Private Sub CommandButton1_Click()
[B1] = CDate("01/" & Me.ComboBox1 & "/" & Me.ComboBox2)
End Sub

N'étant pas utilisateur du fichier, quelques remarques :
Si je change le mois, les données des colonnes qui en dépendent devraient être remise à blanc, non ?
Quand tu crées un contrôle, le renommer directemment est plus facile que plus tard : ComboBox1 est moins parlant que CbxMois et CommandButton1 => Cmd_Insertion (par exemple) surtout si on a une multitude de contrôles.

dionys0s · 2 Novembre 2010

Re : VBA : userform et remplissage de dates sur des plages différentes

Bonjour le forum !

J'ai finallement réussi avec une macro. C'est une vraie usine à gaz mais du coup ça me remplit tout mon fichier intégralement, et surtout, ça marche, et c'est confortable.

Si certains ont le courage de lire mon code et de me faire toutes les remarques que vous jugerez utiles n'hésitez pas. Sinon good day to you all

Code:

Private Sub ComboBox1_Change()

End Sub
Private Sub ComboBox2_Change()

End Sub
Private Sub CommandButton1_Click()

Range("B1:W95").ClearContents

Range("B1") = Format(CDate("01/" & ComboBox1 & "/" & ComboBox2), "mm-yyyy")
 If ComboBox1.ListIndex = 11 Then
   fin = CDate("31/12/" & ComboBox2)
   Unload Me
 Else
   fin = CDate("01/" & ComboBox1.ListIndex + 2 & "/" & ComboBox2) - 1
   Unload Me
 End If
 
 Application.ScreenUpdating = False
  
 Range("D1").FormulaR1C1 = "=IF(MONTH(RC[-2]+1)=MONTH(RC[-2]),RC[-2]+1,"""")"
 Range("F1").FormulaR1C1 = "=IF(MONTH(RC[-2]+1)=MONTH(RC[-2]),RC[-2]+1,"""")"
 Range("H1").FormulaR1C1 = "=IF(MONTH(RC[-2]+1)=MONTH(RC[-2]),RC[-2]+1,"""")"
 Range("J1").FormulaR1C1 = "=IF(MONTH(RC[-2]+1)=MONTH(RC[-2]),RC[-2]+1,"""")"
 Range("L1").FormulaR1C1 = "=IF(MONTH(RC[-2]+1)=MONTH(RC[-2]),RC[-2]+1,"""")"
 Range("N1").FormulaR1C1 = "=IF(MONTH(RC[-2]+1)=MONTH(RC[-2]),RC[-2]+1,"""")"
 Range("P1").FormulaR1C1 = "=IF(MONTH(RC[-2]+1)=MONTH(RC[-2]),RC[-2]+1,"""")"
 Range("R1").FormulaR1C1 = "=IF(MONTH(RC[-2]+1)=MONTH(RC[-2]),RC[-2]+1,"""")"
 Range("T1").FormulaR1C1 = "=IF(MONTH(RC[-2]+1)=MONTH(RC[-2]),RC[-2]+1,"""")"
 Range("B33").FormulaR1C1 = "=IF(MONTH(R[-32]C[18]+1)=MONTH(R[-32]C[18]),R[-32]C[18]+1,"""")"
 Range("D33").FormulaR1C1 = "=IF(MONTH(RC[-2]+1)=MONTH(RC[-2]),RC[-2]+1,"""")"
 Range("F33").FormulaR1C1 = "=IF(MONTH(RC[-2]+1)=MONTH(RC[-2]),RC[-2]+1,"""")"
 Range("H33").FormulaR1C1 = "=IF(MONTH(RC[-2]+1)=MONTH(RC[-2]),RC[-2]+1,"""")"
 Range("J33").FormulaR1C1 = "=IF(MONTH(RC[-2]+1)=MONTH(RC[-2]),RC[-2]+1,"""")"
 Range("L33").FormulaR1C1 = "=IF(MONTH(RC[-2]+1)=MONTH(RC[-2]),RC[-2]+1,"""")"
 Range("N33").FormulaR1C1 = "=IF(MONTH(RC[-2]+1)=MONTH(RC[-2]),RC[-2]+1,"""")"
 Range("P33").FormulaR1C1 = "=IF(MONTH(RC[-2]+1)=MONTH(RC[-2]),RC[-2]+1,"""")"
 Range("R33").FormulaR1C1 = "=IF(MONTH(RC[-2]+1)=MONTH(RC[-2]),RC[-2]+1,"""")"
 Range("T33").FormulaR1C1 = "=IF(MONTH(RC[-2]+1)=MONTH(RC[-2]),RC[-2]+1,"""")"
 Range("B65").FormulaR1C1 = "=IF(MONTH(R[-32]C[18]+1)=MONTH(R[-32]C[18]),R[-32]C[18]+1,"""")"
 Range("D65").FormulaR1C1 = "=IF(MONTH(RC[-2]+1)=MONTH(RC[-2]),RC[-2]+1,"""")"
 Range("F65").FormulaR1C1 = "=IF(MONTH(RC[-2]+1)=MONTH(RC[-2]),RC[-2]+1,"""")"
 Range("H65").FormulaR1C1 = "=IF(MONTH(RC[-2]+1)=MONTH(RC[-2]),RC[-2]+1,"""")"
 Range("J65").FormulaR1C1 = "=IF(MONTH(RC[-2]+1)=MONTH(RC[-2]),RC[-2]+1,"""")"
 Range("L65").FormulaR1C1 = "=IF(MONTH(RC[-2]+1)=MONTH(RC[-2]),RC[-2]+1,"""")"
 Range("N65").FormulaR1C1 = "=IF(MONTH(RC[-2]+1)=MONTH(RC[-2]),RC[-2]+1,"""")"
 Range("P65").FormulaR1C1 = "=IF(MONTH(RC[-2]+1)=MONTH(RC[-2]),RC[-2]+1,"""")"
 Range("R65").FormulaR1C1 = "=IF(MONTH(RC[-2]+1)=MONTH(RC[-2]),RC[-2]+1,"""")"
 Range("T65").FormulaR1C1 = "=IF(MONTH(RC[-2]+1)=MONTH(RC[-2]),RC[-2]+1,"""")"
 Range("V65").FormulaR1C1 = "=IF(MONTH(RC[-2]+1)=MONTH(RC[-2]),RC[-2]+1,"""")"

Cells.Select

Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False

Feuil22.Select

Dim DerLign As Integer

DerLign = Range("A65536").End(xlUp).Row

Dim DerCol As Integer

DerCol = Range("IV1").End(xlToLeft).Column

Feuil27.Select

Range("B2").FormulaR1C1 = _
        "=IF(ISNA(IF(COUNTIF('Datas CdS'!R2C1:R" & DerLign & "C1,R1C[0])=0,"""",INDEX('Datas CdS'!R1,MATCH(R[0]C1,OFFSET('Datas CdS'!R1C1,MATCH(R1C[0],'Datas CdS'!R2C1:R" & DerLign & "C1),,,256),0)))),"""",IF(COUNTIF('Datas CdS'!R2C1:R" & DerLign & "C1,R1C[0])=0,"""",INDEX('Datas CdS'!R1,MATCH(R[0]C1,OFFSET('Datas CdS'!R1C1,MATCH(R1C[0],'Datas CdS'!R2C1:R" & DerLign & "C1),,,256),0))))"

Range("C2").FormulaR1C1 = _
        "=IF(SUMPRODUCT(('Datas CdS'!R1C2:R1C" & DerCol & "=R[0]C[-1])*('Datas CdS'!R1C2:R1C" & DerCol & "<>""Annonce"")*('Datas CdS'!R1C2:R1C" & DerCol & "<>""Entracte"")*('Datas CdS'!R4C2:R4C" & DerCol & "))=0,"""",SUMPRODUCT(('Datas CdS'!R1C2:R1C" & DerCol & "=R[0]C[-1])*('Datas CdS'!R1C2:R1C" & DerCol & "<>""Annonce"")*('Datas CdS'!R1C2:R1C" & DerCol & "<>""Entracte"")*('Datas CdS'!R4C2:R4C" & DerCol & ")))"

Range("B31").FormulaR1C1 = "=SUM(R[-29]C[1]:R[-1]C[1])&"" secondes *"""

Range("B34").FormulaR1C1 = _
        "=IF(ISNA(IF(COUNTIF('Datas CdS'!R2C1:R" & DerLign & "C1,R33C[0])=0,"""",INDEX('Datas CdS'!R1,MATCH(R[0]C1,OFFSET('Datas CdS'!R1C1,MATCH(R33C[0],'Datas CdS'!R2C1:R" & DerLign & "C1),,,256),0)))),"""",IF(COUNTIF('Datas CdS'!R2C1:R" & DerLign & "C1,R33C[0])=0,"""",INDEX('Datas CdS'!R1,MATCH(R[0]C1,OFFSET('Datas CdS'!R1C1,MATCH(R33C[0],'Datas CdS'!R2C1:R" & DerLign & "C1),,,256),0))))"

Range("C34").FormulaR1C1 = _
        "=IF(SUMPRODUCT(('Datas CdS'!R1C2:R1C" & DerCol & "=R[0]C[-1])*('Datas CdS'!R1C2:R1C" & DerCol & "<>""Annonce"")*('Datas CdS'!R1C2:R1C" & DerCol & "<>""Entracte"")*('Datas CdS'!R4C2:R4C" & DerCol & "))=0,"""",SUMPRODUCT(('Datas CdS'!R1C2:R1C" & DerCol & "=R[0]C[-1])*('Datas CdS'!R1C2:R1C" & DerCol & "<>""Annonce"")*('Datas CdS'!R1C2:R1C" & DerCol & "<>""Entracte"")*('Datas CdS'!R4C2:R4C" & DerCol & ")))"

Range("B63").FormulaR1C1 = "=SUM(R[-29]C[1]:R[-1]C[1])&"" secondes *"""

Range("B66").FormulaR1C1 = _
        "=IF(ISNA(IF(COUNTIF('Datas CdS'!R2C1:R" & DerLign & "C1,R65C[0])=0,"""",INDEX('Datas CdS'!R1,MATCH(R[0]C1,OFFSET('Datas CdS'!R1C1,MATCH(R65C[0],'Datas CdS'!R2C1:R" & DerLign & "C1),,,256),0)))),"""",IF(COUNTIF('Datas CdS'!R2C1:R" & DerLign & "C1,R65C[0])=0,"""",INDEX('Datas CdS'!R1,MATCH(R[0]C1,OFFSET('Datas CdS'!R1C1,MATCH(R65C[0],'Datas CdS'!R2C1:R" & DerLign & "C1),,,256),0))))"

Range("C66").FormulaR1C1 = _
        "=IF(SUMPRODUCT(('Datas CdS'!R1C2:R1C" & DerCol & "=R[0]C[-1])*('Datas CdS'!R1C2:R1C" & DerCol & "<>""Annonce"")*('Datas CdS'!R1C2:R1C" & DerCol & "<>""Entracte"")*('Datas CdS'!R4C2:R4C" & DerCol & "))=0,"""",SUMPRODUCT(('Datas CdS'!R1C2:R1C" & DerCol & "=R[0]C[-1])*('Datas CdS'!R1C2:R1C" & DerCol & "<>""Annonce"")*('Datas CdS'!R1C2:R1C" & DerCol & "<>""Entracte"")*('Datas CdS'!R4C2:R4C" & DerCol & ")))"

Range("B95").FormulaR1C1 = "=SUM(R[-29]C[1]:R[-1]C[1])&"" secondes *"""

    Range("B2:C2").Select
    Selection.Copy
    Range("B2:C30").Select
    ActiveSheet.Paste
    Range("B2:C31").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("D2").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("B2:C31").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("F2").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("B2:C31").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("H2").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("B2:C31").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("J2").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("B2:C31").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("L2").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("B2:C31").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("N2").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("B2:C31").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("P2").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("B2:C31").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("R2").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("B2:C31").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("T2").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("B2:C31").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

    Range("B34:C34").Select
    Selection.Copy
    Range("B35:C62").Select
    ActiveSheet.Paste
    Range("B34:C63").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("D34").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("B34:C63").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("F34").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("B34:C63").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("H34").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("B34:C63").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("J34").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("B34:C63").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("L34").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("B34:C63").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("N34").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("B34:C63").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("P34").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("B34:C63").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("R34").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("B34:C63").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("T34").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("B34:C63").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

    Range("B66:C66").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("B67:C94").Select
    ActiveSheet.Paste
    Range("B66:C95").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("D66").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("B66:C95").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("F66").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("B66:C95").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("H66").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("B66:C95").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("J66").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("B66:C95").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("L66").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("B66:C95").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("N66").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("B66:C95").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("P66").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("B66:C95").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("R66").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("B66:C95").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("T66").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("B66:C95").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("V66").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("B66:C95").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

    Application.CutCopyMode = False

    Range("V1:W64").FormulaR1C1 = "=RC[-20]"
    Range("V1:W64").Select

Application.ScreenUpdating = True

End Sub
Private Sub CommandButton2_Click()

Unload Me

End Sub
Private Sub Label1_Click()



End Sub
Private Sub UserForm_Initialize()

For n = 1 To 12
  ComboBox1.AddItem Format(CDate("01/" & n & "/" & Year(Date)), "mmmm")
Next n
For n = 2009 To 2015
  ComboBox2.AddItem n
Next n

End Sub

Effectuez une recherche sur Excel Downloads...

VBA : userform et remplissage de dates sur des plages différentes

dionys0s

XLDnaute Impliqué

Pièces jointes

Gorfael

XLDnaute Barbatruc

dionys0s

XLDnaute Impliqué

Discussions similaires

Nous accordons de l'importance à votre vie privée