W
wutzke
I have a text file I'm imorting into Excel with each line of text in a
cell. Something like this:
Feb '08 0.0000 Nov '07 0.0000 Aug '07 0.0000 May '07 0.0000 Yr 1 avg :
0.4167
Jan '08 1.0000 Oct '07 0.0000 Jul '07 1.0000 Apr '07 0.0000 total :
5.0000
Dec '07 2.0000 Sep '07 0.0000 Jun '07 1.0000 Mar '07 0.0000 Mn 1 stk :
21.60
Feb '08 0.0000 Dec '07 0.0000 Oct '07 0.0000 Aug '07 0.0000 Yr 1 avg :
0.1429
Jan '08 0.0000 Nov '07 0.0000 Sep '07 1.0000 total : 1.0000
Using this code I can split the months from the numbers.
Sub GetSalesData()
Dim x As Long
Dim Cell As Range
Dim Parts() As String
For Each Cell In Selection
Parts = Split(Cell.Value, ".0000")
TestPos = UBound(Parts) + 1
For x = 1 To TestPos
myValue1 = Left(Parts(UBound(Parts) + x - TestPos), 8)
myValue2 = Right(Parts(UBound(Parts) + x - TestPos),
Len(Parts(UBound(Parts) + x - TestPos)) - 8)
Cell.Offset(0, x + 1).Value = myValue1
Cell.Offset(0, x + 2).Value = myValue2
Next
Next
End Sub
Works fine expect on the second and last cell, plus I'm not getting
the values put into to (offset) cell next to the Month.
cell. Something like this:
Feb '08 0.0000 Nov '07 0.0000 Aug '07 0.0000 May '07 0.0000 Yr 1 avg :
0.4167
Jan '08 1.0000 Oct '07 0.0000 Jul '07 1.0000 Apr '07 0.0000 total :
5.0000
Dec '07 2.0000 Sep '07 0.0000 Jun '07 1.0000 Mar '07 0.0000 Mn 1 stk :
21.60
Feb '08 0.0000 Dec '07 0.0000 Oct '07 0.0000 Aug '07 0.0000 Yr 1 avg :
0.1429
Jan '08 0.0000 Nov '07 0.0000 Sep '07 1.0000 total : 1.0000
Using this code I can split the months from the numbers.
Sub GetSalesData()
Dim x As Long
Dim Cell As Range
Dim Parts() As String
For Each Cell In Selection
Parts = Split(Cell.Value, ".0000")
TestPos = UBound(Parts) + 1
For x = 1 To TestPos
myValue1 = Left(Parts(UBound(Parts) + x - TestPos), 8)
myValue2 = Right(Parts(UBound(Parts) + x - TestPos),
Len(Parts(UBound(Parts) + x - TestPos)) - 8)
Cell.Offset(0, x + 1).Value = myValue1
Cell.Offset(0, x + 2).Value = myValue2
Next
Next
End Sub
Works fine expect on the second and last cell, plus I'm not getting
the values put into to (offset) cell next to the Month.