+ Reply to Thread
Results 1 to 10 of 10

Error 1004 when selecting a range of cells

  1. #1
    Registered User
    Join Date
    01-12-2011
    Location
    Queens, NY
    MS-Off Ver
    Excel 2007
    Posts
    7

    Error 1004 when selecting a range of cells

    I've been working on a piece of test code that opens another spread sheet, copies all the information in a particular column, then pastes that data back in the original spread sheet. Currently I’m also trying to add in the ability for the code to print “NA” in all the spaces where the work sheet being copied from is blank, such that if further worksheets are copied, the data all lines up in the composite sheet. I accomplish this by storing the length of the previous copied row in a variable OldRange, and then selecting the blank row out to this range, but it is in that line that I get an Error 1004 Application defined or object defined error. I’ve checked the line for the causes of this error that I’ve found on other forums but I have so far, been unsuccessful. Any help in correcting this problem would be greatly appreciated.

    PS: the hi-lighted line is where the error occurs

    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by isrisian; 01-18-2011 at 07:13 PM. Reason: code tagging

  2. #2
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,216

    Re: Error 1004 when selecting a range of cells

    Please wrap your code in code tags, before the moderators get you...

    Forum rules
    3. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # button at the top of the post window. If you are editing an existing post, press Go Advanced to see the # button.

    Cheers

  3. #3
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200

    Re: Error 1004 when selecting a range of cells

    Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example

  4. #4
    Registered User
    Join Date
    01-12-2011
    Location
    Queens, NY
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: Error 1004 when selecting a range of cells

    Thanks, and sorry about that, Newbie mistake.

  5. #5
    Registered User
    Join Date
    01-12-2011
    Location
    Queens, NY
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: Error 1004 when selecting a range of cells

    I've also tried this bit in place of the line getting the error and the same error occurs.

    Please Login or Register  to view this content.

  6. #6
    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: Error 1004 when selecting a range of cells

    What are the values of col and OldRange when the code breaks?
    Entia non sunt multiplicanda sine necessitate

  7. #7
    Registered User
    Join Date
    01-12-2011
    Location
    Queens, NY
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: Error 1004 when selecting a range of cells

    Quote Originally Posted by shg View Post
    What are the values of col and OldRange when the code breaks?
    C and 12 respectively.

  8. #8
    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: Error 1004 when selecting a range of cells

    You can only Select cells on the active sheet (and you almost never need to select at all, and it slows your code).

    You could use

    Please Login or Register  to view this content.
    Last edited by shg; 01-18-2011 at 07:04 PM.

  9. #9
    Registered User
    Join Date
    01-12-2011
    Location
    Queens, NY
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: Error 1004 when selecting a range of cells

    That still selects C1, when when col = C and OldRange = 12

  10. #10
    Registered User
    Join Date
    01-12-2011
    Location
    Queens, NY
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: Error 1004 when selecting a range of cells

    I fixed the problem. All I did was remove the xlUP and it selected what I wanted. Thank you very much for your help.

+ 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