Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

problem subtracting quantity from sheet inventory

0

hello

I   try   finding  way  to  subtract the  quantity  from  sheet sales   after  fill quantity  in  column  f    should  search  the  brand  and  update   the  quantity in column e in  sheet  inventory   but  I failed  

this  is  my  trying 

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column <> 6 Or Target.Row = 19 Then Exit Sub
    Dim rngFound As Range
    With Worksheets("inventory").Columns(1)
            Set rngFound = .Find(Target(19, 2).Value)
            If Not rngFound Is Nothing Then
                Application.Goto rngFound
                rngFound(1, 5).Value = rngFound(1, 5).Value - Target.Value
            Else
                MsgBox "No match found for " & Target.Value
            End If
    End With
End Sub

so  i  need  help  to  fix  my  problem  

Answer
Discuss

Answers

0
Selected Answer

Leopard

This should work (where I've changed the line in bold below to make the seacrch value the "brand" from the row with the entered quantity):

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column <> 6 Or Target.Row = 19 Then Exit Sub
    Dim rngFound As Range
    With Worksheets("inventory").Columns(1)
            Set rngFound = .Find(Cells(Target.Row, 3).Value) 'search for the Brand associated with the Target quantity
            If Not rngFound Is Nothing Then
                Application.Goto rngFound
                rngFound(1, 5).Value = rngFound(1, 5).Value - Target.Value
            Else
                MsgBox "No match found for " & Target.Value
            End If
    End With
End Sub
Discuss

Discussion

John 
great  assistance !    it  works  excellantly    many  thanks buddy 
leopard (rep: 88) Mar 8, '21 at 2:41 pm
No problem Leopard
John_Ru (rep: 6102) Mar 8, '21 at 4:22 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login