VanChester
07-17-2017, 07:38 AM
Hello Everyone,
I have a table where I would like execute a code to automate copying cells into other cells inside a table. The important columns for this table are J,K,L, and M. Using this picture I will help explain what I'm trying to do.
19774
The conditions are what are underlined in the various colors and the results I would like the code to execute are the arrows and squares.
Column J = Form Component Title
Column K = Answer Title
Column L = Sub Category
Column M = Form Component Comments
Under column J, The first one underlined is "Agree - Competency" and the cell on the right from it (under column K) is "Basic". What I would like for it to do is since the cell under column K is "Basic", then the code needs to look for "Agree - Basic" (which is on column J) and take the cell on the right of it (under column K and the cell under column M) and copy paste it into column L on the same row as the cell "Basic" (which is under column K).
One issue with this is that the order under Column J is not always the same and every 41 rows the data repeats itself. For example: for the range [J1-J40] we will have "Agree - Competency" in cell J3 but on the range [J41-81] it will have "Agree - Competency" in cell J46.
The reason it repeats itself is because every 41 rows it is a new person's data for a new entry.
I have includes a Picture of the requested result should look like.
19775
My current code is (just to insert Column L and name it Sub Category):
Sub CreateNewColumn()
'
' CreateNewColumn Macro
' Creates a new column between Anser Title and Form Components Comments called "Sub Catergory"
'
'
Columns("L:L").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("L4").Select
ActiveCell.FormulaR1C1 = "Sub Category"
End Sub
I have attached the actual Excel File That I'm trying to establish the code on.
19776
Your help is greatly appreciated. Thank you
I have a table where I would like execute a code to automate copying cells into other cells inside a table. The important columns for this table are J,K,L, and M. Using this picture I will help explain what I'm trying to do.
19774
The conditions are what are underlined in the various colors and the results I would like the code to execute are the arrows and squares.
Column J = Form Component Title
Column K = Answer Title
Column L = Sub Category
Column M = Form Component Comments
Under column J, The first one underlined is "Agree - Competency" and the cell on the right from it (under column K) is "Basic". What I would like for it to do is since the cell under column K is "Basic", then the code needs to look for "Agree - Basic" (which is on column J) and take the cell on the right of it (under column K and the cell under column M) and copy paste it into column L on the same row as the cell "Basic" (which is under column K).
One issue with this is that the order under Column J is not always the same and every 41 rows the data repeats itself. For example: for the range [J1-J40] we will have "Agree - Competency" in cell J3 but on the range [J41-81] it will have "Agree - Competency" in cell J46.
The reason it repeats itself is because every 41 rows it is a new person's data for a new entry.
I have includes a Picture of the requested result should look like.
19775
My current code is (just to insert Column L and name it Sub Category):
Sub CreateNewColumn()
'
' CreateNewColumn Macro
' Creates a new column between Anser Title and Form Components Comments called "Sub Catergory"
'
'
Columns("L:L").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("L4").Select
ActiveCell.FormulaR1C1 = "Sub Category"
End Sub
I have attached the actual Excel File That I'm trying to establish the code on.
19776
Your help is greatly appreciated. Thank you