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

update stock based cell value between two sheets

0

Hi Guys

I  try updating  stock in INV sheet based on based on entering QTY in column E  for ENTER sheet  after match B:D with A:C  for INV sheet , then should update QTY in column D for INV  sheet with  multiple  cases : 

if  the  cell M2 in ENTERING sheet  =PURCHASE or = RET1  and  change QTY ,then  should update QTY  in INV sheet  by sum QTY between two sheets 

If  the  cell M2 in ENTERING sheet  =SAELES or = RET2  and  change QTY ,then  should update QTY  in INV sheet  by subtract QTY between two sheets .

the code  doesn't  seem to  be  work 

Private Sub Worksheet_Change(ByVal Target As Range)
Dim X, lr As Long: With Sheets("INV"): lr = .Cells(.Rows.Count, 1).End(xlUp).Row: End With
If Not Intersect(Target, Range("E20:E34")) Is Nothing Then
    If Target.Cells.Count > 1 Then Exit Sub
    Application.EnableEvents = False
    X = Evaluate("=Match(""" & Target.Offset(, -3) & """ & """ & Target.Offset(, -2) & """ & """ & Target.Offset(, -1) & """,'INV'!A1:A" & lr & "&'INV'!B1:B" & lr & "&'INV'!C1:C" & lr & ",0)")
    If Not IsError(X) Then
        With Sheets("INV")
        If Sheets("ENTER").Range("M2") = "SALES" Then
            .Range("D" & X) = .Range("D" & X) - Target.Value
            MsgBox .Range("D" & X).Value
            ElseIf Sheets("ENTER").Range("M2") = "PURCHSE" Then
            .Range("D" & X) = .Range("D" & X) + Target.Value
            MsgBox .Range("D" & X).Value
            ElseIf Sheets("ENTER").Range("M2") = "RET1" Then
            .Range("D" & X) = .Range("D" & X) + Target.Value
            MsgBox .Range("D" & X).Value
             ElseIf Sheets("ENTER").Range("M2") = "RET2" Then
            .Range("D" & X) = .Range("D" & X) - Target.Value
            MsgBox .Range("D" & X).Value
            End If

        End With
    Else
        MsgBox "ITEM DOES NOT EXIST", vbInformation, ""
    End If
    Application.EnableEvents = True
End If
End Sub

I look forward to  help me 

excel
posted by: Halk (rep: 30) on Thu May 25, 2023 at 11:49 am
Answer
Discuss

Answers

0
Selected Answer

Halk

Your file had "PURCHASE" in cell M14 of worksheet "ENTER" but your code looked for PURCHSE in cell M2. In the attached file I corrected it by putting PURCHASE in (green) cell M2 and made the change in bold below:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim X, lr As Long: With Sheets("INV"): lr = .Cells(.Rows.Count, 1).End(xlUp).Row: End With
If Not Intersect(Target, Range("E20:E34")) Is Nothing Then
    If Target.Cells.Count > 1 Then Exit Sub
    Application.EnableEvents = False
    X = Evaluate("=Match(""" & Target.Offset(, -3) & """ & """ & Target.Offset(, -2) & """ & """ & Target.Offset(, -1) & """,'INV'!A1:A" & lr & "&'INV'!B1:B" & lr & "&'INV'!C1:C" & lr & ",0)")
    If Not IsError(X) Then
        With Sheets("INV")
        If Sheets("ENTER").Range("M2") = "SALES" Then
            .Range("D" & X) = .Range("D" & X) - Target.Value
            MsgBox .Range("D" & X).Value
            ElseIf Sheets("ENTER").Range("M2") = "PURCHASE" Then
            .Range("D" & X) = .Range("D" & X) + Target.Value
            MsgBox .Range("D" & X).Value
            ElseIf Sheets("ENTER").Range("M2") = "RET1" Then
            .Range("D" & X) = .Range("D" & X) + Target.Value
            MsgBox .Range("D" & X).Value
             ElseIf Sheets("ENTER").Range("M2") = "RET2" Then
            .Range("D" & X) = .Range("D" & X) - Target.Value
            MsgBox .Range("D" & X).Value
            End If

        End With
    Else
        MsgBox "ITEM DOES NOT EXIST", vbInformation, ""
    End If
    Application.EnableEvents = True
End If
End Sub

Where you have multiple tests on the same value, you could use If/ ElseIf statements (as you have) but I suggest you look at VBA's Case Select statement since it makes coding easier to read e.g. here's the Microsoft guidance: Select Case statement.

Hope this helps

posted by: John_Ru (rep: 6142) on Thu May 25, 2023 at 6:39 pm
Discuss

Discussion

Hi John,
the  code still doesn't  do  anything.
Halk (rep: 30) May 25, '23 at 11:01 pm
Halk. From memory (only) I changed the value of the first item on ENTER and it altered the Value of D3 on the second sheet. Check events are enabled.
John_Ru (rep: 6142) May 26, '23 at 1:32 am
Check events are enabled.
I really  did  it . but  I  no  know  what I miss
Halk (rep: 30) May 26, '23 at 8:17 am
Halk
I just tried again and the macro worked. With my file UP 3 stock update v0_a.xlsm (and macros enabled, I entered the value 20 into the three cells in column E of worksheet ENTER (with M2="PURCHASE"). The effect on INV was as follows (correct in my opinion):
Cell  Value With M2 Message INV Became
$E$20 20 Purchase Msg=202 $D$3 was 182 202
$E$21 20 Purchase Msg=39 $D$5 was 19 39
$E$22 20 Purchase Msg=220 $D$2 was 200 220


Not sure why you don't get the same. I didn't try other values in M2 but should be according to your Else If lines..
John_Ru (rep: 6142) May 26, '23 at 1:55 pm
John
thanks  for  confirmation.
doesn't  seem events are enabled as you  said 
now  I see the  code  works  as  I  expected.
thanks  very  much  for  your  help .
Halk (rep: 30) May 26, '23 at 2:35 pm
Phew! Glad you got it sorted. Thanks for selecting my Answer, Halk. 
John_Ru (rep: 6142) May 26, '23 at 3:36 pm
Add to Discussion


Answer the Question

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