[VBA Excel] rechercher/remplacer & format Résolu/Fermé

Question

Bonjour à tous !

Je bosse actuellement sur une petite automatisation de données. Par défaut, les valeurs du champ "Date" sont au format standard : "20.07.2009", je ne peux donc pas les traiter convenablement.
J'ai enregistré une macro pour rechercher les "." et les remplacer par des "/". Voici d'ailleurs mon code VBA : 

Columns("F:F").Select
Selection.Replace What:=".", Replacement:="/", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=True, _
ReplaceFormat:=True

Seulement voilà, alors que pour toutes les valeurs le "." est bien remplacé par un "/", certaines d'entre elles ne basculent pas automatiquement au format date. Comme j'automatise mes rechercheV, je dénombre une grosse quantité de données non trouvées car la cellule date qui sert de référence se trouve au format standard et non pas au format date.

J'ai essayé d'automatiser la mise au format date sans réussir. La seule solution que j'ai jusque là est de stoper la macro et de faire un rechercher/remplacer manuellement. Lorsque je procède de cette manière, le changement de format se fait sans problèmes.

Je remercie à l'avance tous ceux qui prendront la peine de me venir en aide.

Merci à vous !

kimclem · Answer

je suis moi même un débutant en VB mais j'ai intégrer la fonction rechercher remplacer une fois dans une macro.
Cela fonctionnait correctement sauf que le contenu n'était pas hmm "rafraichi" dirons nous, peut être que c'est le même cas pour toi.
Manipulation toute bête, après ta macro tu ouvres un rechercher remplacer : rechercher sur : "/" et remplacer sur : "/".
Cela n'effectuera aucun changement sur le contenu de tes cellules mais tout sera rafraichi et mes #valeurs se transformaient en nombre.
J'espère avoir pu te donner un coup de pouce :)
Clément

The Flow · Answer

Ce que tu me proposes est exactement la manipulation manuelle par laquelle je passe faute de trouver mieux :)  Peut-être qu'il faut juste trouver un moyen de coder une fonction "rafraîchir" comme tu dis.

kimclem · Answer

ta macro n'effectue qu'un rechercher remplacer ? :)
si oui, je ne comprends pas pourquoi faire une macro, pour ma part elle ne faisait pas que rechercher remplacer donc la solution bidouille me convenait proprement, surtout que c'était a des fins privés, après si des clients doivent la manipuler, ce n'est pas tres propre mais le "rafraichir" je n'ai pas trouvé désolé

The Flow · Answer

Ce n'est qu'une étape parmi d'autre dans ma macro, donc j'ai vraiment besoin de l'automatiser :)

redonky · Answer

Range(ta cellule).numberformat = "m/d/yyyy"

ou encore peut être mieux pour toi

Columns(ta colonne).numberformat = "m/d/yyyy"

The Flow · Answer

Voici mon code :
    Columns("F:F").Select
    Selection.Replace What:=".", Replacement:="/", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=True, _
        ReplaceFormat:=True
    Columns("F:F").NumberFormat = "m/d/yyyy"

Malheureusement, cela ne fonctionne toujours pas. Après avoir effectué cette macro, le changement de format ne s'effectue que si je procède à un rechercher/remplacer des "/" par des "/". La macro réalise bien le changement du caractère des "." par des "/", mais doit altérer la valeur de la cellule. En effet, Excel parvient à me trouver 12000 cellules pour lesquelles des "/" remplacés par des "/"... il y a de quoi s'arracher les cheveux...

Les valeurs contenues dans mes cellules du champ "date" sont pures, il n'y a pas de ="..." pour encadrer la date. Il n'y a donc rien pour empêcher le changement de format.

J'ai essayé de faire une macro qui consisterait à copier toute la colonne et à coller les valeurs dans une nouvelle, mais cela ne fonctionne pas...

Quelqu'un aurait-il une autre solution à proposer ?


merci d'avoir répondu !

redonky · Answer

En essayant juste ça

Columns("F:F").NumberFormat = "m/d/yyyy"

ou ça

Columns("F:F").Select
    Selection.numberformat = "m/d/yyyy"

ou dans ton code actuel remplacer

Columns("H:H")..numberformat = "m/d/yyyy"

par

Selection.numberformat = "m/d/yyyy"
?

eriiic · Answer

Bonsoir,

Méfie toi des dates en vba, il se refuse souvent à reconnaitre le format imposé dans les paramètres régionaux.
Et heureusement que tu en as eu de non converties ce qui t'a alerté, mais en fait celles qui sont converties sont sûrement fausses (jour et mois inversés...)
Essaie avec :
Sub convDate()
    Dim c As Range
    For Each c In Range([F1], [F65536].End(xlUp))
        c = DateValue(Replace(c, ".", "/"))
    Next c
End Sub

Si tu es sur excel 2007 remplace F65536 par la dernière cellule.
Et en règle général si l'affichage ne correspond pas à ce que tu attends (puisque ça fait 2 fois que tu as un soucis de format) contrôle de près le contenu de la cellule et le type, surtout si une simple application du format voulu ne résoud pas le pb. Si la données est conforme excel applique en général le bon format.

Et comme tu commences à être un habitué ;-) tu devrais t'inscrire. 
Ca te permettra de suivre plus facilement tes conversations, et de les mettre en résolu le moment venu (ça c'est plus pour les helpers, ça évite de lire 10 posts pour voir que finalement il n'y a plus de pb...). Merci

eric
.

The Flow · Answer

Ton code fonctionne ! Cependant j'ai une erreur d'exécution '13' : incompatibilité de type.

J'ai jeté un coup d'oeil à ma colonne, chacune des cellules ont bien été traitées jusqu'à ce qu'Excel tombe sur une qui cafouille. En effet, il s'agit d'une coquille dont la cellule n'est pas au bon format (il y a eu un décalage dans mes extracts uniquement sur cette ligne), du coup il refuse de la prendre en compte et met fin au processus. 

Je pense qu'on devrait pouvoir toucher au but en indiquant un truc du genre "si pendant le rechercher/remplacer on tombe sur une valeur bizarre, alors on l'ignore et on passe à la suivante".
Savez vous traduire ça en VBA svp ? :p

eriiic · Answer

Bonjour,

Essaie avec ça :Sub convDate()
    Dim c As Range, d As String, erreurs As String
    For Each c In Range([F1], [F65536].End(xlUp))
        d = Replace(c, ".", "/")
        If IsDate(d) Then
            c = DateValue(d)
        Else
            erreurs = erreurs & c.Row & "-"
        End If
    Next c
    If Len(erreurs) Then MsgBox ("Lignes non conformes :" & vbCrLf & erreurs)
End Sub
eric

The Flow · Answer

Ca fonctionne merci mille fois !

J'ai utilisé ce code pour deux colonnes : 
    Dim c As Range, d As String, erreurs As String
    For Each c In Range([J1], [J65536].End(xlUp))
        d = Replace(c, ".", "/")
        If IsDate(d) Then
            c = DateValue(d)
        Else
            erreurs = erreurs & c.Row & "-"
        End If
    Next c
    For Each c In Range([F1], [F65536].End(xlUp))
        d1 = Replace(c, ".", "/")
        If IsDate(d1) Then
            c = DateValue(d)
        Else
            erreurs = erreurs & c.Row & "-"
        End If
    Next c

Ce n'est peut-être pas super clean, mais ça fonctionne ;)

The Flow · Answer

Erratum : 

    Dim c As Range, d As String, erreurs As String
    For Each c In Range([F1], [F65536].End(xlUp))
        d = Replace(c, ".", "/")
        If IsDate(d) Then
            c = DateValue(d)
        Else
            erreurs = erreurs & c.Row & "-"
        End If
    Next c

    Dim x As Range, y As String, erreurs2 As String
    For Each x In Range([J1], [J65536].End(xlUp))
        y = Replace(x, ".", "/")
        If IsDate(y) Then
            x = DateValue(y)
        Else
            erreurs2 = erreurs2 & x.Row & "-"
        End If
    Next x

eriiic · Answer

Bonsoir,

merci pour le retour :-)
Si tu n'affiches pas les erreurs tu peux enlever les else et ce qu'il y a dedans.
Et tu pouvais réutiliser les variables vu qu'elles sont libres à la fin de la 1ère boucle...
Si tu reposes une question tu devrais t'inscrire, ça te permettra de mieux suivre les réponses, et de mettre en résolu une fois que c'est terminé (ça évite à un helper de tout lire pour voir que c'est fini)
Je le met à ta place

eric

[VBA Excel] rechercher/remplacer & format

13 réponses

Discussions similaires