Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Converting daily data into weekly, monthly and yearly data | Excel Discussion (Misc queries) | |||
Convert monthly data into quarterly data? | Excel Discussion (Misc queries) | |||
How do I get monthly data in the data area to remain in order | Charts and Charting in Excel | |||
How do I set up monthly random work schedule for 60 hours monthly | Excel Discussion (Misc queries) | |||
Mortgage template comparing interest pd, monthly, bi-monthly, ext. | Excel Discussion (Misc queries) |