Help a noob, VBA assistance needed

[DerpDerpDerp]

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.

 

[johnnyL]

One thing I quickly see is that you don't have an 'Else' after the 'Exit Sub' line in the 2nd sub.

You also probably want 'and' instead of 'or' in the line above that.

 

[Fluff]

Hi & welcome to MrExcel.
How about

Private Sub Worksheet_Change(ByVal Target As Range)
   Dim myval As Variant
   Dim myval2 As Variant
   If Target.CountLarge > 1 Then Exit Sub
   If Not Intersect(Target, Range("A1:A5,B1:B5")) Is Nothing Then
      Select Case Target.Column
         Case 1
            Target.Offset(0, 2).Value = Target.Offset(0, 2).Value + Target.Value
         Case 2
            Target.Offset(0, 1).Value = Target.Offset(0, 1).Value - Target.Value
      End Select
      Application.EnableEvents = False
      Target.ClearContents
      Application.EnableEvents = True
   End If
End Sub

 

[DerpDerpDerp]

One thing I quickly see is that you don't have an 'Else' after the 'Exit Sub' line in the 2nd sub.

You also probably want 'and' instead of 'or' in the line above that.

Thank you for your reply, I tried with the Else Added but I had no success.

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

 

[DerpDerpDerp]

Hi & welcome to MrExcel.
How about

Private Sub Worksheet_Change(ByVal Target As Range)
   Dim myval As Variant
   Dim myval2 As Variant
   If Target.CountLarge > 1 Then Exit Sub
   If Not Intersect(Target, Range("A1:A5,B1:B5")) Is Nothing Then
      Select Case Target.Column
         Case 1
            Target.Offset(0, 2).Value = Target.Offset(0, 2).Value + Target.Value
         Case 2
            Target.Offset(0, 1).Value = Target.Offset(0, 1).Value - Target.Value
      End Select
      Application.EnableEvents = False
      Target.ClearContents
      Application.EnableEvents = True
   End If
End Sub

WOW, it works flawlessly. Thank you so much, I can rest now after 11 hours trying to figure it out.

This line " If Not Intersect(Target, Range("A1:A5,B1:B5")) Is Nothing Then" makes my brain hurt.

Thank you again.

 

[johnnyL]

One thing I quickly see is that you don't have an 'Else' after the 'Exit Sub' line in the 2nd sub.

You also probably want 'and' instead of 'or' in the line above that.

Fluff took care of you though.

 

[Fluff]

Glad we could help & thanks for the feedback.