VBA required for Goal Seek solution

[josephteh]

Seeking expert's help!

I need a VBA for goal seek to find the advances required in row 5, round to nearest hundreds, so that the bank balances in row 7 will be at least the values in row 9

Thank you!
Joseph

Goal Seek in VBA.png

[Kaper]

GoalSeek could be used, but why not use just formula in E5 and copied to right:

Please Login or Register  to view this content.

?

[Kaper]

PS. If for some reason you NEED using goalseek, the code could look like this:

Please Login or Register  to view this content.

but please make sure that row 5 contains values, not formulas (so the workbook you posted is OK, but if you tested my proposition from the above post, the code will fail).
if there are chances taht somebody have written a formula in row 5 use the following code

Please Login or Register  to view this content.

[josephteh]

Thank you Kaper, it's actually much more complicated than the sample data.

[josephteh]

The code works perfectly in the sample file.

Is it possible to make the code flexible to allow for inserting rows and columns, say with named-ranges?

Thank you.

[Kaper]

Probably - yes.
Show representative sample file.

[josephteh]

Thank you, Kaper. I have now named the range E5:G5 as "Advances" and range E9:G9 as "MinBal".

Please see attached file.

Thank you.

[Kaper]

I've added third named range

Please Login or Register  to view this content.

as we need 3 elements - value to be changed, real balance and required balance, and you have named only 2 of them.

The adopted code is:

Please Login or Register  to view this content.

Press the GoalSeek button (shape) in the attached file, to see how it works.

[josephteh]

Thank you Kaper! Works perfectly! Here's Rep.

[josephteh]

Dear Kaper

When the Bank balance c/f > Min. bank balance to maintain, a repayment of advance is made, but should not be more than the Cumulative advances made previously.

In Period 3 below, the repayment should be capped at 6,600, the Cumulative advances at end of Period 2.

Please help to take a look.

Thank you.
Joseph

Goal Seek in VBA 2.png

[Bo_Ry]

Please try

Please Login or Register  to view this content.

[josephteh]

Thank you Bo_Ry for the VBA code - it works. But... is it possible to use the range name "BankBalance" instead of using OFFSET to reference the range?

My actual data has rows in between.

Thank you.

[Bo_Ry]

New Range name:
Cumulative =$E$14:$J$14

Please Login or Register  to view this content.

or Formula

E8
=MAX(ROUNDUP((E12-SUM(E6:E7)-D9)/(1-$D9),-2),-SUM($D8:D8))

[josephteh]

Thanks Bo_Ry.. works perfectly! Here's Rep for you.