+ Reply to Thread
Results 1 to 7 of 7

What is wrong with this line of code for a cell offset?

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    08-16-2010
    Location
    Holland, MI
    MS-Off Ver
    Excel 2007
    Posts
    110

    What is wrong with this line of code for a cell offset?

    Wondering if someone could help me understand what's wrong with this line of code.
    Cells(c.Offset(w - 5).Row, c(-109).Column).Resize(, 18).Copy
    The problem seems to be the (-109). I want to offset the cell 109 columns to the left. When I run this I get an "Application define or Object defined" error. If I change that -109 to a zero like this...

    Cells(c.Offset(w - 5).Row, c(0).Column).Resize(, 18).Copy
    The code works, but it just take the row offset and doesn't offset the column. How do I get it to give me a negative offset?

    Thanks.

  2. #2
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: What is wrong with this line of code for a cell offset?

    It depends where c is, ie what are the rows and columns where C is found?

    C must be beyond column DE to offset it -109 to take you to column A.

  3. #3
    Forum Contributor
    Join Date
    08-16-2010
    Location
    Holland, MI
    MS-Off Ver
    Excel 2007
    Posts
    110

    Re: What is wrong with this line of code for a cell offset?

    Thanks for the reply. Yeah, I'm working from Cell ED so offsetting 109 to the left is OK.

  4. #4
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2010, 2019
    Posts
    40,689

    Re: What is wrong with this line of code for a cell offset?

    Assuming c is a range variable, all of these do the same thing:

        Cells(c.Offset(w - 5).Row, c.Offset(, -109).Column).Resize(, 18).Copy
        Cells(c.Row + w - 5, c.Column - 109).Resize(, 18).Copy
        c.Offset(w - 5, -109).Resize(, 18).Copy
        c(w - 4, -108).Resize(, 18).Copy
    Last edited by shg; 09-13-2014 at 05:51 PM.
    Entia non sunt multiplicanda sine necessitate

  5. #5
    Forum Contributor
    Join Date
    08-16-2010
    Location
    Holland, MI
    MS-Off Ver
    Excel 2007
    Posts
    110

    Re: What is wrong with this line of code for a cell offset?

    shg, thanks for the options. The first one was what I was looking for. Worked perfectly. Thanks!

  6. #6
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: What is wrong with this line of code for a cell offset?

    If you are working from column ED, offsetting -109 will take you to column AC.
    One of shg's lines should work

  7. #7
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2010, 2019
    Posts
    40,689

    Re: What is wrong with this line of code for a cell offset?

    ... deleted ...
    Last edited by shg; 09-13-2014 at 05:52 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. What's wrong with a specific line of code?
    By a1b2c3d4e5f6g7 in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 07-30-2014, 04:01 PM
  2. [SOLVED] Stuck using the Offset in Excel 2013, hi guys and gals, what is wrong with this code?
    By Hurricanefly in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-09-2013, 01:01 PM
  3. Wrong line of code banned me out and can't access vba code...
    By albertc in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 05-14-2012, 02:41 PM
  4. Need to know what is wrong with this 3 line code (row hiding with conditional formati
    By Turquoise_dax in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-20-2006, 01:40 PM
  5. Something wrong in one line of VBA code
    By Daniel Bonallack in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 06-29-2005, 03:05 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1