Insert Macro

[j.breindel2]

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.

 

[drsarao]

Welcome to MrExcel.
Try this:

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]

It works. Thank you! I love this site.

 

[drsarao]

You are welcome!

 

[Peter_SSs]

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]

Ingenious. Thanks Peter.

 

[wigi]

FillRight is an option too, for AutoFill:

Cells(3, lc).Resize(, 2).FillRight

 

[Peter_SSs]

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]

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]

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:

[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".