+ Reply to Thread
Results 1 to 6 of 6

Worksheet.Range(Cells(r,c),Cells(r,c)) & error 1004

Hybrid View

onebir Worksheet.Range(Cells(r,c),Cel... 03-27-2007, 08:24 AM
Simon Lloyd it's normally used in this... 03-27-2007, 08:34 AM
mudraker If you do not use a command... 03-27-2007, 09:29 AM
onebir Thanks very much for the... 03-27-2007, 11:46 AM
mudraker If OutputSheet is the active... 03-27-2007, 07:47 PM
onebir Thanks - that's good to know. 03-28-2007, 05:10 AM
  1. #1
    Registered User
    Join Date
    03-27-2007
    Posts
    3

    Worksheet.Range(Cells(r,c),Cells(r,c)) & error 1004

    I'd like to access a range using this syntax, but it never works!
    Here's (the edited hightlights of) my code:

    Sub Test()
      Set TestSheet = Worksheets("A")
      Set OutputSheet = Worksheets("B")
      For i = 1 To 10
        TestSheet.Range(Cells(i, 1), Cells(i, 6)).Copy
        OutputSheet.Cells(i, 1).Paste
      Next i
    End Sub
    When it gets to the bold line, the dreaded incomprehensible error 1004 strikes. It's very frustrating to be thwarted doing something so apparently simple... If anyone can explain what I'm doing wrong, I'll be very grateful!
    Last edited by onebir; 03-27-2007 at 08:29 AM.

  2. #2
    Forum Expert Simon Lloyd's Avatar
    Join Date
    03-02-2004
    Location
    locked in the cage
    MS-Off Ver
    All the ones my homepage shows
    Posts
    3,161
    it's normally used in this fashion:
    Worksheets(1).Range(.Cells(1, 1), _.Cells(10, 10)).copy
    Regards,
    Simon

  3. #3
    Valued Forum Contributor mudraker's Avatar
    Join Date
    11-10-2003
    Location
    Melbourne, Australia
    Posts
    3,983
    If you do not use a command at the end of
    Cells(i, 1)
    VBA thinks you want to use the cells value

    You need to add .Address
    TestSheet.Range(Cells(i, 1).Address, Cells(i, 6).Address).Copy
    Please Read Forum Rules Before Posting
    Wrap VBA code by selecting the code and clicking the # icon or Read This
    How To Cross Post politely

    Top Excel links for beginners to Experts

    If you are pleased with a member's answer then use the Scales icon to rate it
    If my reply has assisted or failed to assist you I welcome your Feedback.

  4. #4
    Registered User
    Join Date
    03-27-2007
    Posts
    3
    Thanks very much for the help!

    TestSheet.Range(Cells(i, 1).Address, Cells(i, 6).Address
    did the trick

    I've got two Excel VBA ebooks here, and neither mention the need for 'Address'. Not worth the paper they're not written on!

    I should add that there's an error in the next line. It should read:

    OutputSheet.Range(Cells(i, 1).Address, Cells(i, 6)).PasteSpecial
    Paste won't work - but at least the error message is intelligible enough that you can go to the object browser and look up something that will...

  5. #5
    Valued Forum Contributor mudraker's Avatar
    Join Date
    11-10-2003
    Location
    Melbourne, Australia
    Posts
    3,983
    If OutputSheet is the active sheet, paste should work

  6. #6
    Registered User
    Join Date
    03-27-2007
    Posts
    3
    If OutputSheet is the active sheet, paste should work
    Thanks - that's good to know.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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