Problème avec Fonction REPLACE

Version imprimable

Bonjour,

Je suis débutante depuis 2 jours sur VBA Excel (je ne suis pas développeuse pour info).

Voici mon problème,

J'ai un fichier Excel, où je dois corriger 3 Colonnes au niveau de leur Format de données en gros c'est du formatage de données. Une des 3 colonnes me posent problème = N° Téléphone

J'ai les format suivants possibles pour la colonne "n° téléphone" :
- 03.07.09.66.55
- 03-07-09-66-55
- 03,07,09,66,55
- 03;07;09;66;55
- 0307096655FAX
- SAM0307096655

Je voudrais avoir uniquement les 10 chiffres ni plus ni moins. Et si la cellule est vide je dois combler par des 0000000000

Voici le Code :

_____________________________________________
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 Public Sub Formatage() Dim NbEnregistrement As Integer Sheets(1).Select 'Compter le Nombre de lignes à importer / [-1] est pour décompter la ligne du libellé des colonnes NbEnregistrement = Application.CountA(Columns("A:A")) - 1 Range("T2").Activate Set plage = Range(ActiveCell(1, 1), ActiveCell(NbEnregistrement, 1)) For Each cell In plage cell.Value = CStr(cell.Value) cell.Value = Replace(cell, " ", "") cell.Value = Replace(cell, ",", "") cell.Value = Replace(cell, "-", "") cell.Value = Replace(cell, ";", "") cell.Value = CStr(cell.Value) 'Pour les cas suivants : FAX0203044456 ou 0201330045SAMB 'Si ce cas se présente, on remet la case à 0 If I = Len(cell) > 10 Then cell.Value = "0000000000" Else GoTo finit finit: Next cell End Sub
____________________________________________

Voici les soucis dont j'ai besoin de votre aide :

1. Comme je ne sais pas gérer les 2 cas "0307096655FAX" et "SAM0307096655", je met des 0 à la place.

Comment peux-t-on gérer ce cas ? Comment identifier ou détecter les lettres aux chiffres ...Je sais tronquer un champs mais je ne sais pas comment savoir si les lettres sont devant ou derrière.

2. Avant la conversion le format de la cellule est au format Texte. Après la conversion il se transforme au format Numérique et donc il m'enlève le 0. Résultat j'ai 9 Caractères au lieu de 10 attendus.

C'est très important car ce fichier va être importé dans un outils et la condition c'est Format Texte et 10 caractères ni plus ni moins.

Voilà .... MERCI D'AVANCE DE VOTRE AIDE !

Juliette.

Bonjour,

Essaye ceci:

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
Public Sub Formatage()
 
Dim NbEnregistrement As Integer, plage As Range
Dim Cel As Range, x As String, i As Byte, Result As String
 
Sheets(1).Select
'Compter le Nombre de lignes à importer / [-1] est pour décompter la ligne du libellé des colonnes
NbEnregistrement = Application.CountA(Columns("A:A")) - 1
Range("T2").Activate
Set plage = Range(ActiveCell(1, 1), ActiveCell(NbEnregistrement, 1))
 
For Each Cel In plage
    x = Cel.Value
    For i = 1 To Len(x)
        If IsNumeric(Mid(x, i, 1)) Then Result = Result & Mid(x, i, 1)
    Next
    Cel.Value = Result
    Result = ""
Next
 
End Sub

Salut Minhy et le forum
j'aime pas travailler sur la cellue active, donc je fais une autre proposition.
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 Sub test() 'Déclaration ================================== Dim X As Integer Dim L As String Dim Cel As Range 'traitment ==================================== For Each Cel In Range([A1], [A65536].End(xlUp)) 'pour chaque cellule de la plage Cel.NumberFormat = "@" 'format cellule = texte For X = Len(Cel) To 1 Step -1 'pour X = nb caractères cel à 1 L = Mid(Cel, X, 1) 'L= lettre en X If Not (L Like "[0-9]") Then _ Cel = Replace(Cel, L, "") 'si L <>0 à 9, le remplacer par vide dans cel Next X Next Cel End Sub
La macro sur les cellules de A:
- Met un format texte aux cellules (permet de garder les 0 avant les chiffre
- remplace tous caractères n'étant pas un chiffre

À toi de l'adapter suivant tes besoins.
S'il y a des questions n'hésite pas
A+

01/08/2008, 11h42
Minhy

Bonjour fring,

Grand pour ta réponse !!!

Très bonne idée pour le code, je n'aurais pas penser le gérer comme ca par contre voici le résultat sous excel :

http://img387.imageshack.us/img387/8578/expx4.jpg

Il m'enlève quand même le 0 et transforme le format de la cellule en numérique (à droite).
01/08/2008, 11h53
ilcocodrillo

plus besoin du VBA pour ça tu applique un format personnalisé à la colonne qui va bien " 0000000000" ou "00-00-00-00-00" si tu veux des tirets et tu aura ton zéro. ;).
01/08/2008, 11h55
Minhy

Coucou Gorfael , je viens de tester ta solution et ca marche !!

Je n'ai pas tout compris ton code mais très efficace.

Quand tu dis "j'aime pas travailler sur la cellue active, donc je fais une autre proposition."

Pourquoi ? Je pensais qu'il fallait activer les cellules enfin la sélection pour pouvoir travailler dessus.
01/08/2008, 11h56
fring

il suffit de mettre la colonne au format "texte", tu garderas l'allignement à gauche et les 0
01/08/2008, 11h56
Minhy

Citation:

Envoyé par ilcocodrillo

plus besoin du VBA pour ça tu applique un format personnalisé à la colonne qui va bien " 0000000000" ou "00-00-00-00-00" si tu veux des tirets et tu aura ton zéro. ;).

J'ai déjà tenté mais à l'importation il ne détecte pas le format que j'ai appliqué sous Excel car pour importer je dois le convertir en CSV. Et en CSV, il ne prend pas en compte le formatage itinialement appliqué sous Excel.

Citation:

Envoyé par fring

il suffit de mettre la colonne au format "texte", tu garderas l'allignement à gauche et les 0

Dès que j'applique le format Texte, il me le met bien à gauche en format Texte mais il ne me remet pas le 0.

Et lors de l'import, il me le rejète car il ne détecte que 9 caractère au lieu de 10.
01/08/2008, 11h59
Minhy

MERCI A TOUS D'AVOIR PRIS DU TEMPS POUR MON PROBLEME !!!! :king:
01/08/2008, 11h59
fring

Citation:

Envoyé par Minhy

Dès que j'applique le format Texte, il me le met bien à gauche en format Texte mais il ne me remet pas le 0.

Et lors de l'import, il me le rejète car il ne détecte que 9 caractère au lieu de 10.

Bin oui forcément qu'il ne va pas te remettre les 0 manquant...!
Il faut appliquer le format avant d'exécuter la procédure
01/08/2008, 11h59
ilcocodrillo
tu ajoute ça dans ta macro
Code:

1 2 Range(ta colonne).Select Selection.NumberFormat = "00-00-00-00-00"
ou tout autre format qui te conviens

pour du texte

Code:

Selection.NumberFormat = "@"
01/08/2008, 12h03
Minhy
Citation:

Envoyé par fring

Bin oui forcément qu'il ne va pas te remettre les 0 manquant...!
Il faut appliquer le format avant d'exécuter la procédure

Oups oui :? Désolée ... je vous avez dit que j'étais débutante loool Merci !!!!
Citation:
Envoyé par ilcocodrillo

tu ajoute ça dans ta macro

Code:

1 2 Range(ta colonne).Select Selection.NumberFormat = "00-00-00-00-00"

ou tout autre format qui te conviens

pour du texte

Code:

Selection.NumberFormat = "@"
En effet ... j'avais mal compris ... autant pour moi ... En tout cas, je ne la connaissais pas cette fonction de conversion ... à présent c'est mémorisé !!

Merci à tous de votre patience.
01/08/2008, 19h14
Gorfael
Salut Minhy et le forum

Citation:

Quand tu dis "j'aime pas travailler sur la cellue active, donc je fais une autre proposition."
Pourquoi ? Je pensais qu'il fallait activer les cellules enfin la sélection pour pouvoir travailler dessus.

Réponse de normand Oui et Non.
pour pouvoir travailler dans excel oui.
Pour pouvoir travaller en VBA sur des macro : ça dépend. À ma connaissance
- Pour un tri, il est nécessaire d'activer une feuille
- Pour travailler sur une "forme" (Shapes) il faut la sélectionner
Pour le reste, il n'y a aucune nécessécité d'activer ou de sélectionner.
Une sélection peut conduire
- à des erreurs d'Excel (si tu est sur un module d'une feuille et que tu veux travailler sur une autre feuille)
- à des ralentissements de traitements importants.

Quand tu fais une macro "apprentissage", Excel qui est bête, retranscrit en code toutes les opérations que tu effectues (et heureusement qu'il peut pas y incorporer "boire une gorgée de café").

Quand tu marque "A" en A1, excel te sort
Code:

1 2 3 Range("A1").Select '=>selectionner/activer A1 ActiveCell.FormulaR1C1 = "A" '=>mettre la formule "A" dans la cellule active Range("A2").Select '=> sélectionner la cellule A2
et c'est exactement ce que tu as fait. Mais c'est parce que tu n'as pas d'autre méthode pour rentrer dans A1 que de la sélectionner, la passer en mode édition et sélectionner la cellule en-dessous (ou une autre) pour valider. Mais tu peux avoir C1 de sléectionnée et lancer la macro :
Code:

1 2 3 Sub Test() Range("A1")="A" end sub
et à la sélection près, tu auras le même résultat.
Quand tu utilises un code issu d'une macro apprentissage,il faut toujours essayer de l'éprurer en enlevant les "Select" et les "Selection" qui les suivent (en faisant attention que l'instruction ait bien une plage de désignée).
A+