convertir des cellules en fonction de leurs tailles

**1pim34** · 28/03/2022, 11h53

Bonjour à tous,
je m'en remets à vous car je ne trouve pas ce que je souhaite ou je n'arrive pas à le mettre en place.
Sur l'exemple suivant, la première colonne est la colonne D et la seconde la colonne E dans mon fichier

F	148
G	51200000
G	62750000
G	51200000
G	64600000
G	51200000
C	1426

ce que je souhaite faire c'est modifier mon fichier d'origine de la façon suivante :

Si dans la colonne j'ai un "F" la nouvelle valeur de la cellule dans la colonne E est :
- si la longueur du contenu de la cellule dans E est de 1 alors : 401000000 & sa valeur d'origine
- si la longueur du contenu de la cellule dans E est de 2 alors : 40100000 & sa valeur d'origine
- si la longueur du contenu de la cellule dans E est de 3 alors : 4010000 & sa valeur d'origine
- si la longueur du contenu de la cellule dans E est de 4 alors : 401000 & sa valeur d'origine
- si la longueur du contenu de la cellule dans E est de 5 alors : 40100 & sa valeur d'origine

Si dans la colonne j'ai un "C" la nouvelle valeur de la cellule dans la colonne E est :
- si la longueur du contenu de la cellule dans E est de 1 alors : 411000000 & sa valeur d'origine
- si la longueur du contenu de la cellule dans E est de 2 alors : 41100000 & sa valeur d'origine
- si la longueur du contenu de la cellule dans E est de 3 alors : 4110000 & sa valeur d'origine
- si la longueur du contenu de la cellule dans E est de 4 alors : 411000 & sa valeur d'origine
- si la longueur du contenu de la cellule dans E est de 5 alors : 41100 & sa valeur d'origine

Si dans la colonne j'ai un "G" la nouvelle valeur de la cellule dans la colonne E est : sa valeur d'origine.

Voila ce que j'ai fait de façon intuitive :

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
Sub longueurcompte()
Dim A As Range
Dim B As Long
Dim C As Range
Dim E As Range
Dim F As Range
 
B = Range("E" & Rows.Count).End(xlUp).Row 'longueur colonne E
Set C = Range("D1:D" & B) 'colonne D
Set E = Range("E1:E" & B) 'colonne E
 
For Each A In E
    For Each F In C
        If C.Value = "F" Then
            If Len(A) = 1 Then
                A.Value = "401000000" & A.Value
            End If
            If Len(A) = 2 Then
                A.Value = "40100000" & A.Value
            End If
            If Len(A) = 3 Then
                A.Value = "4010000" & A.Value
            End If
            If Len(A) = 4 Then
                A.Value = "401000" & A.Value
            End If
            If Len(A) = 5 Then
                A.Value = "40100" & A.Value
            End If
        End If
 
 
        If C.Value = "C" Then
            If Len(A) = 1 Then
                A.Value = "411000000" & A.Value
            End If
            If Len(A) = 2 Then
                A.Value = "41100000" & A.Value
            End If
            If Len(A) = 3 Then
                A.Value = "4110000" & A.Value
            End If
            If Len(A) = 4 Then
                A.Value = "411000" & A.Value
            End If
            If Len(A) = 5 Then
                A.Value = "41100" & A.Value
            End If
        End If
    Next F
Next A
 
End Sub

Mais je me retrouve avec des soucis que je n'arrive pas à corriger.

Merci d'avance pour vos lumières.

Pierre

**21Formatic** · 28/03/2022, 12h58

Bonjour,

Une raison particulière pour faire ceci via VBA plutôt que via formules ?

Tu parles de cellules allant jusqu'à 5 caractères, et tu nous donnes des exemples allant jusqu'à 8 caractères... Dur de s'y retrouver.

N'aurais-tu pas un fichier exemple ?

Sinon, d'après ce que tu dis, mais qui semble incomplet, tu peux tenter quelque chose comme ceci peut-être ?

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
Sub test()
 
Dim A As Range
Dim B As Long
Dim C As Range
Dim E As Range
 
B = Range("E" & Rows.Count).End(xlUp).Row 'longueur colonne E
Set C = Range("D1:D" & B) 'colonne D
Set E = Range("E1:E" & B) 'colonne E
 
For Each A In E
    For Each F In C
        If F.Value = "F" Then A.Value = "401" & Application.Rept("0", 7 - Len(A)) & A.Value
            If F.Value = "G" Then A.Value = "411" & Application.Rept("0", 7 - Len(A)) & A.Value
                If F.Value = "C" Then A.Value = A.Value
    Next F
Next A
End Sub

C'est juste un exemple pour te faire voir. Cela va planter dans tous les cas quand la cellule aura plus de 7 caractères, ce qui semble être quasiment tout le temps le cas.
Tu avais également des confusions dans les lettres de tes variables. Tu mettais "if C.value = à "F" au lieu de tester F.value.

Il faut dire aussi que tes noms ne sont pas représentatifs. Essaie de mettre des noms qui ne prêtent pas à confusion et qui sont explicites...
Un select case ici serait bien également.

**1pim34** · 28/03/2022, 13h38

ah oui pardon,
Quand la longueur de la cellule fait plus de 5 on ne touche pas à sa valeur.

voici une partie du fichier :

50	01/01/2021	9460	G	62700000	ABON CYBERPLUS	3.08	0.00		TR	FRAIS BANCAIRES
50	01/01/2021	9460	G	51200000	ABON CYBERPLUS	0.00	3.08		TR	BANQUE DUPUY DE PARSEVAL
50	01/01/2021	9461	G	62700000	FRAIS EXTRAITS DEC	2.30	0.00		TR	FRAIS BANCAIRES
50	01/01/2021	9461	G	51200000	FRAIS EXTRAITS DEC	0.00	2.30		TR	BANQUE DUPUY DE PARSEVAL
50	01/01/2021	9462	G	10800000	ECHEANCE PRET	358.15	0.00		TR	COMPTE DE L'EXPLOITANT
50	01/01/2021	9462	G	51200000	ECHEANCE PRET	0.00	358.15		TR	BANQUE DUPUY DE PARSEVAL
50	04/01/2021	9463	G	42100000	VIR SALAIRE MR GELY DEC 2020	1512.11	0.00		TR	REMUNERATIONS
50	04/01/2021	9463	G	51200000	VIR SALAIRE MR GELY DEC 2020	0.00	1512.11		TR	BANQUE DUPUY DE PARSEVAL
50	04/01/2021	9464	G	42100002	VIR SALAIRE MME BENAMARA DEC 2020	713.45	0.00		TR	REMUNERATION S. BENAMARA
50	04/01/2021	9464	G	51200000	VIR SALAIRE MME BENAMARA DEC 2020	0.00	713.45		TR	BANQUE DUPUY DE PARSEVAL
50	04/01/2021	9465	G	10800000	VIR SALAIRE MR FERNANDEZ/BENAMARA DEC 20	1500.00	0.00		TR	COMPTE DE L'EXPLOITANT
50	04/01/2021	9465	G	51200000	VIR SALAIRE MR FERNANDEZ/BENAMARA DEC 20	0.00	1500.00		TR	BANQUE DUPUY DE PARSEVAL
50	04/01/2021	9466	G	10800000	VIR SALAIRE MR FERNANDEZ DEC 2020	1500.00	0.00		TR	COMPTE DE L'EXPLOITANT
50	04/01/2021	9466	G	51200000	VIR SALAIRE MR FERNANDEZ DEC 2020	0.00	1500.00		TR	BANQUE DUPUY DE PARSEVAL
50	04/01/2021	9467	F	148	CB CARBURANT BOLQUERE	53.01	0.00	1648	TR	DAC CARBURADO INTERMARCH
50	04/01/2021	9467	G	51200000	CB CARBURANT BOLQUERE	0.00	53.01		TR	BANQUE DUPUY DE PARSEVAL
50	04/01/2021	9469	G	62750000	FRAIS CB PAIEMENT CLIENT	0.84	0.00		TR	FRAIS CB
50	04/01/2021	9469	G	51200000	FRAIS CB PAIEMENT CLIENT	0.00	0.84		TR	BANQUE DUPUY DE PARSEVAL

et oui, je veux le faire via VBA parce que cela évitera aux personnes pour qui je souhaite faire cette macro de ne pas avoir à faire le travail avec les formules.

Merci d'avance.

**1pim34** · 28/03/2022, 14h22

Envoyé par 21Formatic

Bonjour,

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
Sub test()

Dim A As Range
Dim B As Long
Dim C As Range
Dim E As Range

B = Range("E" & Rows.Count).End(xlUp).Row 'longueur colonne E
Set C = Range("D1:D" & B) 'colonne D
Set E = Range("E1:E" & B) 'colonne E

For Each A In E
    For Each F In C
        If F.Value = "F" Then A.Value = "401" & Application.Rept("0", 7 - Len(A)) & A.Value
            If F.Value = "G" Then A.Value = "411" & Application.Rept("0", 7 - Len(A)) & A.Value
                If F.Value = "C" Then A.Value = A.Value
    Next F
Next A
End Sub

J'ai mis en gras dans ton code ce qui selon moi devrait pouvoir identifier quand faire la modification et quand il ne faut pas. d'ailleurs j'ai inversé sur le mien pour que ca colle à ce que je veux.
En gros ce que je souhaite c'est que quand dans la colonne D il y a un F ou un C cela fait la modification souhaité (F-->dans la colonne E en fonction de la taille de la cellule, E.value= 401X0 & E.value; C--> dans la colonne E en fonction de la taille de la cellule, E.value= 411&X0&E.value)

qu'est ce que je ne comprends pas ?

par contre j'ai un message : Erreur d'exécution '13': Incompatibilité de type

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
For Each A In E
    For Each F In C
        If F.Value = "F" Then A.Value = "401" & Application.Rept("0", 7 - Len(A)) & A.Value
            If F.Value = "C" Then A.Value = "411" & Application.Rept("0", 7 - Len(A)) & A.Value
                If F.Value = "G" Then A.Value = A.Value
    Next F

Est ce que tu sais à quoi ca correspond ?

Merci d'avance.

**1pim34** · 28/03/2022, 15h59

Bonjour à tous,

en me servant de ce que 21formatic m'a donné, j'ai ça

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
 
Sub longueur()
 
 
Dim A As Range
Dim B As Long
Dim C As Range
Dim E As Range
Dim F As Range
 
B = Range("E" & Rows.Count).End(xlUp).Row 'longueur colonne E
Set C = Range("D1:D" & B) 'colonne D
Set E = Range("E1:E" & B) 'colonne E
 
For Each A In E
    For Each F In C
 
        If F.Value = "F" And Len(A) < 6 Then
        A.Value = "401" & Application.Rept("0", 7 - Len(A)) & A.Value
        End If
        If F.Value = "C" And Len(A) < 6 Then
        A.Value = "411" & Application.Rept("0", 7 - Len(A)) & A.Value
        End If
    Next F
Next A
 
End Sub

Cela fait le job, par contre ça prend 30 minutes pour un fichier de 13000 lignes.

Est ce qu'il y a un moyen de faire plus rapide ?

Merci d'avance.

**21Formatic** · 28/03/2022, 16h54

Re,

Tu pourrais alléger un peu le code en faisant la vérification du nombre de caractères avant les IF. Comme ça, il ferait beaucoup moins de traitements. Tu peux aussi éviter la double boucle inutile que tu t'imposes. Pour quelques lignes cela ne posait pas de problèmes mais si tu en as des milliers c'est effectivement très loin d'être optimal.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
Sub test()
Derligne = Range("E" & Rows.Count).End(xlUp).Row 'longueur colonne E
 
For i = 1 To Derligne
With Cells(i, 5)
    If Len(.Value) < 6 Then
        If Cells(i, 4).Value = "F" Then .Value = "401" & Application.Rept("0", 7 - Len(.Value)) & .Value
            ElseIf Cells(i, 4).Value = "C" Then .Value = "411" & Application.Rept("0", 7 - Len(.Value)) & .Value
    End If
End With
Next
End Sub

En sachant que le plus rapide (presque instantané) serait de passer par un tableau en mémoire d'Excel. Je verrai si j'ai le temps de te bidouiller ça.

PS : je viens de tester et ça ne prend même pas un dixième de seconde sur 32 000 lignes.

**1pim34** · 29/03/2022, 08h13

Je te remercie, comme ç c'est parfait, ça prend effectivement 1 sec sur mon fichier.

Merci encore pour ton aide.

convertir des cellules en fonction de leurs tailles

Macros et VBA Excel

Vue hybride

Discussions similaires

Partager

Partager