Format nombre avec VBA

**NEC14** · 10/10/2017, 13h34

Bonjour le Forum,

Dans la zone A4:A178 de mon tableau, j'ai des références saisies, elles sont toutes écrites en format texte. Ces références ont ce format "CN 00 00 00 00 000"; "O0 00 00 00 00 000" et "00 00 00 00 00 000".
Dans la zone F25:F97, il y a une zone de liste dans chaque cellule, elles font appel aux références saisies dans A4:A178.

J'aimerai mettre en place, si cela est possible bien entendu, une macro qui modifierait automatiquement le format saisi en format texte.
Par exemple : je saisie CN01010203004, je souhaite que le format soit en texte CN 01 01 02 03 004 dans la cellule. Idem pour les autres formats (O102030405006 - O1 02 03 04 05 006; 0000000000000 - 00 00 00 00 00 000)

J'espère avec été clair dans mon explication. L'utilisateur tapera la référence sans espaces, (par exemple : CN00000000000) le but étant d'éviter de taper les espaces entre chaque chiffre.

Merci par avance pour l'aide que vous pourrez m'apporter
René

**eriiic** · 10/10/2017, 15h02

Bonjour,

un format, s'il est toujours le même pour une plage est à mettre directement sur la feuille. Aucun intérêt à le faire par macro.
De toute façon un format s'adresse à des nombres et CN01010203004 est du texte.
eric

**NEC14** · 10/10/2017, 18h44

Bonsoir,

Le format ne sera jamais le même sur une même plage. Pour le moment la saisie se fait en laissant des espaces entre les lettres et nombres, ce qui n'est pas pratique, c'est pour cela que j'ai opté pour une macro.
Quand il n'y a que un ou deux références à saisir on peut le faire sans automatiser la tâche, mais quand il y en a plusieurs dizaine, voir une centaine, ça prend du temps.

L'utilisateur n'aurait qu'à taper au kilomètre, et le format s'automatise. Mais si ce n'est pas possible tant pis.

Merci quand même d'avoir participer à ce post

**unparia** · 10/10/2017, 19h45

Bonjour
Ce sera donc en effet du texte et non un "nombre"
Tu peux toujours aller vers le ridicule et formater à l'avance le texte à saisir dans la cellule.
Regarde ce que fait ceci :

Code :

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

1
2
3
4
toto = "CN01010203004"
Range("A1") = Left(toto, 2) & " " & Format(Mid(toto, 3, 8), "@@ @@ @@ @@") & " " & Mid(toto, 11)
toto = "O102030405006"
Range("A2") = Left(toto, 2) & " " & Format(Mid(toto, 3, 8), "@@ @@ @@ @@") & " " & Mid(toto, 11)

Mais si tu fais cela, il vaudra mieux n'accepter que si 13 caractères saisis (pas un de plus, pas un de moins).

**NEC14** · 10/10/2017, 21h45

Bonsoir unparia,

Merci pour votre réponse. Effectivement c'est du texte, mais je ne peux pas, plutôt je ne sais pas, le personnaliser à toute ma plage. Je vais mettre votre proposition en œuvre et je vous tiens au courant.
Encore merci.
René

**NEC14** · 11/10/2017, 08h34

Bonjour,

J'ai mis le code en place, le format qu'il rend me plait bien; Maintenant il faut que je l'adapte à la cellule uniquement saisie car pour le moment il me rempli la colonne entière avec les mêmes données.

**Marc-L** · 12/10/2017, 09h48

Bonjour,

juste utiliser l'évènement Worksheet_Change de feuille de calculs …

Quant au formatage d'une cellule post saisie c'est possible de séparer la partie numérique via une expression rationnelle.
(possible aussi sans avec un peu de jugeote …)

Parmi les tutoriels à disposition sur ce forum (sans compter les exemples dans les discussions) :

• Les évènements dans la feuille de calcul Excel

• Les Expressions Rationnelles appliquées en VBA …

___________________________________________________________________________________________________________

Je suis Paris, Barcelone, London, Manchester, Egypte, Stockholm, Istanbul, Berlin, Nice, Bruxelles, Charlie, …

**patricktoulon** · 12/10/2017, 11h12

un exemple en vba avec l'évènement change de la feuille sur la colonne "A"

tout d'abords il faut bien comprendre que cet évènement se produit quand on tape sur enter ou sélectionne une autre cellule

comme on change le texte cela risque de tourner en boucle il faut alors métriser cela
si on joue avec enableevents on risque le rien du tout
il faut alors le gérer autrement sans supprimer les evenements du sheets

pour cela on utilisera simplement le test si il y a au moins un espace

Code :

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

1
2
3
4
5
6
7
8
9
10
11
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 1 Then
        If Not Target.Text Like " " Then
            lettre = Mid(Target.Text, 1, 2)
            nombre = Mid(Target.Text, 3, 16)
            'Debug.Print lettre
            'Debug.Print nombre
            Target.Value = lettre & " " & Format("  " & nombre, "## ## ## ## ###")
        End If
    End If
End Sub

Nom : demo.gif
Affichages : 4124
Taille : 242,5 Ko

on peu l'écrire plus simplement

Code :

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

1
2
3
4
5
6
7
8
9
10
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim lettre$,nombre$
    if Target.Column = 1 Then
        If Not Target.Text Like " " Then
            lettre = Mid(Target.Text, 1, 2) & " "
            nombre = Format(Mid(Target.Text, 3, 16), "## ## ## ## ###")
            Target.Value = lettre & nombre
        End If
    End If
End Sub

et tu fait la même chose pour tes autre colonne dans le même évènement dans un if else ou select case sur le numéro de colonne

**unparia** · 12/10/2017, 11h44

Bonjour Patrick
Corrige ta ligne 8 (elle ne tient pas compte de certaines positions éventuelles de 0s

EDIT : et ce n'est pas tout (tu es dans l'évènement Change. Ton processeur te demande de ne pas l'oublier

)

**patricktoulon** · 12/10/2017, 12h14

bonjour jacques
non je l'oubli pas c'est pour ca le if not like " "
il passe qu'une fois l'ors du changement de cellule

je n'en suis pas sur mais mes deux variables sont des string et contiennent le résultat de mid
donc le/les zéros éventuels qui pourraient se trouver devant sont pris en compte quand même

**unparia** · 12/10/2017, 12h45

Allons allons, Patrick ...
1) Tu crois vraiment que :

Code :

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

If Not Target.Text Like " " Then

est ce qu'il faut ?
N'importe quoi d'autre que " " ne sera pas like " "
2) pour le reste : fais donc quelques essais

Bon appétit

**unparia** · 12/10/2017, 13h13

Bon
La voilà, ta correction, pour éviter à la fois le piège des zéros mal placés et celui d'un évènement Change qui se répète jusqu'à plus soif :

Code :

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

1
2
3
4
5
6
7
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 1 Then
        If InStr(Target.Text, " ") = 0 Then
           Target.Value = Format(Left(Target.Text, 10), "@@ @@ @@ @@ @@") & " " & Mid(Target.Text, 11)
        End If
    End If
 End Sub

**patricktoulon** · 12/10/2017, 13h30

oui le instr c'est vrai
apres le formatage avec les "@"

mais pourquoi tu découpe en 2 le replacement ?avec les"@" pas besoins ?
ceci fonctionne très bien chez moi

Code :

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

Target.Value = Format(Target.Text, "@@ @@ @@ @@ @@ @@@")

2 lettres, 4 doubles, 1 triple

**unparia** · 12/10/2017, 13h35

mais pourquoi tu découpe en 2 le replacement ?avec les"@" pas besoins ?

je ne l'ai fait que pour rester au plus près de ce que TU avais fait...

**RyuAutodidacte** · 12/10/2017, 13h39

Bonjour,

code non habituelle mais pour le fun et dans l'idée de le faire par séquence, surement améliorable :

Code :

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

1
2
3
4
5
6
7
8
9
10
Sub ParSequence()
Dim titi$, toto$, seq, n, i As Byte
    titi = "CN01010203004"
    seq = Array(1, 3, 5, 7, 9, 11)
    n = Array(2, 2, 2, 2, 2, 3)
    For i = LBound(seq) To UBound(seq)
        toto = toto & " " & Mid(titi, seq(i), n(i))
    Next
    Debug.Print Trim(toto)
End Sub

on ne se soucie pas que ce soit des chiffres ou des caractères et au final on récupère le string

Edit :

Jacques

**patricktoulon** · 12/10/2017, 13h43

a ok

au final il est plus facile de traiter tout en string par les"@"
de toute façon a partir du moment ou il y a une lettre la cellule est au format standard ou text
donc on se casse pas la tète
bon ben il a ce qu'il faut maintenant

**patricktoulon** · 12/10/2017, 13h47

oui RYU
tout les chemin mènent a Rome

perso je préfère les routes bien goudronnées, j'ai une voiture bas de caisse et un dos bien esquinté

**RyuAutodidacte** · 12/10/2017, 13h51

le revoilà un peu améliorer

:

Code :

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

1
2
3
4
5
6
7
8
9
10
11
Sub ParSequence2()
Dim titi$, toto$, seq, n as Byte, i As Byte
    titi = "CN01010203004"
    seq = Array(2, 2, 2, 2, 2, 3)
    n = 1
    For i = LBound(seq) To UBound(seq)
        toto = toto & " " & Mid(titi, n, seq(i))
        n = n + seq(i)
    Next
    Debug.Print Trim(toto)
End Sub

perso je préfère les routes bien goudronnées, j'ai une voiture bas de caisse et un dos bien esquinté

je te comprends

**patricktoulon** · 12/10/2017, 14h08

ryu teste en condition réelle sur une cellule dans l'évènement

dans ton exemple ta chaine est entre guillemet donc déjà en string mais dans une cells ca peut différé

teste!

**RyuAutodidacte** · 12/10/2017, 17h47

Re Patrick ,

Trop de taf, je n'ai pas pu répondre avant …
Un exemple concret serait bien stp, je n'ai pa réussi à reproduire le problème que tu décris …

Format nombre avec VBA [XL-2010]

Macros et VBA Excel

Discussions similaires

Partager

Partager