XL 2013 Conversion d'un tableau Excel en numérique

[Daniel Desch]

Bonjour,

Avant de transférer les données sur un AS400 via Client Access je souhaite harmoniser un tableau Excel en convertissant toutes les colonnes en numérique

Le tableau très simple contient une dizaine de colonnes dont les contenus sont par nature numérique (des entiers qui plus est) mais suivant la façon qu'ont les utilisateurs de les remplir (souvent des copies d'autres données) certaines cases se retrouvent avec de l'alpha dedans (case avec le triangle vert) d'autres cases sont effectivement numériques
Une méthode assez simple pour convertir le tableau c'est de procéder colonne par colonne en utilisant convertir. Mais s'il y a 10 colonnes il faut demander aux utilisateurs de faire ça 10 fois ce qui devient assez fastidieux. Malheureusement on ne peut pas convertir les 10 colonnes en une seule opération
Comment faire pour automatiser ce processus ?

Merci par avance pour vos réponses

Cordialement

 

[Dranreb]

Bonsoir.
Essayez cette macro :

Option Explicit
Sub ConvTxtNum()
   Dim Cel As Range
   For Each Cel In ActiveSheet.UsedRange.SpecialCells(xlCellTypeConstants, 2)
      If IsNumeric(Cel.Value) Then Cel.Value = CDbl(Cel.Value)
      Next Cel
   End Sub

 

[eriiic]

Bonjour,

Copie une cellule vide, sélectionne A1, shift+Ctrl+Fin pour sélectionner toute la zone, puis collage spécial addition.
eric

 

[Staple1600]

Re

Même idée (ou presque) qu'eriiiic (donc trop tard)
Mais comme je l'avais pondu en VBA, je poste

Sub Numeriques()
Range("K1") = 1
Range("K1").Copy
Application.ScreenUpdating = False
Range("A1:J20").PasteSpecial Paste:=xlPasteAll, Operation:=xlMultiply, SkipBlanks:=True
Range("A1:J20").Replace What:="0", Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows
Application.CutCopyMode = False
Range("K1") = ""
End Sub

 

[Daniel Desch]

Bonsoir,


Merci beaucoup pour vos réponses,

J'ai essayé la méthode de Dranreb, cela fonctionne relativement vite même sur un tableau de 30000 lignes
Après j'ai autre question concernant la diffusion de cette macro. Comment faire pour diffuser cette macro à un grand nombre d'utilisateurs ?


J'ai essayé aussi la méthode d'eriiic, plus rapide, et qui a l'avantage de pouvoir se faire sans macro
Les deux fonctionnent.


Il me reste quand même un petit souci présente sur les 2 méthodes. Sur une colonne qui contient des EAN donc sur 13 caractères, ces manipulations génèrent sur cette colonne des nombres en format exponentiel au lieu de garder les 13 chiffres. Il faut ensuite à nouveau convertir cette colonne en nombre pour obtenir le résultat escompté.


Cordialement

 

[Staple1600]

Re

Donc du coup, le code du message précédent prend un vieux coup de bambou
Celui-là est mieux comme son nom l'indique

Sub Eriiiic_s_Idea_is_Better_than_mine()
Range("L1").Copy: Range("A1:J20").PasteSpecial xlPasteAll, xlAdd
End Sub

PS: Mais j'encourage vivement de suivre la méthode d'eriiiic manuellement (sans passer par les macros)
Surtout si cette manip doit être faite par des utilisateurs peu habitués au VBA.

 

[Staple1600]

Re

Daniel Desh
Et moi? Je pue de la gue.le?
Ou tu m'as juste zappé?

 

[Daniel Desch]

Bonsoir,

Euh non Staple mais tu m'as envoyé ton message quand j'étais en train de rédiger le mien

Mais j'ai bien compris que ta méthode ressemblait à celle d'Eriiic
Je viens d'essayer ton code et il donne les mêmes résultats

Merci beaucoup

 

[eriiic]

Là tu n'as plus le choix.
Il va falloir boucler sur chaque cellule pour éviter les numériques à 13 chiffres.

Un pot pourri donc à tester :

Sub test()
    Dim pl As Range, pl2 As Range, c As Range
    Set pl = UsedRange.SpecialCells(xlCellTypeConstants)
    If pl Is Nothing Then Exit Sub
    Set pl2 = Cells.SpecialCells(xlCellTypeBlanks)(1)    ' pour éviter le if pl2 is nothing then else
    For Each c In pl
        If IsNumeric(c) Then
            If Len(c) <> 13 Then Set pl2 = Union(pl2, c)
        End If
    Next c
    pl2.Value = pl2.Value
End Sub

eric

 

[Dranreb]

eriiiic, je doute que ça puisse marcher écrit comme ça. Outre qu'on affecte sa propre valeur à une plage morcelée, ce que je n'ai jamais vu marcher, l'essentiel me semble avoir été oublié.
Par ailleurs je suppose que ces numéros EAN ne sont que dans une certaine colonne.
Alors le mieux serait de limiter les colonnes à traiter.
Une version qui permet de sélectionner les colonnes entières concernées :

Option Explicit
Sub ConvTxtNum()
   Dim Rng As Range, Zon As Range, T(), L&, C&, Chgé As Boolean
   Set Rng = Intersect(ActiveSheet.UsedRange, Selection)
   If Rng Is Nothing Then Exit Sub
   Set Rng = Rng.SpecialCells(xlCellTypeConstants, 2)
   If Rng Is Nothing Then Exit Sub
   For Each Zon In Rng.Areas
      If Zon.Count > 1 Then
         Chgé = False
         T = Zon.Value
         For L = 1 To UBound(T, 1): For C = 1 To UBound(T, 2)
            If IsNumeric(T(L, C)) Then T(L, C) = CDbl(T(L, C)): Chgé = True
            Next C, L
         If Chgé Then Zon.Value = T
      ElseIf IsNumeric(Zon.Value) Then
         Zon.Value = CDbl(Zon.Value)
         End If
      Next Zon
   End Sub

On pourrait aussi spécifier en paramètre la plage à traiter.

 

[Dranreb]

Mais j'y pense tout à coup, ce n'est que l'affichage qui est comme ça avec E+12 au bout., mais on les veut bien aussi en numérique, non ?
Alors il suffit de mettre Intersect(ActiveSheet.[X2:X1000000], ActiveSheet.UsedRange).NumberFormat = "0000000000000"
(à supposer qu'il soit dans la colonne X à partir de la ligne 2, bien sûr. On est toujours obligé de supposer un tas de choses quand il n'y a pas de classeur joint)

 

[eriiic]

Ah oui, j'avais oublié. Une boucle à ajouter sur les areas :

Sub test()
    Dim pl As Range, pl2 As Range, c As Range
    Set pl = UsedRange.SpecialCells(xlCellTypeConstants)
    If pl Is Nothing Then Exit Sub
    Set pl2 = Cells.SpecialCells(xlCellTypeBlanks)(1)    ' pour éviter le if pl2 is nothing
    For Each c In pl
        If IsNumeric(c) Then
            If Len(c) <> 13 Then Set pl2 = Union(pl2, c)
        End If
    Next c
    For Each pl In pl2.Areas
        pl.Value = pl.Value
    Next pl
End Sub

 

[Dranreb]

Oui et je ne vois toujours pas où tu convertis les textes en nombres ?

 

[Daniel Desch]

Bonjour,

Merci à tous pour votre aide.

J'ai fait un test de transfert ce matin, ce n'est pas parce que la colonne EAN est en notation exponentielle qu'elle ne se transfère pas correctement sur l'AS400 donc je vais laisser comme ça.


Je vais rester sur la proposition d'Eriiic (copie spéciale avec ajout sur l'ensemble des cellules du tableau) car elle a le mérite de ne pas faire appel à une macro.
Car je n'ai pas encore compris comment diffuser une macro à des utilisateurs sans envoyer un tableau Excel prédéfini contenant la macro en question ce qui les oblige ensuite d'utiliser ce tableau là. Cela présente des avantages mais aussi des inconvénients

Cordialement

 

[eriiic]

Bonjour,

Oui et je ne vois toujours pas où tu convertis les textes en nombres ?

Bah écoute je ne comprend plus.
Hier soir ils étaient bien convertis, ce matin non (?!?) C'est pour ça que j'avais laissé tomber le collage spécial addition
Plus qu'à le remettre :

    '...
    For Each pl In pl2.Areas
        'pl.Value = pl.Value
        Cells.SpecialCells(xlCellTypeBlanks)(1).Copy
        pl.PasteSpecial xlPasteAll, xlAdd
    Next pl

Car je n'ai pas encore compris comment diffuser une macro à des utilisateurs sans envoyer un tableau Excel prédéfini contenant la macro en question ce qui les oblige ensuite d'utiliser ce tableau là.

Tu peux très bien livrer un .xlsm.
Tu mets un raccourcis clavier à ta macro et elle s'appliquera à la feuille active du classeur actif, sans besoin de lui mettre la macro.
Tu peux même automatiser et améliorer un peu plus si tu connais la forme du nom des classeurs et/ou la forme des noms des feuilles.
eric