Posts by jb2_86_uk

    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

    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?


    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: Macro: Identify particular row and paste from specific point

    Quote from S O;731129

    Hi JB, welcome to ozgrid :)

    Sounds like you simply need a combination of Find and Range.Resize() - can you upload an example of your data?

    Hi S O & Thank you,

    Thanks for getting back to me. Unfortunately when it comes to Macros/VBA, my experience is mainly of manually recording them, and then making minor alterations in VBA! Could you give me an example of how the Find & Range.Resize operations would work in this scenario?

    Unfortunately due to the actual version holding sensitive data, I cannot upload the original. However I have created and attached a very basic equivalent which I hope will demonstrate what I'm after.

    Please let me know if it's still unclear or if you need any other info to assist.

    Thank you!



    I have inherited a couple of large data tables ('Original' and 'Revised') which I am working to streamline as best as possible. Part of this is automating the process of modelling and applying changes. I have created a worksheet 'Changes' which pulls data from 'Original' using a unique row ID & vlookups and then allows the user to make or model required changes. I then wish for the changes to be able to be applied to the 'Revised' worksheet.

    I am trying to write a macro that will identify the correct row in the 'Revised' data table, using the unique row ID (Column A). And then paste an array (1 row x 78 columns) into that row, starting at column K. (overwriting anything that was already there). Does that make sense?

    I recorded the macro by copying the required ID from 'Changes', finding it in 'Revised' (via Ctrl+F), then manually moving the selection right by 9 cells to identify the required cell. Unfortunately this didn't work and the eventual cell reference was hard-coded into the macro.

    Can anybody give me any advice for how alter my macro (or completely re-write in VBA) so that it can look-up and select the required row and move the selection to row K ready for the data to be pasted?

    Please ask for clarification if I have been unclear on anything (or all!). Any advice would be greatly appreciated!