Excel row insert (Excel 2002)

Topic

New Reply

I wrote a macro with the line:
Selection.Insert Shift:=x1Down
It worked in Excel 2000 and in Excel 2003. However, the file I created was shared with 21 offices around California and a couple people have Excel 2002. The are getting a debug error with this line. Any ideas?

Reply | Quote

Replies

The constant should be XLDOWN or XLSHIFTDOWN, not X1DOWN.

Apart from that, the code should work in Excel 2002 as well as in the other versions.

Reply | Quote

the appropriate constant to use in this case is called xlShiftDown (member of the XlInsertShiftDirection constants), with a value of -4121. the xlDown constant will also work, because it has the same value, but this one is more used to indicate direction when moving from or to a certain range or expanding it.

the statement you used is equivalent to selection.insert shift:=0, since the x1down constant does not exist, unless you defined it yourself. since its value does not correspond to one of the insertshiftdirection constants, the insert method will revert to its default behavior and shift cells according to the shape of the range.

these types of syntax errors can be catched easily by using the ‘option explicit’ directive on top of the module. when you compile the module, the error will be signaled.

Reply | Quote