Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Separate data into 2 output ranges
The following code copies any values within range CL145:CZ145, in which each digit is one increment higher or lower than the digit before and after it then paste the result to row 162, starting in column CL, all other remaining values in the range CL145:CZ145, copy and paste to row 162, starting column CW. Each output within the range have commas separating the each digit. Example of values that meet this condition: 1,2 or 2,3, or 6,7 or 21,22 Examples of values that do no meet this condition 3,5 or 7,10 or 11,14 sample data1: CL145=2,3.....CM145=4,7......CN145=6,7 Result CL162=2,3...CM162=6,7 CW162=4,7 How do I update the code to add all the others rows in the source range CL145:CZ160 into the CL and CW columns as listed below. Output ranges are CL162:CU177 and CW162:DF177. I need to update this function to work the same as in does in source row 145 to output 162 for source rows 145-148 to output 162-165 For the 3 digits values with 2 commas using the same rules in 149-152 output 166-169 For the 4 digits values with 3 commas using the same rules in 153-156 output 170-173 For the 5 digits values with 4 commas using the same rules in 157-160 output 174-177 sample data2: CL150=1,2,3……CM150=1,15,23……CN150=2,7,19 Result CL167=1,2,3…….CW167=1,15,23…...CX167=2,7,19 Code: -------------------- Sub terst() Dim a, r As Range, i As Integer, ii As Integer, x On Error Resume Next For Each r In Range("cl145:cz145") If InStr(r, ",") 0 Then a = Split(r, ",") x = a(1) - a(0) Select Case x Case 1 Range("ck" & 162).Offset(, 1 + i).Value = r i = i + 1 Case Else Range("cv" & 162).Offset(, 1 + ii).Value = r ii = ii + 1 End Select End If Next End Sub -------------------- -- dkenebre ------------------------------------------------------------------------ dkenebre's Profile: http://www.excelforum.com/member.php...nfo&userid=760 View this thread: http://www.excelforum.com/showthread...hreadid=395590 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Named ranges prints on separate pages | Excel Discussion (Misc queries) | |||
Data validation: concatenate two separate ranges in the List? | Excel Discussion (Misc queries) | |||
Save 2 separate data imports in separate worksheets on the same ex | Excel Worksheet Functions | |||
Different passwords to separate ranges in excel 2000 | Excel Programming | |||
how to select two separate ranges into range object | Excel Programming |