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.

 

[Peter_SSs]

Why the "On Error GoTo 0"? For another iteration? Why?

Yes, as has been indicated, it wasn't related to the extra line, but just ensuring no error problem eventuated if the column didn't have any constants or didn't have any blanks. The On Error lines should really have been in my previous code too.

 

[drsarao]

Peter,
As I understand it, error handling is done by "On Error Resume Next".
"On Error GoTo 0" seems to be just housekeeping at the end.

As per MSDN:

On Error GoTo 0

On Error GoTo 0 disables error handling in the current procedure. It doesn't specify line 0 as the start of the error-handling code, even if the procedure contains a line numbered 0.

Without an On Error GoTo 0 statement, an error handler is automatically disabled when a procedure is exited.

So "On Error GoTo 0" here seems to be just cleaning up code and is redundant as the last line of the procedure. (Infact, I ran your code with "On Error GoTo 0" remmed out and it ran perfectly).
I think you wrote that line to be more methodical and consistent.

Taurean,
I didn't understand this:

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

The only other use of "On Error GoTo 0" (besides cleaning up) seems to be to shut down Error handling in a called procedure and process the error once the called procedure ends in the calling Sub. As given here http://www.herongyang.com/VBScript/Error-Handling-On-Error-GoTo.html

I would of course like to know/understand more.
Thanks.

 

[Peter_SSs]

(Infact, I ran your code with "On Error GoTo 0" remmed out and it ran perfectly).
I think you wrote that line to be more methodical and consistent.

In general, if On Error Goto 0 is omitted after an On Eror Resume Next, all code will run perfectly - in that it will not error (and in many cases still produce the desired result).

As a habit I try to always add the On Error Goto 0 as soon as the code is past where I know I want to deliberately skip errors. So, yes, methodical and consistent but it's a bit more than that ..

1. I want to know when my code errors because of something I have forgotten or just didn't think or know about or typed incorrectly.


2. Although this particular On Error Goto 0 is very near the end of the code, there is no guarantee that further code will not be added to the procedure later and, if it is code I am using, I don't want to have to hope that I remember to add this line at that later point.

For example, suppose in the example we have been dealing with here, we start with data occupying 6 columns and I decide from now on I also want to alter the fifth last heading each time the code is run. So I add the blue line of code below (I know there is a better way but I'm trying to demonstrate a point ), and while I am typing I accidentally omit the comma I meant to type immediately before the -3 in this line.

    End With
    Cells(3, lc).Offset(-3).Value = "Important!"
End Sub

Now, if I didn't have the On Error Goto 0 in the line above this code, my code would not error, but I also may not realise that my code has actually errored.


3. Always adding the line saves having to decide every time whether it may be needed or not.

 

[drsarao]

Crystal clear!
Thanks.

 

[taurean]

Peter,
As I understand it, error handling is done by "On Error Resume Next".
"On Error GoTo 0" seems to be just housekeeping at the end.

As per MSDN:
So "On Error GoTo 0" here seems to be just cleaning up code and is redundant as the last line of the procedure. (Infact, I ran your code with "On Error GoTo 0" remmed out and it ran perfectly).
I think you wrote that line to be more methodical and consistent.

Taurean,
I didn't understand this:The only other use of "On Error GoTo 0" (besides cleaning up) seems to be to shut down Error handling in a called procedure and process the error once the called procedure ends in the calling Sub. As given here http://www.herongyang.com/VBScript/Error-Handling-On-Error-GoTo.html

I would of course like to know/understand more.
Thanks.

While you are writing a code, especially when it comes to error handling part, the decision depends on: how the error will impact on the result.

So in the above case, the code will raise an error when there are specified special cells are not there. So code will throw an error. But will it affect the outcome? No. So we can ignore error raised by both statements very safely. But in rest of the cases, we should know if the procedure raises an error.

So essentially, it brackets / separates, the potential part which may generate error from the rest (where we don't expect an error). So we tell VBA to get back to its default:
Raise an error message when there is!