Remplir cellules automatiquement

Version imprimable

27/07/2014, 17h59
rungis

Remplir cellules automatiquement

Bonjour,

J'aimerais savoir comment faire pour remplir toutes mes cellules automatiquement suivant le nb de caractères le plus élevé par colonne.
Ceci afin de "standardiser" mon fichier et pouvoir le transformer en fichier txt sans décalage.

Exemple:

Ma colonne G contient les données suivantes:
"AUTO" en G1
"BORSOI S." en G2

Je cherche à compter le nb de caractères maximum. Dans cet exemple c'est 10, il faudrait donc remplir la cellule G1 par AUTO + 6 espaces derrière.

Savez-vous comment je dois m'y prendre ?

J'ai penser à passer par la formule nbcar puis max pour connaitre le nb de caractères le plus élevé par colonne. Ensuite créer une formule du type :

Code:

=SI(H1<I1;CONCATENER(G1;" ");)

H1 contenant la formule =NBCAR(G1) et I1 contenant la formule =MAX(H1:H65536)

Mais le nb d'espaces après le CONCATENER est variable donc cette formule n'est pas correcte.
Et procéder ainsi sur toutes mes colonnes est assez fastidieux puisque je serais obliger de créer 4 colonnes supplémentaires contant les formules pour chaque colonne de données.

Merci d'avance de votre aide
27/07/2014, 18h17
SfJ5Rpw8

Bonjour une formule matricielle , à placer dans la colonne H

Code:

{=GAUCHE(g1&"-------------------------------------------------------------------------------";MAX(NBCAR(g:g)))}

les { } ne sont pas à saisir tu les obtiens en validant ta formule en actionnant les touches CTRL + SHIFT + ENTREE

;)
27/07/2014, 18h40
rungis

Super merci beaucoup

Bonjour,

La macro ressemble donc à ça:

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
Sub Mapping_txt()
 
    Rows("1:1").Select
    Selection.Delete Shift:=xlUp
    Columns("B:B").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("B1").Select
    Selection.FormulaArray = _
        "=LEFT(RC[-1]&""                                                                                                                                            "",MAX(LEN(C[-1])))"
    Selection.AutoFill Destination:=Range("B1:B3000")
    Range("B1:B3000").Select
    Selection.Copy
    Range("C1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Columns("E:E").Select
    Application.CutCopyMode = False
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("E1").Select
    Selection.FormulaArray = _
        "=LEFT(RC[-1]&""                                                                                                                                            "",MAX(LEN(C[-1])))"
    Selection.AutoFill Destination:=Range("E1:E3000")
    Range("E1:E3000").Select
    Selection.Copy
    Range("F1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Columns("H:H").Select
    Application.CutCopyMode = False
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("H1").Select
    Selection.FormulaArray = _
        "=LEFT(RC[-1]&""                                                                                                                                            "",MAX(LEN(C[-1])))"
    Selection.AutoFill Destination:=Range("H1:H3000")
    Range("H1:H3000").Select
    Selection.Copy
    Range("I1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Columns("K:K").Select
    Application.CutCopyMode = False
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("K1").Select
    Selection.FormulaArray = _
        "=LEFT(RC[-1]&""                                                                                                                                            "",MAX(LEN(C[-1])))"
    Selection.AutoFill Destination:=Range("K1:K3000")
    Range("K1:K3000").Select
    Selection.Copy
    Range("L1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Columns("P:P").Select
    Application.CutCopyMode = False
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("P1").Select
    Selection.FormulaArray = _
        "=LEFT(RC[-1]&""                                                                                                                                            "",MAX(LEN(C[-1])))"
    Selection.AutoFill Destination:=Range("P1:P3000")
    Range("P1:P3000").Select
    Selection.Copy
    Range("Q1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("Q1").Select
    Application.CutCopyMode = False
    Columns("U:U").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("U1").Select
    Selection.FormulaArray = _
        "=LEFT(RC[-1]&""                                                                                                                                            "",MAX(LEN(C[-1])))"
    Selection.AutoFill Destination:=Range("U1:U3000")
    Range("U1:U3000").Select
    Selection.Copy
    Range("V1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("X8").Select
    Columns("X:X").Select
    Application.CutCopyMode = False
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("X1").Select
    Selection.FormulaArray = _
        "=LEFT(RC[-1]&""                                                                                                                                            "",MAX(LEN(C[-1])))"
    Selection.AutoFill Destination:=Range("X1:X3000")
    Range("X1:X3000").Select
    Selection.Copy
    Range("Y1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Columns("AA:AA").Select
    Application.CutCopyMode = False
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("AA1").Select
    Selection.FormulaArray = _
        "=LEFT(RC[-1]&""                                                                                                                                            "",MAX(LEN(C[-1])))"
    Selection.AutoFill Destination:=Range("AA1:AA3000")
    Range("AA1:AA3000").Select
    Selection.Copy
    Range("AB1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Columns("AE:AE").Select
    Application.CutCopyMode = False
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("AE1").Select
    Selection.FormulaArray = _
        "=LEFT(RC[-1]&""                                                                                                                                            "",MAX(LEN(C[-1])))"
    Selection.AutoFill Destination:=Range("AE1:AE3000")
    Range("AE1:AE3000").Select
    Selection.Copy
    Range("AF1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("AE1").Select
    Application.CutCopyMode = False
    Range("AJ1").Select
    Selection.FormulaArray = _
        "=LEFT(RC[-1]&""                                                                                                                                            "",MAX(LEN(C[-1])))"
    Selection.AutoFill Destination:=Range("AJ1:AJ3000")
    Range("AJ1:AJ3000").Select
    Selection.Copy
    Range("AK1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("AK1").Select
    Range("A:B,D:E,G:H,J:K").Select
    Range("J1").Activate
    Range("A:B,D:E,G:H,J:K,O:P").Select
    Range("O1").Activate
    Range("A:B,D:E,G:H,J:K,O:P,T:U").Select
    Range("T1").Activate
    Range("A:B,D:E,G:H,J:K,O:P,T:U,W:X,Z:AA,AD:AE").Select
    Range("AD1").Activate
    Range("A:B,D:E,G:H,J:K,O:P,T:U,W:X,Z:AA,AD:AE,AI:AJ").Select
    Range("AI1").Activate
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlToLeft
    ChDir "C:\OYAK\MONFALCONE"
    ActiveWorkbook.SaveAs Filename:="C:\OYAK\MONFALCONE\MONFALCONE.txt", _
        FileFormat:=xlUnicodeText, CreateBackup:=False
End Sub

Elle fonctionne correctement mais le seul problème est son temps d'execution qui est de minimum 1min40 sur ma machine (qui possède un quadcore de 2.3 Ghz).
Et j'ai bien peur que sur les pc des utilisateurs (qui n'ont pas le même proc) cela prenne 2x plus de temps.
Est-il possible d'optimiser ce traitement ?
Comment procéder ?

Merci d'avance de votre réponse.

28/07/2014, 19h45
SfJ5Rpw8

bonsoir,

eu ... tu n'avais pas parlé de macro...

tu peu dans un premier temps pour accélérer ta macro passer le calcul en manuel....

mais redis nous ce que tu veux faire..... tu n'as surement pas besoin de formules du coup...
28/07/2014, 19h51
Marc-L

Bonsoir,

en dehors de désactiver la mise à jour de l'écran (ScreenUpdating),
il faudrait revoir le code car plus il y a de Select - inutiles à 99% ! - plus le code est ralenti …

Effectivement, ici c'est pour les formules de calcul, il y a le forum dédié Macros et VBA Excel pour du code !
28/07/2014, 21h10
rungis

Bonsoir Messieurs,

J'ai placé ma question dans le bon topic mais apparemment elle a été déplacé.
Passer le calcul en manuel ?
Mon besoin est de pouvoir transformer le fichier excel ~~en pj~~ en fichier txt sans décalage.

Je pense qu'ici les select sont nécessaires car je suppose que l'insert se base sur ce select.
Cependant je vais désactiver le ScreenUpdating tu as raison.
28/07/2014, 22h53
Marc-L

L'insert s'effectue vis à vis de l'objet parent, donc Select totalement inutile car
pas besoin de sélectionner un objet - cellule - pour y accéder ! Exemple ici …

1 pièce(s) jointe(s)

Bonjour,

J'ai fait les modifs que tu m'as recommandé, ceci a donc considérablement allégé le script:

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
Sub Mapping_txt_v2()
 
    Application.ScreenUpdating = False
 
    Rows("1:1").Delete Shift:=xlUp
    Columns("B:B").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Columns("B:B").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("B1").FormulaArray = _
        "=LEFT(RC[-1]&""                                                                                                                                            "",MAX(LEN(C[-1])))"
    Range("B1").AutoFill Destination:=Range("B1:B3000")
    Range("B1:B3000").Copy
    Range("C1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Columns("E:E").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Columns("E:E").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("E1").FormulaArray = _
        "=LEFT(RC[-1]&""                                                                                                                                            "",MAX(LEN(C[-1])))"
    Range("E1").AutoFill Destination:=Range("E1:E3000")
    Range("E1:E3000").Copy
    Range("F1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Columns("H:H").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Columns("H:H").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("H1").FormulaArray = _
        "=LEFT(RC[-1]&""                                                                                                                                            "",MAX(LEN(C[-1])))"
    Range("H1").AutoFill Destination:=Range("H1:H3000")
    Range("H1:H3000").Copy
    Range("I1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Columns("K:K").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Columns("K:K").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("K1").FormulaArray = _
        "=LEFT(RC[-1]&""                                                                                                                                            "",MAX(LEN(C[-1])))"
    Range("K1").AutoFill Destination:=Range("K1:K3000")
    Range("K1:K3000").Copy
    Range("L1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Columns("P:P").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Columns("P:P").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("P1").FormulaArray = _
        "=LEFT(RC[-1]&""                                                                                                                                            "",MAX(LEN(C[-1])))"
    Range("P1").AutoFill Destination:=Range("P1:P3000")
    Range("P1:P3000").Copy
    Range("Q1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Columns("U:U").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Columns("U:U").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("U1").FormulaArray = _
        "=LEFT(RC[-1]&""                                                                                                                                            "",MAX(LEN(C[-1])))"
    Range("U1").AutoFill Destination:=Range("U1:U3000")
    Range("U1:U3000").Copy
    Range("V1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Columns("X:X").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Columns("X:X").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("X1").FormulaArray = _
        "=LEFT(RC[-1]&""                                                                                                                                            "",MAX(LEN(C[-1])))"
    Range("X1").AutoFill Destination:=Range("X1:X3000")
    Range("X1:X3000").Copy
    Range("Y1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Columns("AA:AA").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Columns("AA:AA").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("AA1").FormulaArray = _
        "=LEFT(RC[-1]&""                                                                                                                                            "",MAX(LEN(C[-1])))"
    Range("AA1").AutoFill Destination:=Range("AA1:AA3000")
    Range("AA1:AA3000").Copy
    Range("AB1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Columns("AE:AE").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Columns("AE:AE").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("AE1").FormulaArray = _
        "=LEFT(RC[-1]&""                                                                                                                                            "",MAX(LEN(C[-1])))"
    Range("AE1").AutoFill Destination:=Range("AE1:AE3000")
    Range("AE1:AE3000").Copy
    Range("AF1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Range("AJ1").FormulaArray = _
        "=LEFT(RC[-1]&""                                                                                                                                            "",MAX(LEN(C[-1])))"
    Range("AJ1").AutoFill Destination:=Range("AJ1:AJ3000")
    Range("AJ1:AJ3000").Copy
    Range("AK1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Range("A:B,D:E,G:H,J:K,O:P,T:U,W:X,Z:AA,AD:AE,AI:AJ").Delete Shift:=xlToLeft
    ChDir "C:\OYAK\MONFALCONE"
    ActiveWorkbook.SaveAs Filename:="C:\OYAK\MONFALCONE\MONFALCONE.txt", _
        FileFormat:=xlUnicodeText, CreateBackup:=False
 
    Application.ScreenUpdating = True	
 
End Sub

J'ai lancé la macro sur le poste d'un utilisateur et elle prend malheureusement 2min24 :(

Voici mon besoin en image:

Pièce jointe 152205

29/07/2014, 12h46
Marc-L

Pas étonnant vu la taille de la plage et le nombre de manipulations …

Désactiver aussi en début de procédure le mode de calcul automatique (Calculation).
Et ne pas oublier de le réactiver avant de sortir de la procédure !

L'image ne me parle pas trop …
29/07/2014, 14h54
rungis

1 pièce(s) jointe(s)

Après l'ajout de

Code:

Application.Calculation = xlCalculationManual

la macro s’exécute maintenant en 10 secondes mais les formules ne fonctionnent plus.

Exemple:

Pièce jointe 152224

Il met AUTO partout :(
Il ne prend plus les valeurs de chaque lignes, dans l'exemple ci-dessus BORSOI S.