Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Nov 2004
    Location
    Sacramento, California, USA
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel row insert (Excel 2002)

    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?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Excel row insert (Excel 2002)

    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.

  3. #3
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Brussels, Brussel, Belgium
    Posts
    159
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel row insert (Excel 2002)

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •