Urgent Help with Improving a Balancing Macro

Dear Experts

I urgently need your help with my problem about a macro that I have written to balance the "Existing Totals" in my spreadsheet with "Expected Totals". The Expected Totals are given values (i.e. pre-specified) at an aggregated level. I need to use them to adjust my detailed data so that their totals become the same as the new totals (i.e. Expected Totals).

I have worked out a Macro, and a method, for balancing (please see attached). The Macro does not perform well for all cases. Perhaps it requires some adjustment, addition of extra codes etc, to get it going well for all cases.

I have spent half a day to set up the file attached to make it easier for you to provide me with your response. In this file, I have described the situation and the problem. Also, I have commented out cell values and methods to make it easier for you to go through.

It has been a challenging task for me. I have exhausted all of my tricks to get it going, but it appears that on my own I can't progress it further.

I need your urgent help as I am in middle of my work and it does not allow me to progress.

I have include a sample of data that the Macro can handle it well; and a sample of data that it stops after going through many iterative loops of reducing balances to zeros.

I look forward to receiving your assistance as soon as possible (please see attached file).

Best Regards
Simon