vendredi 14 juillet 2017

Copy paste Cell inside Table under certain conditions

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. Using this picture I will help explain what I'm trying to do.

Example 1

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.

So 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" and take the cell right from it (under column K) and copy paste it into column L on the same row as the cell "Basic" 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.

Desired Result

Thanks in advanced for your help.

My current code is (just to insert Column L and name it Sub Category):

Sub CreateNewColumn()
'
' CreateNewColumn Macro
' Creates a new column between Answer Title and Form Components Comments called "Sub Category"
'

'
    Columns("L:L").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("L4").Select
    ActiveCell.FormulaR1C1 = "Sub Catergory"


End Sub

Aucun commentaire:

Enregistrer un commentaire