Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

automate loop / logic via excel or vba

0

Hi

PFA Excel sheet in which how to automate Qty to Hold to compensate debit amt (outstanding) and further Release (Free) remaining Qty after recovering debit amt. under same client code. Hold Qty can not be more than given Total Quantity.   regards
Answer
Discuss

Answers

0

I suggest this formula for your column N.

=MIN(-INT($G3/-$E3), $D3)

The calculation can't return a result that is greater than the Total Quantity. The double minus effects a round-up: For example, any value greater than 10 will be interpreted as 11.

The formula for the Free Quantity should be calculated as the difference between the Total Quantity and the Hold Quantity. I recommend the use of absolute references for all columns.

=$D3-$N3

You may like to reconsider the use of column P. Your draft sheet repeats the value from column D but arrived at by a different calculation - not commendable. Total - Hold = Free. Therefore Hold + Free = Total. Whom do you wish to confuse? I suggest this formula instead.

=IF($G3>$F3, "Overdrawn", "OK")

Since the formula in column N doesn't permit a quantity larger than is available you need a safeguard for scuh an event. The above formula will show a warning. Use conditional formatting to draw attention to it.

You claim that the formula incolumn L is working. It will show a negative amount if the Debit is greater than the Holding Value. The formula below prevents that. It can't show a value less than zero. Whether or not that is a good thing is another question but with the warning in column P you should be all right.

=MAX($F3-$G3,0)
Discuss

Discussion

Thanks for your reply.
However its hold good for single client code and not for repleated client code. For example client code B20 (refer cell A46). Now how to calculate hold quantity (column N46 - N67) ?
I need same formula for unique or multiple client codes to enable me to apply on entire sheet.
Either calculate Column N (hold qty) or O (free qty) and omit P (total qty not required, as already given in D)
regards
smtahil Apr 29, '18 at 12:57 am
How do you imagine to count the Hold Qty since the units are all of different value? In A3:A4 it seems that you intend to block the first item only if it is enough to cover the debit. Right now I don't know if that is easier than blocking a percentage of all, but it seems that blocking only some items might require more administrative effort. If the client can request which items to block and which are free you might as well forget about automating anything lol:
To my current understanding of your problem there are two choices. One is what you already have: You enter a debit amount against each item and the formulas work out how much of that item's quantity is blocked. You might have a separate sheet where you have the total debit for each client. The sheet we are looking at only manages the collateral. You can change the collateral by allocating the total debit to the items in a different way.
The other way works in the opposite direction. You have a separate sheet where the total debits of each client is recorded. You use the collateral sheet to enter free (or held) quantities. As you enter a quantity for one item all other items are adjusted automatically to make up the required total. If insufficient collateral is pledged a message appears. This sort of solution would require VBA.
Variatus (rep: 4889) Apr 29, '18 at 1:52 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login