Apply working single row formula to all further rows with Click Button

[RyanMc]

Hi guys,

Complete newbie to VBA! I have created a stock management worksheet that I "simply" want to add or subtract from a running total with a click button to carry out the function. At the minute I have the following code:

Private Sub commandbutton1_click()


        Range("J5").Value = Range("J5").Value + Range("H5").Value
        Range("J5").Value = Range("J5").Value - Range("I5").Value
        Range("H5:I2000").ClearContents


End Sub

I could potentially have hundreds of lines running in rows that I would want this function to be carried out for if a value is present in column H (Goods In) or column I (Goods Out). I therefore also need to ignore blank cells.

I'm sure this is very straight forward but I'm stumped!

Thanks
Ryan

 

[JackDanIce]

Welcome to the board.

Try:

Private Sub commandbutton1_click()

    Dim x       As Long
    Dim arr()   As Variant
    
    x = Cells(Rows.count, 10).End(xlUp).row - 4
    arr = Cells(5, 8).Resize(x, 3).Value
    For x = LBound(arr, 1) To UBound(arr, 1)
        arr(x, 3) = arr(x, 3) + arr(x, 1) - arr(x, 2)
        arr(x, 1) = Empty
        arr(x, 2) = Empty
    Next x
    
    Cells(5, 8).Resize(UBound(arr, 1), UBound(arr, 2)).Value = arr
    Erase arr

End Sub

 

[RyanMc]

Thanks for that! Worked perfectly!

I'm at the very start of developing a fairly robust stock management database where I will eventually want to submit a date with each addition or removal of stock in order to build a weekly/monthly usage and forecasting model...wish me luck!

 

[JackDanIce]

You're welcome and good luck.