# Insert Macro

#### j.breindel2

##### New Member
Hi all,
this is my first post here. Ive been trying to write the macro for this for hours but can't seem to get it to work. the task is
The macro should do the following in the columns it creates:
1. Insert the test name (Test 6, Test 7, and so on) into the appropriate cell (ie. if Test 6 is the last column, the inserted column should be Test 7). This is in row 3.
2. Insert zeros into all cells in the column, so you can put the actual grades in later.
3. Repeat all equations for a cell if they are done for a cell adjacent to it. (if the cell adjacent has taken the high grade from the column, then the macro should do this for the cell.) (ie. apply all the equations from the cell next to it to apply to this column as well)
4. The macro should be repeatable, meaning that I should be able to run it multiple times and have it keep adding additional test columns.
Thank you.

### Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

#### drsarao

##### Well-known Member
Welcome to MrExcel.
Try this:
Code:
``````Sub ColInsert()
With ActiveSheet
lr = .Cells(Rows.Count, "A").End(xlUp).Row 'last row
lc = .Cells(3, Columns.Count).End(xlToLeft).Column 'lastcol
.Cells(3, lc).AutoFill .Range(.Cells(3, lc), .Cells(3, lc + 1)) 'autofill Test xx series
For r = 4 To lr 'go thru all rows
If .Cells(r, lc).HasFormula Then
.Cells(r, lc).AutoFill .Range(.Cells(r, lc), .Cells(r, lc + 1)) 'autofill formula
Else
.Cells(r, lc + 1) = 0 'put 0
End If
Next r
End With
End Sub``````

#### j.breindel2

##### New Member
It works. Thank you! I love this site.

You are welcome!

#### Peter_SSs

##### MrExcel MVP, Moderator
Welcome to the MrExcel board!

Here's a non-looping method you may wish to try as well.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> ColInsert2()<br>    <SPAN style="color:#00007F">Dim</SPAN> lr <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, lc <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <br>    lr = Cells(Rows.Count, "A").End(xlUp).Row<br>    lc = Cells(3, Columns.Count).End(xlToLeft).Column<br>    Cells(3, lc).AutoFill Cells(3, lc).Resize(, 2)<br>    <SPAN style="color:#00007F">With</SPAN> Cells(4, lc).Resize(lr - 3)<br>        .Copy Destination:=.Offset(, 1)<br>        .Offset(, 1).SpecialCells(xlConstants).Value = 0<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

#### drsarao

##### Well-known Member
Ingenious. Thanks Peter.

#### wigi

##### Well-known Member
FillRight is an option too, for AutoFill:

Code:
``Cells(3, lc).Resize(, 2).FillRight``

#### Peter_SSs

##### MrExcel MVP, Moderator
Just looking at it again, it may need a little more though we don't really know what the columns contain. It could be this:

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> ColInsert2()<br>    <SPAN style="color:#00007F">Dim</SPAN> lr <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, lc <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <br>    lr = Cells(Rows.Count, "A").End(xlUp).Row<br>    lc = Cells(3, Columns.Count).End(xlToLeft).Column<br>    Cells(3, lc).AutoFill Cells(3, lc).Resize(, 2)<br>    <SPAN style="color:#00007F">With</SPAN> Cells(4, lc).Resize(lr - 3)<br>        .Copy Destination:=.Offset(, 1)<br>        <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">Resume</SPAN> <SPAN style="color:#00007F">Next</SPAN><br>        .Offset(, 1).SpecialCells(xlConstants).Value = 0<br>        .Offset(, 1).SpecialCells(xlBlanks).Value = 0<br>        <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> 0<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

#### drsarao

##### Well-known Member
Yes , it takes care of blanks also.
Why the "On Error GoTo 0"? For another iteration? Why?
Wigi, Thanks. FillRight was unknown to me. Of course now I know FillDown, Left and UP also.

#### taurean

##### Well-known Member
Yes , it takes care of blanks also.
Why the "On Error GoTo 0"? For another iteration? Why?
Wigi, Thanks. FillRight was unknown to me. Of course now I know FillDown, Left and UP also.
The coding will throw an error if the case is not applicable:
Code:
``````[FONT=Courier]        .Offset(, 1).SpecialCells(xlConstants).Value = 0
.Offset(, 1).SpecialCells(xlBlanks).Value = 0[/FONT]``````
"On Error Resume Next" tells VBA to ignore errors raised by above statements. But once we are done with it, we can get back to default system which raises an error if there is. This is reset by "On Error Goto 0".

Replies
0
Views
146
Replies
1
Views
287
Replies
0
Views
161
Replies
4
Views
310
Replies
6
Views
190

1,195,669
Messages
6,011,059
Members
441,580
Latest member
BornholmerBjarne

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back