+ Reply to Thread
Results 1 to 25 of 25

Error 1004 - object-defined error when vba code not applicable

  1. #1
    Forum Contributor
    Join Date
    08-21-2011
    Location
    Australia, Perth
    MS-Off Ver
    Excel 2010
    Posts
    159

    Error 1004 - object-defined error when vba code not applicable

    Hi folks,

    Through playing around with the macro recorder and looking at other macros, I have written the following:

    Please Login or Register  to view this content.
    My only issue is that if one of the elements of the macro does not apply (eg there are no blank cells in R:R), I have an error and the macro does not run.

    Can someone please tell me what to add to bypass this problem and allow the macro to continue running? Also, if any of you can see a way to speed up the processing of the macro (which takes quite a bit of time at the moment), I'll be very grateful :-)

    Thanks in advance.
    S
    Last edited by SubwAy; 06-08-2016 at 08:51 PM.

  2. #2
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,122

    Re: Error 1004 - Application-defined or object-defined error when vba code not applicable

    You need to include an error trapping line.

    Please Login or Register  to view this content.

    Follow these lines and complete the remaining lines

    Please Login or Register  to view this content.
    Last edited by AB33; 06-08-2016 at 10:34 AM.

  3. #3
    Forum Contributor
    Join Date
    08-21-2011
    Location
    Australia, Perth
    MS-Off Ver
    Excel 2010
    Posts
    159

    Re: Error 1004 - Application-defined or object-defined error when vba code not applicable

    Thank you very much, that worked perfectly

    I see you removed the various ".select and selection". Can you please explain what the benefit of this is?

    Thank you,
    S

  4. #4
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,122

    Re: Error 1004 - Application-defined or object-defined error when vba code not applicable

    No need to use "Select". Let's say I want Range("A1"). VBA knows it is Range("A1") whether or not I added select.
    In fact it is bad programming style and could slow the execution of a code. However, as you learn and record macro, it is very helpful tool to easily see what is going on with the flow of the code.

  5. #5
    Forum Contributor
    Join Date
    08-21-2011
    Location
    Australia, Perth
    MS-Off Ver
    Excel 2010
    Posts
    159

    Re: Error 1004 - Application-defined or object-defined error when vba code not applicable

    Thank you AB33.

    Now that I am back at work I have rerun the macro and everythime I hit a
    Please Login or Register  to view this content.
    I get a Run-time error 424 - Object Required.

    I found that this disappears if I had "Selection" before the .Formula. Did I miss anything or do something wrong?

    Thank you,
    S
    Last edited by SubwAy; 06-08-2016 at 08:53 PM.

  6. #6
    Forum Contributor
    Join Date
    08-21-2011
    Location
    Australia, Perth
    MS-Off Ver
    Excel 2010
    Posts
    159

    Re: Error 1004 - object-defined error when vba code not applicable

    Hi all,

    So I got the code to work roughly how I intend it to but face two problems, one significantly more annoying than the other. Below is what I'm working with:

    Please Login or Register  to view this content.
    My two problems are:
    . I currently define the range of cells from row 2 to 70000 in most of the macro but the data actually stops at 61000 lines (may increase in the future though hence why I went with a bigger number). I therefore have 9000 lines of #N/A which I would like to be able to avoid but am worried the code would become a lot more complex.
    . If I happen to run the macro twice (when blank cells have already been filled), some fields (eg column H addressed in the second argument of the macro) gets filled randomly with #N/A. Can this be prevented?

    Thanks in advance for everyone's help.
    S
    Last edited by SubwAy; 06-09-2016 at 12:12 AM.

  7. #7
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,122

    Re: Error 1004 - object-defined error when vba code not applicable

    You have fixed the range and therefore lost the flexibility. I am surprised why you got an error. The best and easiest way to solve the issue is for you to attach a sample. I can quickly adjust your code by testing it with the sample provided.

  8. #8
    Forum Contributor
    Join Date
    08-21-2011
    Location
    Australia, Perth
    MS-Off Ver
    Excel 2010
    Posts
    159

    Re: Error 1004 - object-defined error when vba code not applicable

    So I have extracted the code and relevant spreadsheets and of course now I get a random 400 Error.

    Nevertheless, I have attached the format of data I am using so you may be able to see what triggers the error on the second run and how to automate the last row number.

    Thanks again.
    S
    Attached Files Attached Files
    Last edited by SubwAy; 06-09-2016 at 05:53 AM.

  9. #9
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,122

    Re: Error 1004 - object-defined error when vba code not applicable

    This is not a sample data, as you do not have any blanks on all of the columns, but I have attached a code which shows no error. Please attach a realistic sample.
    Attached Files Attached Files

  10. #10
    Forum Contributor
    Join Date
    08-21-2011
    Location
    Australia, Perth
    MS-Off Ver
    Excel 2010
    Posts
    159

    Re: Error 1004 - object-defined error when vba code not applicable

    Hi AB33,

    Apologies for not sending the full file, it simply cannot fit (over 70Mo).

    So I've verified everything once again and the issue keeps occuring.

    Effectively I run the macro once and everything is fine. I have one column at the end that checks whether the line balances out and it does. I run the macro a second time and while nothing should change (because all blanks are filled and all the arguments shouldn't execute), all my numbers change (but it is too difficult for me to isolate to one particular column because of the sheer volume of data).

    So my question is, is there anything I can add to the below code so each "argument" (probably not using the right terminology) does not run if the conditions aren't met (eg don't do anything to column H if you can't find blank cells).

    Please Login or Register  to view this content.
    THank you

  11. #11
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,122

    Re: Error 1004 - object-defined error when vba code not applicable

    You do not need to attach massive data, just a sample with few lines which mimics your actual data. It is a simple-few lines of raw data and desired result. When I apply the code, it should work with any data as long as the format of data for which the code is designed to work has not changed.
    The reason I always ask for a sample is to cut down all the misunderstanding and running around the circle.

  12. #12
    Forum Contributor
    Join Date
    08-21-2011
    Location
    Australia, Perth
    MS-Off Ver
    Excel 2010
    Posts
    159

    Re: Error 1004 - object-defined error when vba code not applicable

    Hi AB33,

    I finally found a way to isolate enough lines to replicate the issue.

    In the attached file, column CI is used as a check (when the macro is pressed, all values should turn to 0 via adjustments made in previous columns). Because some data is missing, you will see that the values don't change the first time you click. Yet if you click a second time, everything changes to values that are highly incorrect.

    I can't isolate what seems to create the issue if the macro is run a second time so any help on this would be greatly appreciated.

    Also, could you please let me know whether:

    . there is a way to automatically adjust the range to the last line of data (which would vary every month) rather than hardcoding it in the vba
    . there is a more efficient way to write the code to reduce the processing time over ~70,000 lines.

    Thanks again for your help.
    S
    Attached Files Attached Files

  13. #13
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,122

    Re: Error 1004 - object-defined error when vba code not applicable

    Hi SubWay,
    Before I look at how to trim the code and cut some of the lines down, we need to get the code working. I think the issue you have is: There are lots of errors on the formula because the formulas do not have iferror line. Since the cells are linked by formulas and if there is a single error on a column that will affect the rest of the columns. You need to include iferror on each formula. I also believe that your Index and match formula is wrong. I have come up with my own formula for column H and you can follow the format of this formula for the rest of I and M function.
    Attached Files Attached Files

  14. #14
    Forum Contributor
    Join Date
    08-21-2011
    Location
    Australia, Perth
    MS-Off Ver
    Excel 2010
    Posts
    159

    Re: Error 1004 - object-defined error when vba code not applicable

    Hi AB33,

    I haven't been able to find errors in the formula you mentioned but more than happy to include an IFERROR(___,"").

    The only section of the file that is likely to return an error is "' Imports Min for start of year range", which is promptly followed by a section that addresses the said "errors" (which need to be loaded so it can trigger this follow-up section).

    Please Login or Register  to view this content.
    I have double checked my index&match and confirm that it works as it is. I know I'm not capturing the column and define the lookup array to one column but it works well :-)

  15. #15
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,122

    Re: Error 1004 - object-defined error when vba code not applicable

    If the formulas work, why are the issues then? When I run the code, I did not get an error.
    Why do you have these 2 different lines

    Please Login or Register  to view this content.
    ?
    What I mean by error is: if a formula returns #NA or #REf, these are errors and if there are cells linked to cells, all the others will error too
    Last edited by AB33; 06-14-2016 at 05:01 AM.

  16. #16
    Forum Contributor
    Join Date
    08-21-2011
    Location
    Australia, Perth
    MS-Off Ver
    Excel 2010
    Posts
    159

    Re: Error 1004 - object-defined error when vba code not applicable

    Sorry for not being clear before

    The issue that I have is when I run the macro twice (inadvertently, which may happen if someone else is using this file).

    The main aim of this spreadsheet is to fill the blanks in the data set that I have so theoretically once it has been run once, there is nothing left to do. Yet when I run it a second time, I have very strange values that are loaded.

    To ensure your question on the vba you quoted:

    step1: for all blank cells, import via the Index&Match
    step2: when the Index&Match returns an error (as it should because data is missing somewhere else) then the default is to import the value across from the same line 31 columns prior.

    I hope it makes sense

  17. #17
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,122

    Re: Error 1004 - object-defined error when vba code not applicable

    First, please move the code from sheet data in to a module. If you look at my code, the sheet name is fully referenced, while your code works on the active sheet, so it may be probable that you are not always running on the data sheet. It is difficult to see why are getting a strange result. please post the sample when you get this weird result so as to see why the code behaves in a such away.

  18. #18
    Forum Contributor
    Join Date
    08-21-2011
    Location
    Australia, Perth
    MS-Off Ver
    Excel 2010
    Posts
    159

    Re: Error 1004 - object-defined error when vba code not applicable

    Ok now I identified what precisely creates the issue

    When the following code runs, it should select blank cells in column AP and enter a 0 value. The problem is that when the macro is run a second time (and there are no more blank cells), it replaces existing values with 0 instead of skipping them.

    Please Login or Register  to view this content.
    It seems to only do it with column AP even though I use the same "Selection.SpecialCells(xlCellTypeBlanks).Select" several times in the same macro. Any idea what is going on here?

    Thanks,
    Johann

  19. #19
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,122

    Re: Error 1004 - object-defined error when vba code not applicable

    Why are keep replacing the dynamic range I gave you with static data?
    Please Login or Register  to view this content.

  20. #20
    Forum Contributor
    Join Date
    08-21-2011
    Location
    Australia, Perth
    MS-Off Ver
    Excel 2010
    Posts
    159

    Re: Error 1004 - object-defined error when vba code not applicable

    So I think this is the first time but I'm giving up.

    I can't explain why but the code you gave me (with the dynamic range) returns completely weird results (ie instead of importing numbers in blank cells it will make cells with value turn blank). THe code that I currently have (with the defined range) works well as long as I only run it only once. A second time just messes everything up.

    Alternatively is there something simple to say "if there's isn't any blank cells in columns AP, AY, etc", then don't run the macro at all?

  21. #21
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,122

    Re: Error 1004 - object-defined error when vba code not applicable

    No,No!
    First, I did not give you any code. It is your code, I just adjust the rows.
    Second, the code on post 19, for example, does not fill 0 a second time if there are not blank cells. I have tested it. The code fills only when a cell is blank, so when it is first blank, it fills it with 0, but when you run the second time, the code skips all cells which are blank. Since we are testing more than a single cell and if some cells that are blank while others are not, the code errors, hence for tapping the error using resume next.
    I suggest that you should isolate each range in to separate code and run a test so as to see which lines of the code are not working. So, put each range column on a separate code and run them.
    I just can not replicate the issue you have. When I test and run the code, it works, but I do not know what do you mean by second time. Unless you show me when it errors, it is difficult to comment.

  22. #22
    Forum Contributor
    Join Date
    08-21-2011
    Location
    Australia, Perth
    MS-Off Ver
    Excel 2010
    Posts
    159

    Re: Error 1004 - object-defined error when vba code not applicable

    Thanks for sticking with me!

    So I've done what you recommended and using the code you amended, mainly having ".SpecialCells (xlCellTypeBlanks)" instead of my initial "Selection.SpecialCells(xlCellTypeBlanks).Select" makes it that the formula is inserted in every single line instead of the blank ones. Hence why when I try to run with your proposal (that clearly defines the range) I get results that are all over the place.

  23. #23
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,122

    Re: Error 1004 - object-defined error when vba code not applicable

    No, you are not getting it. I have not changed xlCellTypeBlanks and the rest of the code. All I did was to remove selecting a static range and replace it with a dynamic range. Since I could not see why you are getting an error, I have attached my own sample so that you can see the code works.

    There are two main reasons I suspect why you are getting funny result. For example, the attached code works on active sheet. If my cursor is on any other sheet when I run the code, I get a funny result. Why? Because, I have not told the code to run it on a specific sheet. My cursor has to be on active sheet when I run the code.
    Your code is also runs on active sheet, despite I have amended it to run it on a specific sheet, you may keep using your old code. I have also suggested you should put all your codes on "Standard Module" NOT on sheets or workbook. You need to run this code and tell me where it does not work, but I can not help you if the code does not work with unseen data.
    Attached Files Attached Files
    Last edited by AB33; 06-17-2016 at 03:55 AM.

  24. #24
    Forum Contributor
    Join Date
    08-21-2011
    Location
    Australia, Perth
    MS-Off Ver
    Excel 2010
    Posts
    159

    Re: Error 1004 - object-defined error when vba code not applicable

    My apologies for the confusion, a colleague suggested I change the "selection" part which I have now reversed.

    I am now going crazy and will try to word my quest for a solution differently: is there something stopping this macro from running if there are no blank cells in the selected ranges? It runs very well the first time but then replaces existing values with formulas if run a second time.

    If we can't add anything, I'll just make sure I am the only user and run it once only.

    Also, is there a way to use the dynamic range you gave me for the last section (' Removes formulas from workbook (excluding BZ to CH)

    Please Login or Register  to view this content.

  25. #25
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,122

    Re: Error 1004 - object-defined error when vba code not applicable

    Subway,
    The thread has been open for over three weeks now and have not made any progress, in fact we are now gone back to square one. It might be better for someone else with fresh eyes to have a go. You need to re-start a new thread and hopefully others might be able to sort it out for you.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Error message: Run-Time error '1004 '; application-defined or object-defined error
    By Davasu in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-23-2015, 06:52 AM
  2. Replies: 1
    Last Post: 10-22-2015, 05:50 AM
  3. Replies: 5
    Last Post: 08-19-2015, 07:10 PM
  4. HELP WITH ERROR CODE '1004' Application-defined or object defined error
    By QuintonMcCloud in forum Excel Programming / VBA / Macros
    Replies: 15
    Last Post: 04-20-2015, 03:34 PM
  5. VBA ERROR: run time error 1004: Application-defined or Object-defined error in excel 2013
    By AnanthKrishna in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-13-2015, 06:16 AM
  6. [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
  7. 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1