Bonjour les programmeurs,

Pour trouver la derniere colonne vide, j'utilise, pour 2 onglets differents, la meme methode (Columns.Count ....)
Cependant le premier comptage (nb_columns1) j'arrive au resultat desire (55 colones comptees pour 55 dans mon tableau), par contre le second (nb_columns2) me donne la valeur "1" alors que mon tableau comporte 21 colonnes.

Quelqu'un a une idee et/ou solution pour fixer ce probleme?

NB: Cette operation se realise depuis un userform. Doublement verifier que sur ma 2nde feuille les cellules A1 a U1 ne sont pas vides.

Code : Sélectionner tout - Visualiser dans une fenêtre à part
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
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
Private Sub CdB_Ok_Click()
Dim nb_column1 As Long, nb_column2 As Long
    nb_column1 = Worksheets("Personnal List").Cells(1 & Columns.Count).End(xlToLeft).Column
    nb_column2 = Worksheets("Training Hours").Cells(1 & Columns.Count).End(xlToLeft).Column
 
    Application.ScreenUpdating = False
 
'Activate "Personnal List"
With Worksheets("Personnal List").Activate
    ActiveSheet.Unprotect
 
    'Unhide hidden column
        Columns("G:K").Select
        Selection.EntireColumn.Hidden = False
 
    'Copy of reference column
        Columns("H:J").Select
        Selection.Copy
 
    'Insert at the end of table a new column
        Columns(nb_column1 + 1).Select
        Selection.Insert Shift:=xlToRight
        Application.CutCopyMode = False
 
    'Replace "--Template--" by the tab's name
        Columns(nb_column1 + 1).Select
        Selection.Replace What:="--Template--2", Replacement:=TxtBox_Training.Value, LookAt:= _
            xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False
        Selection.Replace What:="--Template--", Replacement:=TxtBox_Training.Value, LookAt:= _
            xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False
 
        Columns(nb_column1 + 2).Select
        Selection.Replace What:="--Template--Training Year3", Replacement:=TxtBox_Training.Value, LookAt:= _
            xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False
        Selection.Replace What:="--Template--", Replacement:=TxtBox_Training.Value, LookAt:= _
            xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False
 
        Columns(nb_column1 + 3).Select
        Selection.Replace What:="--Template--Hours4", Replacement:=TxtBox_Training.Value, LookAt:= _
            xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False
        Selection.Replace What:="--Template--", Replacement:=TxtBox_Training.Value, LookAt:= _
            xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False
 
    'Hide reference column
        Columns("H:J").Select
        Selection.EntireColumn.Hidden = True
 
    'Protect sheet
        ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
            , AllowSorting:=True, AllowFiltering:= _
            True
 
End With
 
'Activate "Training Hours"
With Worksheets("Training Hours").Activate
 
    'Unhide hidden column
        Columns("E:G").Select
        Selection.EntireColumn.Hidden = False
 
    'Copy of reference column
        Columns("F:F").Select
        Selection.Copy
 
    'Insert at the end of table a new column
        Columns(nb_column2 + 1).Select
        Selection.Insert Shift:=xlToRight
        Application.CutCopyMode = False
 
    'Replace "--Template--" by the tab's name
        Selection.Replace What:="--Template--", Replacement:=TxtBox_Training.Value, LookAt:= _
            xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False
 
    'Hide reference column
        Columns("F:F").Select
        Selection.EntireColumn.Hidden = True
 
End With
 
'Close userform
    Unload Me
    Worksheets("Training List").Select
 
End Sub
Merci par avance pour votre temps et votre savoir.

Cordialement