Code Works, but only sometimes?
Results 1 to 4 of 4

Thread: Code Works, but only sometimes?

  1. #1
    New Member
    Join Date
    Nov 2015
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Code Works, but only sometimes?

    Hey all, for some reason my VBA code sometimes works, but sometimes spits out "Run-time error '1004': Application-defined or object-defined error".

    The section that causes trouble is always this one, specifically the select line:


    'Entering Stocks from IB Report


    Dim Report As Workbook
    Set Report = Application.Workbooks("IB_statement.csv")
    Report.Sheets("IB_statement").Range(Range("D29"), Range("D29").End(xlDown)).Select
    Selection.Copy
    Analysis.Sheets("Profit Calculations").Range("C3").PasteSpecial

    The rest of the code is copying things from xlsx files, so I wonder if that's causing the issue. On more testing, it looks like it runs fine if i have the IB_statement file open AND selected.
    Last edited by stillenacht; Jun 11th, 2018 at 12:19 PM.

  2. #2
    MrExcel MVP Jonmo1's Avatar
    Join Date
    Oct 2006
    Location
    Bryan, TX
    Posts
    44,054
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Code Works, but only sometimes?

    When nesting ranges, and you specify the book/sheet on the outside range, you must also specify it on the inside ranges too.
    Also, you can't select a sheet that is not currently active. (you don't need to select the range anyway.

    Try

    Code:
    Dim Report As Workbook
    Set Report = Application.Workbooks("IB_statement.csv")
    Report.Sheets("IB_statement").Range(Report.Sheets("IB_statement").Range("D29"), Report.Sheets("IB_statement").Range("D29").End(xlDown)).Copy
    Analysis.Sheets("Profit Calculations").Range("C3").PasteSpecial
    That can be simplified with a WITH structure

    Code:
    Dim Report As Workbook
    Set Report = Application.Workbooks("IB_statement.csv")
    With Report.Sheets("IB_statement")
        .Range(.Range("D29"), .Range("D29").End(xlDown)).Copy
    End With
    Analysis.Sheets("Profit Calculations").Range("C3").PasteSpecial
    Last edited by Jonmo1; Jun 11th, 2018 at 12:22 PM.
    Use the MrExcel HTML Maker to post nicely formatted tables in your forum posts.
    Find a link in post number 31

    The more we learn, and the better we get at our trade, the easier it becomes to overlook the obvious.

    Life moves pretty fast. If you don't stop and look around once in a while, you could miss it.
    Ferris Bueller A.K.A. John Hughes, 1986

  3. #3
    New Member
    Join Date
    Nov 2015
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Code Works, but only sometimes?

    Ah that makes total sense! Thank you, this has been causing me difficulties.

  4. #4
    MrExcel MVP Jonmo1's Avatar
    Join Date
    Oct 2006
    Location
    Bryan, TX
    Posts
    44,054
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Code Works, but only sometimes?

    You're welcome.
    Use the MrExcel HTML Maker to post nicely formatted tables in your forum posts.
    Find a link in post number 31

    The more we learn, and the better we get at our trade, the easier it becomes to overlook the obvious.

    Life moves pretty fast. If you don't stop and look around once in a while, you could miss it.
    Ferris Bueller A.K.A. John Hughes, 1986

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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