+ Reply to Thread
Results 1 to 8 of 8

Error '1004': Application-defined or object-defined error when trying to copy a range

  1. 08-07-2014, 02:55 PM #1
    TLeng
    TLeng is offline
    Registered User
    Join Date
    08-07-2014
    Location
    Houston, Texas
    MS-Off Ver
    2010
    Posts
    22

    Error '1004': Application-defined or object-defined error when trying to copy a range

    I'm new to vba and trying to create a complex spreadsheet, so I'm sure I'll be posting a lot of questions. I'm trying to use a find function to range in one worksheet and copy those values into another worksheet. The hard part is the find function is looking for a value in the first column of the spreadsheet and then I need to choose the information in a different column of that worksheet. I've gotten the first two find functions to work. The third function returns the error. There's probably a better way of accomplishing what I'm trying to do. Any thoughts?

    Please Login or Register  to view this content.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 08-07-2014, 04:10 PM #2
    Bernie Deitrick
    Bernie Deitrick is offline
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    5,945

    Re: Error '1004': Application-defined or object-defined error when trying to copy a range

    This will fail because Cells (1,1) is actually cell A1
    Please Login or Register  to view this content.
    What cell do you want information from? Perhaps column L of that row?

    Please Login or Register  to view this content.
    And what do you mean by range of temperatures? Multiple Cells? (11 rows worth?)

    Please Login or Register  to view this content.
    And you can find the CPhase value once:

    Please Login or Register  to view this content.
    And unless you are referring to batter-fried foods, I think "tempura-ture" is actually "temperature"
    Last edited by Bernie Deitrick; 08-07-2014 at 04:13 PM.
    Bernie Deitrick
    Excel MVP 2000-2010
    Reply With QuoteRegister To Reply
  3. 08-11-2014, 08:32 AM #3
    TLeng
    TLeng is offline
    Registered User
    Join Date
    08-07-2014
    Location
    Houston, Texas
    MS-Off Ver
    2010
    Posts
    22

    Re: Error '1004': Application-defined or object-defined error when trying to copy a range

    The code looks a lot cleaner using the one find function. I made the changes below:

    Please Login or Register  to view this content.
    The error message change to "Method 'Range' of object'_Worksheet' failed. I think the comment that Bernie Deitrick made about trying to populate the values for 11 cells in a range is where I'm getting stuck.
    Reply With QuoteRegister To Reply
  4. 08-11-2014, 08:33 AM #4
    TLeng
    TLeng is offline
    Registered User
    Join Date
    08-07-2014
    Location
    Houston, Texas
    MS-Off Ver
    2010
    Posts
    22

    Re: Error '1004': Application-defined or object-defined error when trying to copy a range

    I still have the spellling error of "tempurature" instead of "temperature". I am going to fix that before I forget again.
    Reply With QuoteRegister To Reply
  5. 08-11-2014, 09:28 AM #5
    Bernie Deitrick
    Bernie Deitrick is offline
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    5,945

    Re: Error '1004': Application-defined or object-defined error when trying to copy a range

    There is nothing wrong with your code. What is the probable source of the error is how you are interacting with the workbook/worksheets.

    At first glance, I think you have this backwards:

    Range("ToStrSpan1").Value = pOutput.Cells(r, "G").Value

    What that is doing is assigning a value FROM the output sheet rather than TO the output sheet, which seems the more logical desire.

    Further, Range("Temp1") may not exist on the currently active sheet. You should fully specify where that value is located, along the lines of

    Chart.Range("Temp1")
    or
    pOutput.Range("Temp1")
    or
    Worksheets("Other worksheet").Range("Temp1")
    Reply With QuoteRegister To Reply
  6. 08-11-2014, 12:56 PM #6
    TLeng
    TLeng is offline
    Registered User
    Join Date
    08-07-2014
    Location
    Houston, Texas
    MS-Off Ver
    2010
    Posts
    22

    Re: Error '1004': Application-defined or object-defined error when trying to copy a range

    My naming convention is probably not the normal. I've named the one spreadsheet "pOutput" because I've copied a report generated in another computer program and pasted it into Excel. I'm using the "Chart" spreadsheet to format the information found in the report and organize the information differently. I'm trying to take 11 cells worth of information from column K in the "PLS CADD Output" worksheet and copy it to column D in the "Circuit 1" spreadsheet. The first block of code specifies Range "Temp1" as D20:D30 in the "Circuit1" worksheet. Hopefully that makes sense.
    Reply With QuoteRegister To Reply
  7. 08-11-2014, 01:15 PM #7
    Bernie Deitrick
    Bernie Deitrick is offline
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    5,945

    Re: Error '1004': Application-defined or object-defined error when trying to copy a range

    I understand now.

    You are confusing range object variables with named ranges.

    Range("Temp1")

    If a range named "Temp1"

    In your code, you are creating a range object named "Temp1"

    Set Temp1 = Chart.Range("D20:D30")

    but Temp1 is not its range name, but the object variable name.

    So:

    Range("Temp1").Resize(11, 1).Value = pOutput.Cells(r, "K").Resize(11, 1).Value

    should be

    Temp1.Resize(11, 1).Value = pOutput.Cells(r, "K").Resize(11, 1).Value

    It is possible to have both: a range object named Temp1, and a named range named "Temp1" Since they are stored in different places there is no conflict.

    Please Login or Register  to view this content.
    Last edited by Bernie Deitrick; 08-11-2014 at 01:21 PM.
    Reply With QuoteRegister To Reply
  8. 08-11-2014, 01:35 PM #8
    TLeng
    TLeng is offline
    Registered User
    Join Date
    08-07-2014
    Location
    Houston, Texas
    MS-Off Ver
    2010
    Posts
    22

    Re: Error '1004': Application-defined or object-defined error when trying to copy a range

    Okay, that makes sense about the keeping the range name and object variable name straight. I made the changes and the column populated perfectly! Thank you for the help! Please watch for more posts from me in the near future, because I'm definitely going to have more questions.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Run time error 1004: Application-defined or object-defined error in long IF VBA formula
    By lgass in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-12-2014, 12:42 PM
  2. [SOLVED] Error " Run-time error '1004': application defined or object defined error
    By lengwer in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-11-2013, 07:26 AM
  3. [SOLVED] Error 1004: Application-defined or object-defined error on Range(Cells(x,y)) syntax
    By winch in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 01-04-2013, 11:02 AM
  4. [SOLVED] run-time error '1004' application-defined or object-defined error - Excel 2007
    By kaurka in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-06-2012, 02:46 AM
  5. Opening Any Excel 2010 file get error 1004 Application defined or Object defined error
    By Kei Martin in forum Excel General
    Replies: 2
    Last Post: 09-12-2012, 01:01 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1