Hey Guys,
This might seem like alot to ask but im kinda stuck, and im kind of a newbie, so if someone could help me out that would be greatly appreciated.
Here what i have:
(Sheet 1)
Column A: Item
Column B: Total In Stock
Column C: Total Out
Column D: Weight In Stock
Column E: Weight Out
And a button that on click: subtracts C from B, updates B, then clears C.
and, subtracts E from D, updates D, then clears E.
What i want to do:
Before i clear C and E, i want to copy all the data from any row that was changed, to Sheet 2 (in the next available row) and then add the date (pref. xx/xx/xxxx) to Column F (basically a log of every change)
thanx to all in advanced.
This might seem like alot to ask but im kinda stuck, and im kind of a newbie, so if someone could help me out that would be greatly appreciated.
Here what i have:
(Sheet 1)
Column A: Item
Column B: Total In Stock
Column C: Total Out
Column D: Weight In Stock
Column E: Weight Out
And a button that on click: subtracts C from B, updates B, then clears C.
and, subtracts E from D, updates D, then clears E.
Code:
Sub FindSubtract()
With Worksheets("Sheet1").Range("C:C")
Set C = .Find("*", LookIn:=xlFormulas)
If Not C Is Nothing Then 'Found value
firstaddress = C.Address
Do
Cells(C.Row, 2).Value = Cells(C.Row, 2) - Cells(C.Row, 3)
Set C = .FindNext(C)
Loop While Not C Is Nothing And C.Address <> firstaddress 'If it's not the first instance, keep going
End If
End With
With Worksheets("Sheet1").Range("E:E")
Set E = .Find("*", LookIn:=xlFormulas)
If Not E Is Nothing Then 'Found value
firstaddress = E.Address
Do
Cells(E.Row, 4).Value = Cells(E.Row, 4) - Cells(E.Row, 5)
Set E = .FindNext(E)
Loop While Not E Is Nothing And E.Address <> firstaddress 'If it's not the first instance, keep going
End If
End With
With Worksheets("Sheet1").Range("C:C")
Range("C:C").ClearContents
End With
With Worksheets("Sheet1").Range("E:E")
Range("E:E").ClearContents
End With
End Sub
What i want to do:
Before i clear C and E, i want to copy all the data from any row that was changed, to Sheet 2 (in the next available row) and then add the date (pref. xx/xx/xxxx) to Column F (basically a log of every change)
thanx to all in advanced.