Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy rows multiple times changing value of a cell in one column each time
Hi
Thanks for your help in advance. I will try and explain the probelm with an example. Sheet 1: "Raw Data" Data1 Data2 Data3 Data4 Data5 Data6 Data7 Data8 Some 1 51 41 75 50 A6 HGH NUM Some 2 58 70 62 72 B6 HGH NUM Some 3 61 24 74 43 A4 HGH NUM Sheet 2" "Structure" Structure6 Structure5 Structure4 Structure3 Structure2 Structure1 A6 A5 A4 A3 A2 A1 B6 B5 B4 B3 B2 B1 Column called Data 6 in Sheet 1 corresponds to the data in Seeet 2. Lets call it the LEVEL in the structure. My task is to copy all the data in each row of Sheet 1 for all LEVELs "Below" the relevant structure. For instance, in the first row, the Level is "A6", then in a new sheet I would like the entire row copied (many times) and the only cell that needs to be changed in each copy is A6. So the first row in this example will be repeated 6 times (once as A6, and in the other "copied rows", A6 will be replaced by A5, A4, A3, A2, A1 in the other 5 rows.) Just to exapnd on the point, in case of ROW 3, where the level is A4, the other 3 rows will have A3, A2, A1. The final output will look like this. Data1 Data2 Data3 Data4 Data5 Data6 Data7 Data8 Some 1 51 41 75 50 A6 HGH NUM Some 1 51 41 75 50 A5 HGH NUM Some 1 51 41 75 50 A4 HGH NUM Some 1 51 41 75 50 A3 HGH NUM Some 1 51 41 75 50 A2 HGH NUM Some 1 51 41 75 50 A1 HGH NUM Some 3 61 24 74 43 A4 HGH NUM Some 3 61 24 74 43 A3 HGH NUM Some 3 61 24 74 43 A2 HGH NUM Some 3 61 24 74 43 A1 HGH NUM (((I used numbers (A6) here but the real data is all TEXT. SO the macro needs to look for the value in "data 6" column in the Structure sheet and create copies of that entire row with all values in the structure to the "right" of the "matching" value in structure sheet. )))) I guess I repeated myself twice or thrice here. Again thanks for any help / guidance you can provide. Best Regards Prasad |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy rows multiple times changing value of a cell in one column ea
Not sure why my previous posting is not shwing up. here is the code again
Sub move_data() Const FirstRow = 2 Const FirstCol = "A" Level = InputBox("enter Column Number of Level : ") StructRowCount = 1 With Sheets("Raw Data") LastRow = .Cells(Rows.Count, FirstCol).End(xlUp).Row For RawRowCount = FirstRow To LastRow For LevelCount = 0 To (Level - 1) Sheets("Structure"). _ Range("A" & StructRowCount). _ Offset(0, LevelCount).Value = _ .Range(FirstCol & RawRowCount). _ Offset(0, Level - LevelCount - 1). _ Value Next LevelCount StructRowCount = StructRowCount + 1 Next RawRowCount End With End Sub "EE" wrote: Hi Thanks for your help in advance. I will try and explain the probelm with an example. Sheet 1: "Raw Data" Data1 Data2 Data3 Data4 Data5 Data6 Data7 Data8 Some 1 51 41 75 50 A6 HGH NUM Some 2 58 70 62 72 B6 HGH NUM Some 3 61 24 74 43 A4 HGH NUM Sheet 2" "Structure" Structure6 Structure5 Structure4 Structure3 Structure2 Structure1 A6 A5 A4 A3 A2 A1 B6 B5 B4 B3 B2 B1 Column called Data 6 in Sheet 1 corresponds to the data in Seeet 2. Lets call it the LEVEL in the structure. My task is to copy all the data in each row of Sheet 1 for all LEVELs "Below" the relevant structure. For instance, in the first row, the Level is "A6", then in a new sheet I would like the entire row copied (many times) and the only cell that needs to be changed in each copy is A6. So the first row in this example will be repeated 6 times (once as A6, and in the other "copied rows", A6 will be replaced by A5, A4, A3, A2, A1 in the other 5 rows.) Just to exapnd on the point, in case of ROW 3, where the level is A4, the other 3 rows will have A3, A2, A1. The final output will look like this. Data1 Data2 Data3 Data4 Data5 Data6 Data7 Data8 Some 1 51 41 75 50 A6 HGH NUM Some 1 51 41 75 50 A5 HGH NUM Some 1 51 41 75 50 A4 HGH NUM Some 1 51 41 75 50 A3 HGH NUM Some 1 51 41 75 50 A2 HGH NUM Some 1 51 41 75 50 A1 HGH NUM Some 3 61 24 74 43 A4 HGH NUM Some 3 61 24 74 43 A3 HGH NUM Some 3 61 24 74 43 A2 HGH NUM Some 3 61 24 74 43 A1 HGH NUM (((I used numbers (A6) here but the real data is all TEXT. SO the macro needs to look for the value in "data 6" column in the Structure sheet and create copies of that entire row with all values in the structure to the "right" of the "matching" value in structure sheet. )))) I guess I repeated myself twice or thrice here. Again thanks for any help / guidance you can provide. Best Regards Prasad |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Changing data in column for multiple rows | Excel Discussion (Misc queries) | |||
Copy Rows Multiple times "changing" values in one column | Excel Programming | |||
Copy rows to multiple sheets based off column value | Excel Programming | |||
how do i copy the same cell multiple times in excel. | Excel Discussion (Misc queries) | |||
Way To Copy Formula In Column Without Changing All Cell Numbers | Excel Discussion (Misc queries) |