1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44
| 'initialisation des dictionnaires
Sub DicoSuivis(Optional ColB As Boolean, Optional DataB As Boolean)
'Variables
Dim MyRange As Range, AllRange As Range
Dim NBL As Long, MyDataR As DataR
With ThisWorkbook.Worksheets("Suivis")
NBL = .Range("Suivis_NbReport").Value
If ColB Then
'test initialisation dico
If MyColsuivis.Count > 0 Then MyColsuivis.RemoveAll
'creation du dictionnaire
Set AllRange = .Range(.Range("First"), .Range("First").End(xlToRight))
For Each MyRange In AllRange
If Not MyColsuivis.Exists(MyRange.Value) Then
MyColsuivis.Add MyRange.Value, MyRange.Column
End If
Next MyRange
End If
If NBL > 0 And DataB Then
If MyDataSuivis.Count > 0 Then MyDataSuivis.RemoveAll
Set AllRange = .Range(.Range("Start").Offset(1), .Range("Start").Offset(NBL))
For Each MyRange In AllRange
If Not MyDataSuivis.Exists(MyRange.Value) Then
Set MyDataR = New DataR
MyDataR.Status = .Cells(MyRange.Row, MyColsuivis("Status")).Value
MyDataR.Actif = .Cells(MyRange.Row, MyColsuivis("Choice")).Value
MyDataR.Comment = .Cells(MyRange.Row, MyColsuivis("Comment")).Value
If .Cells(MyRange.Row, MyColsuivis("Last mailing")).Value = "" Then
MyDataR.LastMail = Now()
Else
MyDataR.LastMail = .Cells(MyRange.Row, MyColsuivis("Last mailing")).Value
End If
MyDataSuivis.Add MyRange.Value, MyDataR
End If
Next MyRange
End If
End With
End Sub |
Partager