Getting Monthly Data using Split()

[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.

 

[Per Jessen]

Hi

Try this:

Option Base 1
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)
For x = 1 To TestPos
On Error Resume Next
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 + MyOffset).Value = myValue1
Cell.Offset(0, x + MyOffset + 1).Value = myValue2
MyOffset = MyOffset + 1
Next
MyOffset = 0
Next
End Sub

Regards,

Per

 

[wutzke]

THanks. That works to a point...

with

Feb '08 0.0000 Nov '07 0.0000 Aug '07 0.0000 May '07 1.0000 Yr 1 avg :
0.5000
Jan '08 0.0000 Oct '07 0.0000 Jul '07 2.0000 Apr '07 0.0000 total :
6.0000
Dec '07 1.0000 Sep '07 0.0000 Jun '07 0.0000 Mar '07 2.0000 Mn 1 stk :
8.00


returned

Nov '07 0 Aug '07 0 May '07 1
Oct '07 0 Jul '07 2 Apr '07 0 total : 6
Sep '07 0 Jun '07 0 Mar '07 2


Still not getting the 1st column (Feb' 08, Jan '08 & Dec '08)
so I changed it

Option Base 1
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 - 1
On Error Resume Next
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 + MyOffset).Value = myValue1
Cell.Offset(0, x + MyOffset + 1).Value = myValue2
MyOffset = MyOffset + 1
Next
MyOffset = 0
Next
End Sub

thanks again