VBA that Finds Matching Cells and Adds Cell Values

[pahickham]

This is a re-post from another site, since I wasn't having much luck on there:

The problem I'm presenting's a little above my VBA skills, so I appreciate any help and ideas. I've pasted a couple of excel sheets to give a general idea of what I'm looking for. Essentially I'm looking for a code that will find matching the "SAP" numbers, when input on the "Received" tab, and add the "qty" from the "Received" tab to the matching "Actual Qty" in the "Inventory" tab. Thanks for any help in advance, I look forward to seeing these ideas!

https://www.excelguru.ca/forums/showthread.php?9767-VBA-that-Finds-Matching-Cells-and-Adds-Cell-Values

 

[pahickham]

Sorry for the late response, I pasted in the code and there was no trailing space yet the line was still highlighted after the debug

 

[mumps]

The last file you posted had a trailing space in the sheet name. When I removed the extra space and tested the macro, it worked properly. Are you trying the macro on the same file or on a different file? Please upload a copy of the file that is generating the error.

 

[pahickham]

Yea i used it on the before file. This should be posted under the module section right?

 

[mumps]

Click here to download your file. Run the macro in Module2.

 

[pahickham]

Thank you for all the help, the downloaded VBA works, but for some reason won't work when i copy it onto the original before sheet i had posted? I know it doesn't matter since both sheets are the same, but I can't find the mistake. Also, the only minor issue that I have is that every time i activate the VBA it will continually add what's already been posted in the received section. I know this is my fault for not clarifying, but i intend to keep everything that goes in the received list and wish to add onto it in the future. Sorry to keep bothering you with problems

 

[mumps]

Please upload a copy of the file that is not working for you and explain in detail using an example, of what you mean by:

I intend to keep everything that goes in the received list and wish to add onto it in the future.

 

[pahickham]

Here's a link to a new example of what i'm looking for. I've added comments of the changes i'm looking for in both example sheets.

https://app.box.com/s/167gjv4hw1pa0ayvpvdo98wripxkblli

 

[mumps]

Let's try a different approach that might work for you. Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your "Received" sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Enter a "Y" (uppercase) in column G and exit the cell. Each time you enter a "Y" in any cell in column G, the macro will trigger automatically. This saves you from manually running the macro each time.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("G:G")) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    Dim SAP As Range
    If Target = "Y" Then
        Set SAP = Sheets("Inventory").Range("C:C").Find(Target.Offset(0, -4).Value, LookIn:=xlValues, lookat:=xlWhole)
        If Not SAP Is Nothing Then
            SAP.Offset(0, 1) = SAP.Offset(0, 1) + Target.Offset(0, -3)
        End If
    End If
    Application.ScreenUpdating = True
End Sub

 

[NoSparks]

@ pahickham

re-writing the date, the quantity and now whether or not its already stored doesn't jibe with saying
i intend to keep everything that goes in the received list
as you'll have no record of anything other than the most recently received.

 

[pahickham]

That works perfectly! Mumps I can't thank you enough for dealing with me! I know I haven't exactly been the most clear or easiest to help, but I appreciate everything you've done!