+ Reply to Thread
Results 1 to 21 of 21

macro not working as expected, need someone good at debugging macro's

  1. #1
    Valued Forum Contributor dmcgov's Avatar
    Join Date
    11-11-2015
    Location
    Florida, USA
    MS-Off Ver
    Office 365 Business
    Posts
    1,421

    macro not working as expected, need someone good at debugging macro's

    HELLO ALL,

    So I have this macro that reads a sheet (named A through FFF), which basically copies each sheet to a "master" page, performs some calculations (like adding items up or deleting rows) and then copies the master to a final sheet (called "big master"). with me so far? when i run the macro on just a few pages (like A-F), the routine works like a champ. but when i run in on my big list (A-FFF), then it fails in certain pages (see list below). I have tried recreating the source pages to no avail. i just cant figure out why its doing what its doing. i have included the workbook and most of the problematic sheets (left A which works perfectly, cant upload all the sheets as it makes the file go above 1Mb). the macro that i run is called "grand finale" which isn't so grand now that its not working as expected. i need someone with a critical eye that can deduce what is wrong with my source pages.

    Sheets that dont calc correctly:
    Sheets T, W, Y, Z, AA, HH, II, UU, VV, ZZ, DDD, EEE, FFF (left this one off so that i could get file size under 1mb)

    Estimate Test upload.xlsm

    please rename "estimate test upload" to "estimate test", otherwise the macro will fail. thanks
    Last edited by dmcgov; 12-22-2015 at 08:31 AM. Reason: added reason for renaming upload

  2. #2
    Forum Expert skywriter's Avatar
    Join Date
    06-09-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    2,528

    Re: macro not working as expected, need someone good at debugging macro's

    Rather than all that explanation, it might be more helpful to just include the code, tell us which line it fails on and what the error message is.
    As a reminder, once your original request has been fulfilled please mark this thread as SOLVED by going to the "Thread Tools" drop down list above your first post and choosing solved.

    If you are happy with my help, then please consider clicking the add reputation button in the lower left hand corner of this post.

  3. #3
    Valued Forum Contributor dmcgov's Avatar
    Join Date
    11-11-2015
    Location
    Florida, USA
    MS-Off Ver
    Office 365 Business
    Posts
    1,421

    Re: macro not working as expected, need someone good at debugging macro's

    No error message as the routine runs through the paces normallly. so no error's at all. which is why i included the workbook, its the only way to see what is not happening. when you look at exhibit A (fixture 001) it correctly puts all the info on big master, but then it fails on the sheets listed in the OP.

    so can anyone help me?

  4. #4
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: macro not working as expected, need someone good at debugging macro's

    Hello dmcgov,

    There is quite a bit of code that needs to be looked at. I wouldn't expect anyone to respond right away. It may take a day or two for someone to locate the problem.
    Sincerely,
    Leith Ross

    Remember To Do the Following....

    1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.
    2. Thank those who have helped you by clicking the Star below the post.
    3. Please mark your post [SOLVED] if it has been answered satisfactorily.


    Old Scottish Proverb...
    Luathaid gu deanamh maille! (Rushing causes delays!)

  5. #5
    Valued Forum Contributor dmcgov's Avatar
    Join Date
    11-11-2015
    Location
    Florida, USA
    MS-Off Ver
    Office 365 Business
    Posts
    1,421

    Re: macro not working as expected, need someone good at debugging macro's

    ok, ill wait. i just cant figure out why its not working. most of the tabs are good. but the ones that dont work. i just dont know.

    thanks for all your help.

  6. #6
    Valued Forum Contributor dmcgov's Avatar
    Join Date
    11-11-2015
    Location
    Florida, USA
    MS-Off Ver
    Office 365 Business
    Posts
    1,421

    Re: macro not working as expected, need someone good at debugging macro's

    so one place the macro fails is during this code:

    Please Login or Register  to view this content.
    what this section of the code does is to take all of the rows from Master and insert it into BigMaster. but it only copies one column of data, instead of the whole range.

    any ideas?

  7. #7
    Valued Forum Contributor dmcgov's Avatar
    Join Date
    11-11-2015
    Location
    Florida, USA
    MS-Off Ver
    Office 365 Business
    Posts
    1,421

    Re: macro not working as expected, need someone good at debugging macro's

    so one place the macro fails is during this code:

    Please Login or Register  to view this content.
    what this section of the code does is to take all of the rows from Master and insert it into BigMaster. but it only copies one column of data (this shows up in certain pages), instead of the whole range. i can list the pages that fail this way if necessary.

    any ideas?

  8. #8
    Forum Guru jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: macro not working as expected, need someone good at debugging macro's

    Hi dmcgov

    You're missing a couple of dots (.) in these two lines...could be part of the problem.
    Please Login or Register  to view this content.
    Please Login or Register  to view this content.
    John

    If you have issues with Code I've provided, I appreciate your feedback.

    In the event Code provided resolves your issue, please mark your Thread as SOLVED.

    If you're satisfied by any members response to your issue please use the star icon at the lower left of their post.

  9. #9
    Valued Forum Contributor dmcgov's Avatar
    Join Date
    11-11-2015
    Location
    Florida, USA
    MS-Off Ver
    Office 365 Business
    Posts
    1,421

    Re: macro not working as expected, need someone good at debugging macro's

    I made the change that you requested and still the same problem with this part of the macro. it only copies one column and not the entire range.

  10. #10
    Forum Expert nilem's Avatar
    Join Date
    10-22-2011
    Location
    Ufa, Russia
    MS-Off Ver
    2013
    Posts
    3,357

    Re: macro not working as expected, need someone good at debugging macro's

    Hi dmcgov,
    try this
    Please Login or Register  to view this content.

  11. #11
    Valued Forum Contributor dmcgov's Avatar
    Join Date
    11-11-2015
    Location
    Florida, USA
    MS-Off Ver
    Office 365 Business
    Posts
    1,421

    Re: macro not working as expected, need someone good at debugging macro's

    nilem, gold star for you. that fixed one of the problems with the macro. now on to the next one. i will detail that in the post below.

  12. #12
    Valued Forum Contributor dmcgov's Avatar
    Join Date
    11-11-2015
    Location
    Florida, USA
    MS-Off Ver
    Office 365 Business
    Posts
    1,421

    Re: macro not working as expected, need someone good at debugging macro's

    ok so the next part of the macro needs to copy formulas correctly.

    here is the offending code (there is a lot of it). i will detail what fails during the macro running.

    Please Login or Register  to view this content.
    when it copies the formulas from the formulas tab, it fails on these lines of code

    Please Login or Register  to view this content.

    instead of giving the fixture number and name, it comes in as #N/A for both columns.

    any ideas?

  13. #13
    Forum Expert nilem's Avatar
    Join Date
    10-22-2011
    Location
    Ufa, Russia
    MS-Off Ver
    2013
    Posts
    3,357

    Re: macro not working as expected, need someone good at debugging macro's

    What is lookupABC123 and lookupROOMS - the named range? variable?

  14. #14
    Valued Forum Contributor dmcgov's Avatar
    Join Date
    11-11-2015
    Location
    Florida, USA
    MS-Off Ver
    Office 365 Business
    Posts
    1,421

    Re: macro not working as expected, need someone good at debugging macro's

    its found on the formulas sheet. its a named range. as far as i can tell, it should pull the names and numbers over like it does on most of the sheets.

    thanks for looking at this.

  15. #15
    Forum Expert nilem's Avatar
    Join Date
    10-22-2011
    Location
    Ufa, Russia
    MS-Off Ver
    2013
    Posts
    3,357

    Re: macro not working as expected, need someone good at debugging macro's

    I tested this code in your file
    Please Login or Register  to view this content.
    and it works for me

  16. #16
    Valued Forum Contributor dmcgov's Avatar
    Join Date
    11-11-2015
    Location
    Florida, USA
    MS-Off Ver
    Office 365 Business
    Posts
    1,421

    Re: macro not working as expected, need someone good at debugging macro's

    @nilem, now when i run "grand finale", it leaves off the fixture number (havent fixed the room names yet).

    any idea on why the column A is blank? i will put in the whole macro so you can run that against the workbook.

    Please Login or Register  to view this content.
    dont forget to rename the workbook, it should be "estimate test" and not "estimate test upload".
    Last edited by dmcgov; 12-22-2015 at 01:33 PM. Reason: added filename rename

  17. #17
    Valued Forum Contributor dmcgov's Avatar
    Join Date
    11-11-2015
    Location
    Florida, USA
    MS-Off Ver
    Office 365 Business
    Posts
    1,421

    Re: macro not working as expected, need someone good at debugging macro's

    my general thinking is that there must be something corrupt on the individual sheets though i cant point out where this would be. the macro runs fine on 75% of the sheets. so if i take sheet z and copy it to zz, then it works just fine. if i restore the old zz, then it fails. not sure of how to proceed.

  18. #18
    Forum Expert nilem's Avatar
    Join Date
    10-22-2011
    Location
    Ufa, Russia
    MS-Off Ver
    2013
    Posts
    3,357

    Re: macro not working as expected, need someone good at debugging macro's

    Hi dmcgov,
    I do not really understand the essence of your macro, but try this
    Please Login or Register  to view this content.
    Have you observed Falkon9 landing in Florida? Probably, it is amazing.

  19. #19
    Valued Forum Contributor dmcgov's Avatar
    Join Date
    11-11-2015
    Location
    Florida, USA
    MS-Off Ver
    Office 365 Business
    Posts
    1,421

    Re: macro not working as expected, need someone good at debugging macro's

    @nilem, yup, same problem, either blank in column A or NA in column B. any thoughts?

  20. #20
    Forum Expert nilem's Avatar
    Join Date
    10-22-2011
    Location
    Ufa, Russia
    MS-Off Ver
    2013
    Posts
    3,357

    Re: macro not working as expected, need someone good at debugging macro's

    Look at the Sheet "W" cell D5. There is space (gap) at the end of the word "Exhibit W", remove this space. Do the same thing with a sheet "AA" and "HH"

  21. #21
    Valued Forum Contributor dmcgov's Avatar
    Join Date
    11-11-2015
    Location
    Florida, USA
    MS-Off Ver
    Office 365 Business
    Posts
    1,421

    Re: macro not working as expected, need someone good at debugging macro's

    Wow, glad you have a critical eye! Gold Stars for you... i'll be marking this as solved.

+ 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. Macro template good saved but running macro with imported data problem
    By brazilexcel in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-18-2015, 10:42 PM
  2. [SOLVED] Help debugging macro
    By kellynicolebarrett in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 07-16-2014, 09:17 AM
  3. Specific line of my macro isnīt working as expected
    By Marceltcm in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 08-30-2013, 11:47 AM
  4. [SOLVED] Debugging a macro
    By jharaldson in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-07-2013, 01:52 PM
  5. MACRO debugging but working in specific istances
    By ska87RA in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-05-2013, 08:19 AM
  6. [SOLVED] Macro debugging
    By VelvetRevolver84 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-01-2013, 06:14 PM
  7. Help please in debugging this macro
    By sungen99 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 06-23-2006, 11:45 AM

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