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 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97
|
Sub TesterDecomposerLaChaine()
Dim J As Long, DerniereLigne As Long
Dim ShATraiter As Worksheet
Dim MonSplitValeurs As Variant
Dim MaChaineValeurs As String
Dim ValeurEnCours As Integer, ColonneEnCours As Integer
Set ShATraiter = ActiveSheet
With ShATraiter
DerniereLigne = .Cells(.Rows.Count, 1).End(xlUp).Row
For J = 3 To DerniereLigne
.Cells(J, 3) = LaChaineAlpha(.Cells(J, 1))
MaChaineValeurs = Split(.Cells(J, 1), .Cells(J, 3))(1)
MonSplitValeurs = Split(MaChaineValeurs, " ")
.Cells(J, 2) = "'" & Trim(Split(.Cells(J, 1), .Cells(J, 3))(0))
.Cells(J, 10) = CLng(MonSplitValeurs(UBound(MonSplitValeurs)))
ValeurEnCours = 0
For ColonneEnCours = 4 To 4 + UBound(MonSplitValeurs) - 1
With .Cells(J, ColonneEnCours)
.Value = ChaineATransformer(Trim(MonSplitValeurs(ValeurEnCours)))
.NumberFormat = "#,##0.00"
End With
ValeurEnCours = ValeurEnCours + 1
Next ColonneEnCours
Next J
End With
Set ShATraiter = Nothing
End Sub
Function LaChaineAlpha(ByVal ChaineATester) As String
Dim ChaineAlpha As Variant
Dim I As Integer, PremiereVirgule As Integer
Dim MaChaine As String
LaChaineAlpha = ""
PremiereVirgule = 0
For I = Len(ChaineATester) To 1 Step -1
If Mid(ChaineATester, I, 1) = "," Then PremiereVirgule = I
Next I
If PremiereVirgule = 0 Then Exit Function
ChaineAlpha = Split(Mid(ChaineATester, 1, PremiereVirgule), " ")
Debug.Print Mid(ChaineATester, 1, PremiereVirgule) & ", " & UBound(ChaineAlpha) & ", "; PremiereVirgule
MaChaine = ""
For I = 1 To UBound(ChaineAlpha) - 1
MaChaine = MaChaine & "" & ChaineAlpha(I) & " "
Next I
LaChaineAlpha = Trim(MaChaine)
End Function
Function ChaineATransformer(ByVal LaChaine As String) As Variant
Dim I As Integer
Dim ValeurChaine As String
Dim ValeurSigne As String
ValeurSigne = "Positif"
ValeurChaine = ""
For I = 1 To Len(LaChaine)
Select Case Mid(LaChaine, I, 1)
Case ","
ValeurChaine = ValeurChaine & "."
Case "-"
ValeurSigne = "Négatif"
Case Else
ValeurChaine = ValeurChaine & Mid(LaChaine, I, 1)
End Select
Next I
If ValeurChaine <> "" Then
If ValeurSigne = "Négatif" Then
ChaineATransformer = "-" & ValeurChaine
Else
ChaineATransformer = ValeurChaine
End If
End If
End Function |
Partager