Excel Macros Re write help. 2

[MSUser]

I would like to inset a column in an excel workbook with a long list of macros. However, when I do the insertion it disables the macros because they still refer to the unshifted cells. Is there an easy way to re write the macros in this situation?

 

[ETID]

Have you tried the "edit replace" from the vb editor...then choose search entire project. then find (old cell ref) and replace (new cell ref).....tedious, but only way I know.

 

[DaleWatson123321]

MBUser,

This is a "classical" example of a situation where you MUST begin to use (and preferably ALWAYS use) "RANGE NAMES".

Internally, Excel maintains a "link" between "Range Names" and the cell(s) to which they are assigned. Therefore, whenever rows or columns are inserted, this does NOT affect any VBA code which uses Ranges Names.

The EASY way to Assign Range Names:

1) Highlight the cell or range of cells.

2) Use <Control> <F3> - i.e. hold down the <Control> key and hit the <F3> key.

3) Type the name you wish to use.

4) Hit <Enter>

Reminder: Do NOT use any names which &quot;conflict&quot; with cell addresses or numbers. For example, do NOT use &quot;A1&quot; - instead use &quot;_A1&quot;, do NOT use &quot;1&quot; - instead use &quot;_1&quot;.

If you have any misunderstanding over this, please enquire either here, or email me directly.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca