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

 

[mumps]

Try:

Sub Received()
    Application.ScreenUpdating = False
    Dim LastRow As Long, srcWS As Worksheet, desWS As Worksheet, SAP As Range, foundSAP As Range
    Set srcWS = Sheets("Received")
    Set desWS = Sheets("Inventory")
    LastRow = srcWS.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    For Each SAP In srcWS.Range("C2:C" & LastRow)
        Set foundSAP = desWS.Range("C:C").Find(SAP, LookIn:=xlValues, lookat:=xlWhole)
        If Not foundSAP Is Nothing Then
            foundSAP.Offset(0, 1) = foundSAP.Offset(0, 1) + SAP.Offset(0, 1)
        End If
    Next SAP
    Application.ScreenUpdating = True
End Sub

You should also attach a link to this thread in your exelguru thread.

 

[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

Update:

Thanks for the reply Mumps I'll check it out


This is what I've got now, just missing the summation part of the code. I need to add the ws2 qty (cell D) value to the found qty (cell D) value

Sub Duplicate_Digits()


Dim ws1 As Worksheet: Set ws1 = ThisWorkbook.Sheets("Inventory")
Dim ws2 As Worksheet: Set ws2 = ThisWorkbook.Sheets("Tested")
Dim ws3 As Worksheet: Set ws3 = ThisWorkbook.Sheets("Seal Pick Up")
Dim Numbers1, Numbers2, Numbers3, i
Dim Found As Range


Numbers1 = ws1.Range("B2:B" & ws1.Range("B" & ws1.Rows.Count).End(xlUp).row).Value
Numbers2 = ws2.Range("B2:B" & ws2.Range("B" & ws2.Rows.Count).End(xlUp).row).Value
Numbers3 = ws3.Range("B2:B" & ws3.Range("B" & ws3.Rows.Count).End(xlUp).row).Value


For i = LBound(Numbers2, 1) To UBound(Numbers2, 1)
Set Found = ws1.Range("B:B").Find(Numbers2(i, 1))
If Not Found Is Nothing Then
???
End If
Set Found = Nothing
Next i


For i = LBound(Numbers3, 1) To UBound(Numbers3, 1)
Set Found = ws1.Range("B:B").Find(Numbers3(i, 1))
If Not Found Is Nothing Then
???
End If
Set Found = Nothing
Next i
End Sub


On this new one I have different sheet names and I'm now having one add to the inventory and one subtract

 

[mumps]

Perhaps you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Include a detailed explanation of what you would like to do using a few examples from your data and referring to specific cells, rows, columns and worksheets. If the workbook contains confidential information, you could replace it with generic data.

 

[pahickham]

Thanks for the help, it claims that

foundSAP.Offset(0, 1) = foundSAP.Offset(0, 1) + SAP.Offset(0, 1)

is a mismathch

 

[mumps]

Please attach a link to your file as I suggested in Post #4 .

 

[NoSparks]

@ pahickham
have you checked your post at ExcelGuru ?

 

[pahickham]

I hope that worked

https://www.dropbox.com/home/Example%20Sheet

 

[pahickham]

My bad use this one

https://www.dropbox.com/sh/8ncmkhgwm9luml7/AAAzrk1UJXcwDR2tZJlbbu2Ga?dl=0

 

[mumps]

The original macro I posted in Post# 2 will work for you. The problem is that the sheet name "Received " has a trailing space. Remove that extra space at the end of the sheet name and try the macro again.