If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
how to combine each record into a range?
how to combine each record into a range?
Table1 Day Value 1 0 2 0 3 0 4 1 5 1 6 1 7 0 8 0 9 0 10 0 11 1 12 1 13 0 14 1 15 0 16 0 17 1 18 1 19 0 20 1 .. . .. . .. . 1000 1 how to covert the above table into: DayStart DayEnd Value 1 3 0 4 6 1 7 10 0 11 12 1 13 13 0 14 14 1 15 16 0 17 18 1 19 19 0 .... thanks, pemt |
#2
|
|||
|
|||
how to combine each record into a range?
Try some code like the following.
Sub AAA() Dim R As Range Dim Dest As Range Dim CurrVal As Long Dim Upper As Long Dim Lower As Long Dim LastRow As Long Set Dest = Sheets(2).Range("A1") ' CHANGE Set R = Worksheets(1).Range("A1") ' CHANGE CurrVal = R(1, 2).Value Upper = R(1, 1).Value Lower = R(1, 1).Value For Each R In Worksheets(1).Range("A1:A26") ' CHANGE If R(1, 2).Value CurrVal Then Dest(1, 1).Value = Lower Dest(1, 2).Value = Upper Dest(1, 3).Value = CurrVal Lower = R(1, 1).Value CurrVal = R(1, 2).Value Set Dest = Dest(2, 1) End If Upper = R(1, 1).Value Next R End Sub Change the values marked with to the appropriate values. R should be set to the first cell that is to be examined. The For Each loop should be set to the entire range that will be examined. Dest should be set to the destination of the new records. Cordially, Chip Pearson Microsoft Most Valuable Professional, Excel, 1998 - 2010 Pearson Software Consulting, LLC www.cpearson.com On Tue, 23 Mar 2010 11:13:01 -0700, pemt wrote: how to combine each record into a range? Table1 Day Value 1 0 2 0 3 0 4 1 5 1 6 1 7 0 8 0 9 0 10 0 11 1 12 1 13 0 14 1 15 0 16 0 17 1 18 1 19 0 20 1 . . . . . . 1000 1 how to covert the above table into: DayStart DayEnd Value 1 3 0 4 6 1 7 10 0 11 12 1 13 13 0 14 14 1 15 16 0 17 18 1 19 19 0 ... thanks, pemt |
#3
|
|||
|
|||
how to combine each record into a range?
Hi Chip,
Thanks a lot! pemt "Chip Pearson" wrote: Try some code like the following. Sub AAA() Dim R As Range Dim Dest As Range Dim CurrVal As Long Dim Upper As Long Dim Lower As Long Dim LastRow As Long Set Dest = Sheets(2).Range("A1") ' CHANGE Set R = Worksheets(1).Range("A1") ' CHANGE CurrVal = R(1, 2).Value Upper = R(1, 1).Value Lower = R(1, 1).Value For Each R In Worksheets(1).Range("A1:A26") ' CHANGE If R(1, 2).Value CurrVal Then Dest(1, 1).Value = Lower Dest(1, 2).Value = Upper Dest(1, 3).Value = CurrVal Lower = R(1, 1).Value CurrVal = R(1, 2).Value Set Dest = Dest(2, 1) End If Upper = R(1, 1).Value Next R End Sub Change the values marked with to the appropriate values. R should be set to the first cell that is to be examined. The For Each loop should be set to the entire range that will be examined. Dest should be set to the destination of the new records. Cordially, Chip Pearson Microsoft Most Valuable Professional, Excel, 1998 - 2010 Pearson Software Consulting, LLC www.cpearson.com On Tue, 23 Mar 2010 11:13:01 -0700, pemt wrote: how to combine each record into a range? Table1 Day Value 1 0 2 0 3 0 4 1 5 1 6 1 7 0 8 0 9 0 10 0 11 1 12 1 13 0 14 1 15 0 16 0 17 1 18 1 19 0 20 1 . . . . . . 1000 1 how to covert the above table into: DayStart DayEnd Value 1 3 0 4 6 1 7 10 0 11 12 1 13 13 0 14 14 1 15 16 0 17 18 1 19 19 0 ... thanks, pemt . |
Thread Tools | |
Display Modes | |
|
|