VBA Code crashing

  • Hi Team,


    I had written a VBA code, which ran when ran it step by step. But it crashes when run directly. So I broke down it into steps to check where the error is.

    Pasting below the code which crashes when run directly and works fine when run step by step. Can anyone please help me what's wrong:

    Note: I've already declared the variables and objects in Step1.

  • Hello,


    There are many reasons for this code not to run as smoothly as you could expect it ...


    As a general rule, you should also get rid of all the instructions with Activate and with Select


    How have you declared your variables ??? As Private ...??? As Public ...??


    Finally, a macro is designed and belongs to a workbook ...

    so, for consistency and testing purposes, attaching your file would be more helpful ....


    Hope this will help

    :)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • But Carim , I wouldn't be able to replace the activate to select everywhere, since at times it's about activating the different excel window at different instance.

  • Hello,


    Thanks for your sample workbook.


    As soon as I have a moment, will dive into your macros ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Trying to understand your code is a nightmare. It's difficult to follow which sheet is being used.


    You should not use error handlers until you have the code working. You are stopping any useful error messages!


    I'm not sure if I have followed this correctly, but this is how to avoid selecting and activating.


    It's also a good idea to add notes to the code


  • Unfortunately, it is not an issue of a quick fix ...


    All your instructions do require to be re-written ... which will take a while ...


    Will try to redesign your " Step 2 " macro ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Seems to me you are constantly trying to determine the last row ...


    Code
    1. lr = Sheet1.Cells(Rows.Count, "A").End(xlUp).Row


    and regarding the handling of all your headers ...



    Hope this will help

    :)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Among the headaches you are creating yourself ...


    In order to get a simple count, you are using

    Code
    1. macro_wb.Worksheets("Sheet1").Select
    2. Range("F2").Select
    3. Selection.End(xlDown).Select
    4. lr = ActiveCell.Row
    5. db_count = lr - 2


    which can be achieved with a one-line instruction

    Code
    1. db_count = Sheet1.Cells(Rows.Count, "F").End(xlUp).Row - 2


    Would encourage you to completely re-visit your entire code ...


    Hope this will help

    :)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Have you managed to solve your problem ...?


    Should the thread be marked as resolved ...?

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)