DerpDerpDerp
New Member
- Joined
- Jan 14, 2022
- Messages
- 3
- Office Version
- 2019
- Platform
- Windows
Hello there,
I am new to VBA and I am trying to make a sheet where I can update the values of 1 collumn for my inventory managment.
The idea is that my stock value for each product will be updated based on the imputs of manufactured items (will increase product stock value) and sold items (will decrease product stock value)
After many hours of search I can make 1 column work but not 2 at the same time.
1 Column working:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim myval As Variant
Application.EnableEvents = False
If Intersect(Target, Range("B1:B5")) Is Nothing Then
Exit Sub
Else
myval = Target.Value
Target.Offset(0, 1).Value = Target.Offset(0, 1).Value + myval
Range("B1:B5").ClearContents
End If
Application.EnableEvents = True
End Sub
My attempt at 2 columns (not working):
Private Sub Worksheet_Change(ByVal Target As Range)
Dim myval As Variant
Dim myval2 As Variant
Application.EnableEvents = False
If Intersect(Target, Range("B1:B5")) Is Nothing Or Intersect(Target, Range("A1:A5")) Is Nothing Then
Exit Sub
myval = Intersect(Target, Range("B1:B5"))
Target.Offset(0, 1).Value = Target.Offset(0, 1).Value - myval
myval2 = Intersect(Target, Range("A1:A5"))
Target.Offset(0, 2).Value = Target.Offset(0, 2).Value + myval2
Range("B1:B5").ClearContents
Range("A1:A5").ClearContents
End If
Application.EnableEvents = True
End Sub
Thanks in advance.
I am new to VBA and I am trying to make a sheet where I can update the values of 1 collumn for my inventory managment.
The idea is that my stock value for each product will be updated based on the imputs of manufactured items (will increase product stock value) and sold items (will decrease product stock value)
After many hours of search I can make 1 column work but not 2 at the same time.
1 Column working:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim myval As Variant
Application.EnableEvents = False
If Intersect(Target, Range("B1:B5")) Is Nothing Then
Exit Sub
Else
myval = Target.Value
Target.Offset(0, 1).Value = Target.Offset(0, 1).Value + myval
Range("B1:B5").ClearContents
End If
Application.EnableEvents = True
End Sub
My attempt at 2 columns (not working):
Private Sub Worksheet_Change(ByVal Target As Range)
Dim myval As Variant
Dim myval2 As Variant
Application.EnableEvents = False
If Intersect(Target, Range("B1:B5")) Is Nothing Or Intersect(Target, Range("A1:A5")) Is Nothing Then
Exit Sub
myval = Intersect(Target, Range("B1:B5"))
Target.Offset(0, 1).Value = Target.Offset(0, 1).Value - myval
myval2 = Intersect(Target, Range("A1:A5"))
Target.Offset(0, 2).Value = Target.Offset(0, 2).Value + myval2
Range("B1:B5").ClearContents
Range("A1:A5").ClearContents
End If
Application.EnableEvents = True
End Sub
Thanks in advance.