VBA to Normalize Data: Loop through Row, Insert Column, Cut/Paste, Insert formula in new Column

[milesthedog]

Hello,

I am new to Excel Macros and I need the VBA to:

1) Loop through a single Row

2) Insert Column before "not empty" cells

3) Cut the content from the "not empty" cell

4) Paste cut content into newly inserted column [from step 2]

5) several cells down within newly inserted column, paste an if-statement for the entire column.

6) if-statement: =if([first column to the right of inserted column]+[second column to the right of inserted column]=1, "Agree", (if([third column to the right of inserted column]+[fourth column to the right of inserted column]=1, "Disagree", "")))

7)hide the 4 columns to the right of the newly inserted column

 

[Hui]

Milesthedog

Firstly, Welcome to the Chandoo.org Forums

Can you post a sample file so that we can help you more specifically

 

[Smallman]

Hi MTD

Without a workbook it is very difficult to get a feel for what your data looks like. I have created some sample data but this will unlikely hit the mark. it is a good idea to post a workbook with questions as it adds context not just for you but for all.

Here is my code and the file to show how it works
.

Option Explicit
 
Sub Manipulate()
Dim lc As Long
Dim lr As Long
lr = Range("A" & Rows.Count).End(xlUp).Row
 
lc = Range("a2").End(xlToRight).Column
Columns(lc).EntireColumn.Insert
Columns(lc - 1).Cut Cells(1, lc)
 
Range(Cells(3, lc - 1), Cells(lr, lc - 1)).FormulaR1C1 = "=IF(RC[1]+RC[2]=1,""Agree"",""Disagree"")"
Range(Cells(1, lc + 1), Cells(lc + 4)).EntireColumn.Hidden = True
End Sub

Take care

Smallman