Getting Monthly Data using Split()
 

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.

Getting Monthly Data using Split()
 

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" skrev i en meddelelse
...
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.

Getting Monthly Data using Split()
 

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