Why doesn't your macro select the right cell?

If your macro is supposed to select cells as it runs, but selects the wrong ones or even causes an error, one of the following items may be the cause:

• The macro was recorded using one combination of settings for absolute or relative column or row references, but the actual conditions under which the macro is run require a different combination.

You can either rerecord the macro by using a combination that better suits the situa tion under which the macro is run, or you can edit the macro code in the Visual Basic Editor and then change it manually.

If you change the reference settings manually, the following table shows the different values that should be used when changing the type of column and row references:





The value for the Column argu ment is the name of the field in quotes.

The value for the Column argument is a positive number, indicating the number of the column.


The value for the Row argu ment is a positive number, indicating the number of the row, and the RowRelative argument is False.

The value for the Row argument may be either a negative or positive num ber. The RowRelative argument is either True or is missing (the default value is True).

Note The columns for the row number and the Indicators field (if showing) are both counted when using relative column references. The first "normal" column is actually column 3 when manually editing column references in a macro.

• The macro assumes that a particular cell or item has been selected before the macro is run.

You could always try to remember that the proper cell is selected before running the macro, but rerecording the macro (or editing it in the Visual Basic Editor) to select the proper cell before it does anything else solves the problem and also makes the macro more robust.

• The column (or row, if it is for a subtask) containing the cell to select may have been hidden or the row may have been deleted.

Most of the solutions to this problem involve writing complicated Visual Basic code, so the best solution, until you're more comfortable working with the Visual Basic Editor to edit your macros, is to simply make sure that the proper conditions are met before running your macro. Using column references can also help you spot this problem early on because your macro will cause an error on the line that refers to the missing column and make it easier for you to guess at what the problem is.

Project Management Made Easy

Project Management Made Easy

What you need to know about… Project Management Made Easy! Project management consists of more than just a large building project and can encompass small projects as well. No matter what the size of your project, you need to have some sort of project management. How you manage your project has everything to do with its outcome.

Get My Free Ebook

Post a comment