problème de select dans code

Version imprimable

bonjour a vous tous

j'ai récupéré un fichier sur le net qui est plein de lignes remplie de .select qui me pose a quelques endroits des soucis de suppression donc dans le bloc de code suivant
Code:

1 2 3 4 5 6 7 8 9 10 11 Sheets("Feuil2").Select Range(AdrDebut).Offset(0, 0).Select Selection = "Reference" With Selection .ColumnWidth = 11 .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter With .Font .Size = 10 End With End With
j'arrive a modifier la première ligne par
Code:

1 2 With Sheets("Feuil2") .Range(AdrDebut).Offset(0, 0)= "Reference"
mais pour l'autre sélection je ne sais pas par quoi la remplacer

Merci de votre aide, car il y a plein de lignes de codes comme celle la

Pascal

Salut,

essaye ça:

Code:

1
2
3
4
5
6
7
8
9
10
11
12
Sheets("Feuil2").Activate
Range(AdrDebut).Offset(0, 0).Select
 
With Selection
    .Value = "Reference"
    .ColumnWidth = 11
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlCenter
    With .Font
        .Size = 10
    End With
End With

bonjour et comme çà :

Code:

1
2
3
4
5
6
7
8
With Sheets("Feuil2").Range(AdrDebut)
.Value = "Reference"
.ColumnWidth = 11
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.Font.Size = 10
 
End With

pour moi offset(0,0) sert à rien

25/04/2013, 17h41
amrod93

en effet la version de rvtoulon est bien meilleure!
25/04/2013, 18h03
rvtoulon

Merci amrod.

bonjour a vous

merci de vos réponses mais l'offset que j'ai donné est erroné et est peut etre utile, je vous joints tout le code pour vous en rendre compte et vous verrez s'il peux etre améliorer

Code:

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
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
Sub Gabarit_Bibli(NbBibli, Devise)
Dim AdrDebut$, Na%, i%
Dim Toto$, Classeur$
Dim CX As Variant
 
Application.ScreenUpdating = False
Na = 30000 'Capacité de la Bibli (ici 1000 articles, max=30000)
Classeur = Workbooks(1).Name
Workbooks(Classeur).Worksheets("Feuil2").Activate
 
'---------------------- Adressage pour 28 bibli ----------------------------------
AdrDebut = Range("A1").Address
CX = Array("", "A1", "J1", "S1", "AB1", "AK1", "AT1", "BC1", _
            "BL1", "BU1", "CD1", "CM1", "CV1", "DE1", "DN1", _
            "DW1", "EF1", "EO1", "EX1", "FG1", "FP1", "FY1", _
            "GH1", "GQ1", "GZ1", "HI1", "HR1", "IA1", "IJ1")
 
For i = 1 To NbBibli
'--------------- Adressage des Biblis --------------
    AdrDebut = Range(CX(i)).Address
    Range(AdrDebut).Offset(0, 1).Select
'----------------- Titre --------------------------
    With Selection
        .ColumnWidth = 56.43
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        With Selection.Font
            .Size = 10
            .Bold = True
        End With
    End With
'----------------- Référence -------------------
    Range(AdrDebut).Offset(1, 0).Select
    Selection = "Reference"
    With Selection
        .ColumnWidth = 11
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        With .Font
            .Size = 10
        End With
    End With
'********* Colonne Réference **************
    Range(AdrDebut).Offset(2, 0).Select
    ActiveCell.Range(Cells(1, 1), Cells(Na, 1)).Select
    With Selection
        .HorizontalAlignment = xlCenter
    End With
'----------------- Désignation -------------------
    Range(AdrDebut).Offset(1, 1).Select
    Selection = "Désignation"
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        With .Font
            .Size = 10
        End With
    End With
'********** Colonne Désignation *************
    Range(AdrDebut).Offset(2, 1).Select
    ActiveCell.Range(Cells(1, 1), Cells(Na, 1)).Select
    With Selection
        .HorizontalAlignment = xlLeft
    End With
'------------------ Unité ------------------------
    Range(AdrDebut).Offset(1, 2).Select
    Selection = "Unité"
    With Selection
        .ColumnWidth = 5
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
    End With
'********** Colonne Unité ***************
    Range(AdrDebut).Offset(2, 2).Select
    ActiveCell.Range(Cells(1, 1), Cells(Na, 1)).Select
    With Selection
        .HorizontalAlignment = xlCenter
    End With
'------------------ Prix Unitaire HTVA (devise) ------------
    Range(AdrDebut).Offset(1, 3).Select
    Selection = "Prix Unit. (" & Devise & ")"
    With Selection
        .ColumnWidth = 9.29
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        With .Font
            .Size = 8
        End With
    End With
'************ Colonne Prix Unitaire ************
    Range(AdrDebut).Offset(2, 3).Select
    ActiveCell.Range(Cells(1, 1), Cells(Na, 1)).Select
    With Selection
        .HorizontalAlignment = xlRight
    End With
'-----------------------------------------------
'---------------- Libre 1 ----------------------
    Range(AdrDebut).Offset(1, 4).Select
    Selection = " Stock "
    With Selection
        .ColumnWidth = 5
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        With .Font
            .Size = 8
        End With
    End With
'<<<<<<<<<< Colonne Libre 1 >>>>>>>>>>>>>>>>>
    Range(AdrDebut).Offset(2, 4).Select
    ActiveCell.Range(Cells(1, 1), Cells(Na, 1)).Select
    With Selection
        .HorizontalAlignment = xlRight
    End With
'-----------------------------------------------
'---------------- Libre 2 ----------------------
    Range(AdrDebut).Offset(1, 5).Select
    Selection = "Seuil"
    With Selection
        .ColumnWidth = 5
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        With .Font
            .Size = 8
        End With
    End With
'<<<<<<<<<< Colonne Libre 2 >>>>>>>>>>>>>>>>>
    Range(AdrDebut).Offset(2, 5).Select
    ActiveCell.Range(Cells(1, 1), Cells(Na, 1)).Select
    With Selection
        .HorizontalAlignment = xlRight
    End With
'-----------------------------------------------
'---------------- Libre 3 ----------------------
    Range(AdrDebut).Offset(1, 6).Select
    Selection = "Prix en Stock"
    With Selection
        .ColumnWidth = 11
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        With .Font
            .Size = 8
        End With
    End With
'<<<<<<<<<< Colonne Libre 3 >>>>>>>>>>>>>>>>>
    Range(AdrDebut).Offset(2, 6).Select
    ActiveCell.Range(Cells(1, 1), Cells(Na, 1)).Select
    With Selection
        .HorizontalAlignment = xlRight
    End With
'--------------------------------------------
Application.ScreenUpdating = True
End Sub

Pascal

bonjour,
voici une amélioration d'une partie du code, je te laisse faire la suite:

Code:

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
'---------------------- Adressage pour 28 bibli ----------------------------------
CX = Array("", "A1", "J1", "S1", "AB1", "AK1", "AT1", "BC1", _
            "BL1", "BU1", "CD1", "CM1", "CV1", "DE1", "DN1", _
            "DW1", "EF1", "EO1", "EX1", "FG1", "FP1", "FY1", _
            "GH1", "GQ1", "GZ1", "HI1", "HR1", "IA1", "IJ1")
 
For i = 1 To NbBibli
'--------------- Adressage des Biblis --------------
    AdrDebut = Range(CX(i)).Address
'----------------- Titre --------------------------
   With Range(AdrDebut).Offset(0, 1)
        .ColumnWidth = 56.43
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        With .Font
            .Size = 10
            .Bold = True
        End With
    End With
'----------------- Référence -------------------
   With Range(AdrDebut).Offset(1, 0)
    .Value = "Reference"
        .ColumnWidth = 11
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        With .Font
            .Size = 10
        End With
    End With
'********* Colonne Réference **************
    With Range(AdrDebut).Offset(2, 0).Range(Cells(1, 1), Cells(Na, 1))
       .HorizontalAlignment = xlCenter
    End With
'----------------- Désignation -------------------
    With Range(AdrDebut).Offset(1, 1)
        .Value = "Désignation"
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        With .Font
            .Size = 10
        End With
    End With
 
'etc......

26/04/2013, 12h28
grisan29

bonjour RVTOULON:ccool:

merci de ta solution,:ccool: moi qui pensait qu'il fallait ôter tous les with pour le faire:oops:
merci beaucoup de ton savoir:ccool:

ceci clos le post:ccool:

Pascal