My inventory spreadsheet has manually entered inventory counts that are refreshed periodically. We're not sticklers on keeping records of changes on this sheet because they are already elsewhere. I need to be able to enter the number of items detracted from different line items in an empty cell in column L, have the entered value automatically subtracted from the value in the corresponding cell in column J, leaving the new inventory count. After this, the cell where the value was entered in column L needs to be empty again, ready for a new entry.
I have manually entered values in J10 to J133, no formulas. When I enter a value in the corresponding cell in column L, I want it subtracted from the corresponding cell in column J. Exe: The value 25 is in cell J15. I enter the value 5 in cell L15. That value needs to be automatically subtracted from J15, leaving a new value of 20 in J15. Afterwards, cell L15 is returned to having no value and is ready for the next entry to be subtracted from J15.
I have attempted the VBA code in both these threads, Enter a value in one cell and subtract from another without formula and Enter a value in one cell and subtract from another that has a value already in it without formula. Neither are working and I have adjusted the Target Range to fit my sheet as well as the 2 offset values for the appropriate column differences. The VBA code given are both below, respectively, with the adjusted examples further below those.
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("F7:F14")) Is Nothing Then Exit Sub
Application.EnableEvents = False
With Target
.Offset(, -2).Value = .Offset(, -2).Value - .Value
.ClearContents
End With
Application.EnableEvents = True
End Sub
and
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
Dim cell As Range
Set rng = Intersect(Target, Range("A:A"))
If rng Is Nothing Then Exit Sub
Application.EnableEvents = False
For Each cell In rng
cell.Offset(0, 1).Value = cell.Offset(0, 1).Value - cell.Value
Next cell
Application.EnableEvents = True
End Sub
The corrected Target Range is "L:L" and cell.Offset is (0, -2) so that the VBA code looks like this:
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("F7:F14")) Is Nothing Then Exit Sub
Application.EnableEvents = False
With Target
.Offset(, -2).Value = .Offset(, -2).Value - .Value
.ClearContents
End With
Application.EnableEvents = True
End Sub
and
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
Dim cell As Range
Set rng = Intersect(Target, Range("A:A"))
If rng Is Nothing Then Exit Sub
Application.EnableEvents = False
For Each cell In rng
cell.Offset(0, 1).Value = cell.Offset(0, 1).Value - cell.Value
Next cell
Application.EnableEvents = True
End Sub
Copying just I6 to L19 into a blank workbook, the same exact VBA code works like a charm. When I copy my original spreadsheet to the new one, cells I6 to L19 are exactly the same, but I get the pop up
"Run-time error '1004':
Application-defined or object-defined error"
After that, no matter what I do, even creating a new work sheet from scratch, the VBA code will not work again until I restart my computer, and repeat.
Please help me.
I have manually entered values in J10 to J133, no formulas. When I enter a value in the corresponding cell in column L, I want it subtracted from the corresponding cell in column J. Exe: The value 25 is in cell J15. I enter the value 5 in cell L15. That value needs to be automatically subtracted from J15, leaving a new value of 20 in J15. Afterwards, cell L15 is returned to having no value and is ready for the next entry to be subtracted from J15.
I have attempted the VBA code in both these threads, Enter a value in one cell and subtract from another without formula and Enter a value in one cell and subtract from another that has a value already in it without formula. Neither are working and I have adjusted the Target Range to fit my sheet as well as the 2 offset values for the appropriate column differences. The VBA code given are both below, respectively, with the adjusted examples further below those.
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("F7:F14")) Is Nothing Then Exit Sub
Application.EnableEvents = False
With Target
.Offset(, -2).Value = .Offset(, -2).Value - .Value
.ClearContents
End With
Application.EnableEvents = True
End Sub
and
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
Dim cell As Range
Set rng = Intersect(Target, Range("A:A"))
If rng Is Nothing Then Exit Sub
Application.EnableEvents = False
For Each cell In rng
cell.Offset(0, 1).Value = cell.Offset(0, 1).Value - cell.Value
Next cell
Application.EnableEvents = True
End Sub
The corrected Target Range is "L:L" and cell.Offset is (0, -2) so that the VBA code looks like this:
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("F7:F14")) Is Nothing Then Exit Sub
Application.EnableEvents = False
With Target
.Offset(, -2).Value = .Offset(, -2).Value - .Value
.ClearContents
End With
Application.EnableEvents = True
End Sub
and
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
Dim cell As Range
Set rng = Intersect(Target, Range("A:A"))
If rng Is Nothing Then Exit Sub
Application.EnableEvents = False
For Each cell In rng
cell.Offset(0, 1).Value = cell.Offset(0, 1).Value - cell.Value
Next cell
Application.EnableEvents = True
End Sub
Copying just I6 to L19 into a blank workbook, the same exact VBA code works like a charm. When I copy my original spreadsheet to the new one, cells I6 to L19 are exactly the same, but I get the pop up
"Run-time error '1004':
Application-defined or object-defined error"
After that, no matter what I do, even creating a new work sheet from scratch, the VBA code will not work again until I restart my computer, and repeat.
Please help me.
