+ Reply to Thread
Results 1 to 5 of 5

Assigning Range to Array - one way works, the other doesn't

  1. #1
    Registered User
    Join Date
    05-21-2012
    Location
    Maryland
    MS-Off Ver
    Excel 2010
    Posts
    3

    Assigning Range to Array - one way works, the other doesn't

    I'm using VBA in Excel 2007 and noted that, when assigning a range to an array, one of these works, but the other doesn't:


    vrtExceptionsList = Worksheets("Exceptions").Range("A1:B80") <----WORKS LIKE A CHARM

    vrtExceptionsList = Worksheets("Exceptions").Range(Cells(1, 1), Cells(80, 2)) <---GIVES APPLICATION/OBJECT-DEFINED ERROR


    I'd really like to use the second method so I can have a variable for the last row instead of some arbitrary number (80 in this case).
    Any ideas why the 2nd method doesn't work for me?

    Thanks,
    -Jim

  2. #2
    Forum Expert
    Join Date
    12-15-2009
    Location
    Chicago, IL
    MS-Off Ver
    Microsoft Office 365
    Posts
    3,177

    Re: Assigning Range to Array - one way works, the other doesn't

    Try

    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    05-21-2012
    Location
    Maryland
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Assigning Range to Array - one way works, the other doesn't

    I just tried but got the same result

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

    Re: Assigning Range to Array - one way works, the other doesn't

    In your code (and in JieJenn's), the unqualified reference to Cells is to the active worksheet.

    Please Login or Register  to view this content.
    Last edited by shg; 05-21-2012 at 02:37 PM.
    Entia non sunt multiplicanda sine necessitate

  5. #5
    Registered User
    Join Date
    05-21-2012
    Location
    Maryland
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Assigning Range to Array - one way works, the other doesn't

    Ahhh...that worked. Yes I was referencing an inactive worksheet. Intuitively, it seemed like both methods should have worked since I explicitly referenced the inactive sheet by name. Now I can use my variable:

    Please Login or Register  to view this content.
    Thank you all for your help.
    -Jim

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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