Macro to subtract from one column first, then another if remainder

[neilo1969]

Hi All,

I'm pretty new to Excel and am having problems trying to write a macro using Excel 2003

I'm wanting to subtract a booking out value first from the old stock value, once old stock is at zero I want to subtract any remaining booked out material from new stock value. In some cases the booked out value will be greater than the old stock, so should run that down to zero before moving on to new stock.

I have numbers listed in column J which represent parts to book out. When I run a macro I want excel to look at the stock held in column E and column G. If quantity of parts to be booked out in column J is less than old stock in column E, then delecte J from E. If the value in J is greater than E, than first subtract E down to zero and then subtract the remainder of J value from new stock in column G.

Example 1 (before macro)

Col E Col G Col J
Old Stock New Stock Book out
10 100 5
30 90 30
15 200 25


Example 2 (after macro)

Col E Col G Col J
Old Stock New Stock Book out
5 100
0 90
0 190

In example 2, the 5 from column J has been subtracted from the 10 in column E leaving 5, the 30 has been subtracted from 30 leaving zero, and 15 of the 25 from book out value in J has been subtracted from the 15 in E bringing it down to zero, then the remaining 10 has been subtracted from new stock in column G.

When I only had a single stock column, I was able to record a macro that copied column J and then "Paste Special" with values and subtract options selected to the stock column. Now that I have two columns of stock the method has me stumped.

Does anyone have an idea of how I could add some code to my macro to achieve the results I'm looking for ?

Thanks in advance,

Neil

[snb]

Please, post a sample workbook.....

[watersev]

hi, neilo1969, please check attachment, run code "test"