Incomplete Calculation when running Macro

  • I'm tearing my hair out!

    I have what should be a simple macro to refresh two data tables (external sources) and recalculate a 'dashboard' style worksheet.

    If I manually refresh the tables and recalculate - it works.
    If I manually step through the macro (F8) - it works
    If I run the macro, it only partially recalculates. As soon as the macro finishes, I can press F9 and it will calculate again.

    How can this be!?

    I have tried to force a second calculation in the macro, but this doesn't work. Stepping through the macro the second calc isn't required (because the first one worked!) and when running the macro, it still ends leaving calculations outstanding.

    Can anyone suggest why this might be or what I can to get around this?


    For what it's worth, the code is below:

  • Re: Incomplete Calculation when running Macro

    sounds like your not leaving enough time for refresh

    Application.Wait(Now + TimeValue("0:00:10"))

    maybe make it wait for awhile add this before calculate

  • Re: Incomplete Calculation when running Macro

    Quote from humdingaling;790894

    sounds like your not leaving enough time for refresh

    Thanks for the suggestion, unfortunately it hasn't helped. I can see from the tables after the macro has ran that the tables have refreshed. It is really is just a case that certain formulae haven't updated.

    There are three main types of formula on the dashboard;
    Array formulae - These do recalculate
    Vlookups - These do not recalculate
    Direct cell references - These do not update.
    - Does that give any clues about what might be going on?


  • Re: Incomplete Calculation when running Macro

    Damn, I haven't come across the CalculateFull method before, my eyes just lit up like a Christmas tree - alas it has not worked though :( Same for CalculateFullRebuild too.

    Even using another idea from the same thread -

    Cells(2, 8).Formula = Cells(2, 8).Formula

    - to reapply the formula in the cells which don't recalculate still doesn't work :(

    I think I'm flogging a dead horse here, I am just going to have to turn on autocalculations and accept it is clunky as hell :(

  • Re: Incomplete Calculation when running Macro

    Wow, breakthrough, I have found -


    - Will perform the whole calculation! Hooray

    Thanks for your help Humdingaling!


Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!