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 an 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

 

[Ralajer]

Welcome to the board Neil,

Using the macro recorder has it limitation especially when you want to apply conditions. So I wrote the below macro. I assumed the data starts in Row 2 and that only thing in column J is the Book Out Values. Give it a try and let me know if it works.

Sub SubtractToZero()
    Dim rng As Range
    Dim cell As Range
    Dim temp As Double
    '// Range of book out Values
    Set rng = Range("J2:J" & Cells(Rows.Count, "J").End(xlUp).Row)
    
    For Each cell In rng
        '// Old Stock minus Book Out
        temp = cell.Offset(0, -5).Value - cell.Value
        '// When Book is Greater than Old it will be negative
        If temp < 0 Then
            '// Add the negative temp to New Stock
            cell.Offset(0, -3).Value = cell.Offset(0, -3).Value + temp
            '// Set temp  to zero
            temp = 0
        End If
        cell.ClearContents
        '// Output temp to old stock
        cell.Offset(0, -5).Value = temp
    Next cell
End Sub