Forcer un format à 4 chiffres avec 0 par défaut sur des codes

**Arno_ap** · 08/02/2011, 18h14

Bonjour,

J'ai un petit souci.

J’ai dans 2 colonnes (I et H) des codes à 4 chiffres, commençant régulièrement par des 1,2 voir 3 zéro.
Il y très peu de codes identiques et servent à alimenté une base de données et des plans autocad et doivent obligatoirement être sous le format XXXX.
Pour ce fichier 11500.

Malheureusement les personnes ayant rempli le tableau ont mis le type de code en lettre devant (même cellule donc).

J’ai donc enregistré la commande, format texte, puis rechercher le texte et le remplacer par « rien » et l’ai incrémenté. Mais quoi que je fasse les zéro s’en vont… il y a-t-il un moyen qu’il reste, ou de dire à Monsieur Excel d'incrementé en debut de cellule le morceau de code restant avec des zéro pour arrivé à 4 chiffres?

Désolé je ne connais que pas du tout le Vba.

Merci d’avance

Arno

PS : Voici mon super méga top premier code :

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
Sub Ug()
'
' Ug Macro
' Macro enregistrée le 07/02/2011 par Administrateur
'
 
'
' Format texte
 
    Cells.Select
    Selection.NumberFormat = "@"
    Selection.Locked = False
    Selection.FormulaHidden = False
    Range("A1").Select
 
 
' Recherche et remplacement Ug Occupant
 
          Columns("H:H").Select
    Selection.Replace What:="UGC ", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
 
          Columns("H:H").Select
    Selection.Replace What:="UG ", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
 
          Columns("H:H").Select
    Selection.Replace What:="0ATN ", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
 
' Recherche et remplacement Ug Affectation
 
          Columns("I:I").Select
    Selection.Replace What:="UGC ", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
 
          Columns("I:I").Select
    Selection.Replace What:="UG ", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
 
          Columns("I:I").Select
    Selection.Replace What:="0ATN ", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
 
 
    ActiveWorkbook.save
 
 
 
End Sub

**helas** · 08/02/2011, 18h35

Bonsoir,
et avec ?

Code :

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

1
2
 
Selection.NumberFormat = "0000"

**Pierre Fauconnier** · 08/02/2011, 23h05

Salut.

Attention que modifier le format d'affichage ne modifie pas la valeur de la cellule...

Dans ton cas, je pense qu'il n'y a pas d'autre solution que de boucler sur les cellules. Voici le code pour la colonne H (colonne 8). A toi d'adapter pour les colonnes suivantes.

Code VB :

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

1
2
3
4
5
6
7
8
9
  Dim Plage As Range
  Dim Cellule As Range
 
  Set Plage = Range(Cells(1, 8), Cells(Rows.Count, 8).End(xlUp)) ' évite de boucler sur toutes les lignes
  Plage.NumberFormat = "@" ' Format texte
  For Each Cellule In Plage
    Cellule.Value = Format(Replace(Cellule.Value, "UGC ", ""), "0000")
    Cellule.Value = Format(Replace(Cellule.Value, "UG ", ""), "0000")
  Next Cellule

[EDIT] Code modifié sur la ligne "set plage = " suite à la remarque pertinente de Qwerty [/EDIT]

**Arno_ap** · 09/02/2011, 13h09

Bonjour,

Merci pour ces réponses rapides et efficaces

@ helas : Trés bonne idée, mais le soucis est que si par exemple le code = 0001, la cellule affiche bien 0001, mais en réalité (dans la barre en haut) il n'y a que le 1 .

@ Pierre Fauconnier, Merci pour ce code qui marche parfaitement, et qui en plus incremente avec des zéro en début de cellule quand le code à été entré dans une cellule n'yant pas "subit" le format texte.

Puis je vous demandé qu'est ce qui fait quoi dans ce code (par curiosité de comprendre

=)

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
Dim Plage As Range  '<= Que veux dire ceci ? cette ligne ne doit apparaitre qu'une fois pour chaque "commande" (su? (me suis fais avoir quand j'ai incrementé pour la colonne 9 :mrgreen:
  Dim Cellule As Range 'Même question que ce dessus
 
  ' Occupant
 
 
  Set Plage = Range(Cells(1, 8), Cells(Rows.Count, 1).End(xlUp)) ' évite de boucler sur toutes les lignes. 
'Set plage = selection de la plage à traiter je suppose. Range = Colonne ? et range(cells(1, 8) = à commence cellule 1, colonne 8 ? 
' Cells(Rows.Count, 1).End(xlUp)) Ca par contre je ne vois pas trop.. :roll:
  Plage.NumberFormat = "@" ' Format texte
 
 
  For Each Cellule In Plage ' Dans la plage de donnée trouvée plus haut applique (Stp Mr Excel) la "régle" suivante ?
    Cellule.Value = Format(Replace(Cellule.Value, "UGC ", ""), "0000")
    Cellule.Value = Format(Replace(Cellule.Value, "UG ", ""), "0000")
    Cellule.Value = Format(Replace(Cellule.Value, "0ATN", ""), "0000")
  Next Cellule

A oui je souhaiterais commencer cette opération à partir de la cellule H14 (donc ligne 14 colonne 8) et I14 (donc ligne 14 colonne 9)

Donc je remplace le 1 de Set Plage = Range(Cells(1, 8), Cells(Rows.Count, 1).End(xlUp)) ' évite de boucler sur toutes les lignes par 14

Cela donne :

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
  Dim Plage As Range
  Dim Cellule As Range
  ' Occupant
  Set Plage = Range(Cells(14, 8), Cells(Rows.Count, 8).End(xlUp)) ' évite de boucler sur toutes les lignes
  Plage.NumberFormat = "@" ' Format texte
  For Each Cellule In Plage
    Cellule.Value = Format(Replace(Cellule.Value, "UGC ", ""), "0000")
    Cellule.Value = Format(Replace(Cellule.Value, "UG ", ""), "0000")
    Cellule.Value = Format(Replace(Cellule.Value, "ATN ", "9935"), "0000")
  Next Cellule
 
  'Affectation
 
  Set Plage = Range(Cells(14, 9), Cells(Rows.Count, 9).End(xlUp)) ' évite de boucler sur toutes les lignes  Plage.NumberFormat = "@" ' Format texte
  For Each Cellule In Plage
    Cellule.Value = Format(Replace(Cellule.Value, "UGC ", ""), "0000")
    Cellule.Value = Format(Replace(Cellule.Value, "UG ", ""), "0000")
  Next Cellule

Par contre je n'arrive pas à remplacer purement et simplement la valeur ATN (et non 0ATN) par 9935

J'ai donc fais ceci :

Code :

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

1
2
3
4
5
6
Columns("H:H").Select
    Range("H7").Activate
    Selection.Replace What:="ATN", Replacement:="9935", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Range("A1").Select

Tout simplement

Puis je encore abusé de votre savoir Vbien? Allez j'ose

Dans le cas ou je souhaiterais appliquer cette "régle" de recherche et remplacement à tous les onglets d'un tableau, en sachant que les noms des onglets sont compris dans une liste prédéfini de 71 possibilité qu'il n'y a pas forcement les mêmes onglets dans chaque tableau (mais le nom est forcement compris dans la liste des 71) c'est possible ?

Ou peut être qu'il y as plus simple dans la même idée que Next Cellule, par exemple next onglet non?

En tout cas merci beaucoup

**Phileo** · 09/02/2011, 15h51

Bonjour

Pour appliquer un code à tous les onglets d'un classeur il faut parcourir la collection de toutes les feuilles de ce classeur

Exemple : pour le classeur actif

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
 
Sub exemple()
 
 
Dim feuille As Worksheet
For Each feuille In ActiveWorkbook.Worksheets ' passe en revue toute les feuilles du classeur active
 
' affiche le nom de chaque feuille
' à remplacer par le code souhaité
MsgBox feuille.Name
 
Next
 
 
End Sub

**Arno_ap** · 09/02/2011, 18h03

Bonjour, ou re pour ceux que j'ai déjà croisé tout à l'heure

Je ne doit pas avoir tout compris (enfin c'est sur même)

Le code (Posté par Phileo) marche nickel . (Pour lister les onglets que j'ai)

Faut que lorsque j'insere ma macro au millieu ban il ne se passe pas grand chose... ya bien un truc mais pas de résultat

Ce que j'ai fais :

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
Sub Ug()
 
Dim feuille As Worksheet
For Each feuille In ActiveWorkbook.Worksheets ' passe en revue toute les feuilles du classeur active
 
Dim Plage As Range
  Dim Cellule As Range
 
  Set Plage = Range(Cells(14, 8), Cells(Rows.Count, 8).End(xlUp)) ' Occupant évite de boucler sur toutes les lignes
  Plage.NumberFormat = "@" ' Format texte
  For Each Cellule In Plage
    Cellule.Value = Format(Replace(Cellule.Value, "UGC ", ""), "0000")
    Cellule.Value = Format(Replace(Cellule.Value, "UG ", ""), "0000")
    Cellule.Value = Format(Replace(Cellule.Value, "UG", ""), "0000")
      Next Cellule
    Columns("H:H").Select ' Remplacement ATN par 9935
    Range("H7").Activate
    Selection.Replace What:="ATN", Replacement:="9935", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Range("A1").Select
  Set Plage = Range(Cells(14, 9), Cells(Rows.Count, 9).End(xlUp)) ' Affectation évite de boucler sur toutes les lignes  Plage.NumberFormat = "@" ' Format texte
  For Each Cellule In Plage
    Cellule.Value = Format(Replace(Cellule.Value, "UGC ", ""), "0000")
    Cellule.Value = Format(Replace(Cellule.Value, "UG ", ""), "0000")
    Cellule.Value = Format(Replace(Cellule.Value, "UG", ""), "0000")
  Next Cellule
 
Next
 
End Sub

J'ai farfouiller à droite à gauche mais je ne pige pas trop ou il faut mettre les lignes sup

D'un autre coté je ne comprend pas du tout (ou presque) ce que je viens de copier/coller

**Qwazerty** · 09/02/2011, 19h22

Salut
Je me permet de commenter le code de Pierre F sur ta demande.

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
  Dim Plage As Range
  Dim Cellule As Range
 
'Defini la plage sur laquelle on va travailler
'On commence a partie de la cellule Cells(1, 8), pourrait simplifier en mettant directement "H1"
'Et on ce place sur la dernière cellule de la colonne A, ici Cells(Rows.Count, 1)
'Puis on remonte jusqu'a trouver une cellule non vide End(xlUp)
'La plage est donc H1:Ax , avec x la dernière ligne non vide de la colonne A
  Set Plage = Range(Cells(1, 8), Cells(Rows.Count, 1).End(xlUp)) ' évite de boucler sur toutes les lignes
  Plage.NumberFormat = "@" ' Format texte (on change le format de toutes les cellules de la plage)
  'On boucle sur chaque cellules de la plage, à chaque itération de la boucle 
  'Cellule représentera une des cellules. 
  For Each Cellule In Plage
    'On remplace UGC et UG par rien, et on format pour toujours avoir 4 chiffres, les zéros seront soit remplacés par un chiffre, soit par un zéro si pas de chiffre dispo dans la chaîne (voir F1 sur Format)
    Cellule.Value = Format(Replace(Cellule.Value, "UGC ", ""), "0000")
    Cellule.Value = Format(Replace(Cellule.Value, "UG ", ""), "0000")
  Next Cellule

Pour info, lorsque tu places du code sur ton sujet, sélectionne le code et clique sur le bouton # dans la barre de menu de l’éditeur.

Pour

Code :

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

Set Plage = Range(Cells(1, 8), Cells(Rows.Count, 1).End(xlUp))

Comme je le dis dans le commentaire, je remplacerais par

Code :

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

Set Plage = Range("H1", Cells(Rows.Count, 1).End(xlUp))

Je trouve cette écriture plus lisible
++
Qwaz

**mercatog** · 09/02/2011, 19h33

Bonjour, je crois que pour la boucle sur les feuilles, il fallait adapter comme ceci

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
Sub Ug()
Dim feuille As Worksheet
Dim Plage As Range, Cellule As Range
 
Application.ScreenUpdating = False
For Each feuille In ThisWorkbook.Worksheets   ' passe en revue toute les feuilles du classeur active
   With feuille
      Set Plage = .Range(.Cells(14, 8), .Cells(.Rows.Count, 9).End(xlUp))   ' Occupant évite de boucler sur toutes les lignes
      Plage.NumberFormat = "@"   ' Format texte
      For Each Cellule In Plage
         Cellule.Value = Format(Replace(Cellule.Value, "UGC ", ""), "0000")
         Cellule.Value = Format(Replace(Cellule.Value, "UG ", ""), "0000")
         Cellule.Value = Format(Replace(Cellule.Value, "UG", ""), "0000")
      Next Cellule
      .Range("H:H").Replace What:="ATN", Replacement:="9935", LookAt:=xlPart
   End With
Next feuille
End Sub

**Pierre Fauconnier** · 09/02/2011, 20h46

Explication du code comprenant l'ajout de Mercatog pour traiter les onglets

Code :

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

1
2
3
Sub Ug()
Dim feuille As Worksheet
Dim Plage As Range, Cellule As Range

Lignes de déclaration des variables. Elles indiquent à Excel que tu vas utiliser des variables nommées feuille, Plage et Cellule, et que tu fixes le type de la variable, grâce au mot clé As.
Worksheet signifie "feuille de calcul" => feuille sera un objet "feuille de calcul". Range signifie "Plage" (ensemble de cellules). Idéalement, il faut déclarer les variables avec au moins une majuscule, et les saisir à l'intérieur du code en minuscules. L'éditeur convertit alors les minuscules en majuscules selon la ligne de déclaration, ce qui permet un débogage visuel lors de la saisie (ce qui reste en minuscules est susceptible de contenir une erreur de frappe, par exemple). A ce sujet, lire ce tuto de Maxence et celui de Michel.

Code :

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

1
2
 
Application.ScreenUpdating = False

gèle l'écran durant l'exécution de la macro. Il est nécessaire, dans certains contextes, de basculer la propriété à TRUE en fin de macro = > c'est une bonne habitude de le faire systématiquement.

Code :

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

1
2
 
For Each feuille In ThisWorkbook.Worksheets

Feuille est un objet "Feuille de calcul". La boucle "For each ... Next" permet de boucler sur tous les objets de la collection, ici toutes les feuilles de la collection "Feuilles de calcul" du classeur

Code :

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

1
2
3
 
   With feuille
      Set Plage = .Range(.Cells(14, 8), .Cells(.Rows.Count, 9).End(xlUp))

Le bloc "With... End With" permet de préciser que tu vas travailler avec les propriétés et/ou les méthodes d'un objet, ici la variable "Feuille". Il suffira alors de préfixer une propriété avec un point pour indiquer que tu travailles avec une propriété de l'objet repris sur la ligne "With...".
La ligne "Set..." attribue à la variable Plage la plage commençant en H14 et se terminant à la dernière ligne utilisée de I.
La présence du point devant le mot Rows est indispensable pour éviter un plantage si la feuille active est une feuille de graphe.
Le bloc ".Cells(.Rows.Count, 9).End(xlUp)" signifie que tu pointes vers la première cellule non vide en partant de la dernière cellule de I et en remontant. Pour permettre une compatibilité de version, on a pris l'habitude, depuis la version 2007, de pointer vers la dernière cellule et de remonter plutôt que de pointer vers la cellule en ligne 65536 (limite 2003) puis de remonter.

Code :

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

1
2
 
      Plage.NumberFormat = "@"   ' Format texte

bascule les cellules de la plage en format texte

Code :

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

1
2
3
4
5
6
 
      For Each Cellule In Plage
         Cellule.Value = Format(Replace(Cellule.Value, "UGC ", ""), "0000")
         Cellule.Value = Format(Replace(Cellule.Value, "UG ", ""), "0000")
         Cellule.Value = Format(Replace(Cellule.Value, "UG", ""), "0000")
      Next Cellule

Pour chaque cellule de la plage, remplace une chaine par une autre, en forçant le format à 4 chiffres, de manière à placer le 0 à gauche dans la valeur de la cellule.

Code :

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

1
2
3
 
      .Range("H:H").Replace What:="ATN", Replacement:="9935", LookAt:=xlPart
   End With

recherche "ATN" pour le remplacer. Perso, j'aurais gardé la même logique de programmation que pour les autres remplacements en utilisant

Code :

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

1
2
 
if cellule.column=8 then cellule.value=replace(cellule.value,"ATN","9935")

C'est un peu moins performant (en terme de millisecondes), mais on garde ainsi la même structure de raisonnement et la même façon d'agir, ce qui rend le code plus lisible et sa maintenance plus aisée.

Code :

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

1
2
 
Next feuille

ferme le "For..." qui nécessite toujours un "Next". Le "feuille" est facultatif mais rend le code plus lisible.

Un peu de lecture sur le VBA ne fera pas de tort pour progresser.

**Pierre Fauconnier** · 09/02/2011, 20h49

Qwazerty,

Oui oui, tu as raison. Il y avait une erreur dans mon code initial (c'est pour voir qui suivait

)

Il fallait lire
Set Plage = Range(Cells(1, 8), Cells(Rows.Count, 8).End(xlUp))Le code de Mercatog balaie en une fois les deux colonnes (plus productif dès lors que le traitement est identique), puisqu'il a utilisé
Set Plage = Range(Cells(1, 8), Cells(Rows.Count, 9).End(xlUp))

**Pierre Fauconnier** · 09/02/2011, 19h59

Envoyé par Qwazerty

Pour

Code :

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

Set Plage = Range(Cells(1, 8), Cells(Rows.Count, 1).End(xlUp))

Comme je le dis dans le commentaire, je remplacerais par

Code :

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

Set Plage = Range("H1", Cells(Rows.Count, 1).End(xlUp))

Je trouve cette écriture plus lisible
++
Qwaz

==> Cela te donnera une plage A1:Hx et non H1:Hx !!

**Qwazerty** · 09/02/2011, 20h20

Salut Pierre

Envoyé par Pierre Fauconnier

==> Cela te donnera une plage A1:Hx et non H1:Hx !!

Heu, désolé, mais ton code donnera également une plage de A1:Hx.
Mais c'est vrai que je me suis trompé au niveau de mon commentaire dans ton code, on aura non pas "H1:Ax" mais "A1:Hx", ce qui bien entendu couvrira les mêmes cellules.
Donc à moins que je ratte quelque chose

Code :

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

1
2
Set Plage = Range(Cells(1, 8), Cells(Rows.Count, 1).End(xlUp))
Set Plage = Range("H1", Cells(Rows.Count, 1).End(xlUp))

Sont 2 écritures parfaitement identiques, non?

Pour avoir H1:Hx on utiliserait

Code :

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

Set Plage = Range("H1", Cells(Rows.Count, 8).End(xlUp))

++
Qwaz

Forcer un format à 4 chiffres avec 0 par défaut sur des codes

Macros et VBA Excel

Vue hybride

Discussions similaires

Partager

Partager