Using VBA to search for a matching value on another sheet when a row is added

Question

In a single workbook, I have one sheet that is our MASTER list of inventory items. On another sheet (called GR) where we log when new inventory is received.

When a new item is added to the GR table, I want a VBA to search for the matching item by SKU on the MASTER worksheet and update the number of inventory in stock based on the value we received (on the GR table)

I'm pretty new to VBA so I am just stumped.

A copy of my workbook is attached. Module 1 is the one that I have some code I found on another forum and I was trying to update it, but it's above my head. Please help me! (Edited Module Number)

sydney2153 · Answer

Hi&nbsp;crystalhowat, this is probably too late to help you but I've posted this in case it is of use to others following.&nbsp; I made some changes so that it works for me.&nbsp; tt is the subscript for the transaction file and mm&nbsp;is the subscript for the master file.&nbsp; I read each row in the transaction file and search for a match on the master then update the master.&nbsp; The code below is only sample code not my full processing.Sub FindValues()

Dim lookUpSheet As Worksheet, updateSheet As Worksheet
Dim valueToSearch As String
Dim tt As Integer, mm As Integer
Dim newstock As Integer
Dim instock As Integer

Set lookUpSheet = Worksheets("From LinkedIn")
Set updateSheet = Worksheets("Contacts_Reformatted")

'get the number of the last row with data in Contacts_Reformatted and in From LinkedIn
    lastRowLookup = lookUpSheet.Cells(Rows.Count, "A").End(xlUp).Row
    lastRowUpdate = updateSheet.Cells(Rows.Count, "A").End(xlUp).Row
    
'for every value in column A of From LinkedIn
    For tt = 1 To lastRowLookup
         valueToSearch = lookUpSheet.Cells(tt, 3)
         'look for the value in column D of Contacts_Reformatted
         For mm = 2 To lastRowUpdate
            'if found a match, copy column B value to sheet1 and proceed to the next value
            If updateSheet.Cells(mm, 4) = valueToSearch Then
    '            Set newstock = lookUpSheet.Cells(mm, 8)
    '            Set instock = updateSheet.Cells(tt, 12)
    '            updateSheet.Cells(tt, 12).Value = newstock + instock
                updateSheet.Cells(mm, 29).Value = "IRATA"
                Exit For
            End If
         Next mm
    Next tt

End Sub&nbsp;&nbsp;

devendrajain · Answer

crystalhowat&nbsp;Your sheet is working .&nbsp; Can you please text me in Private to explain your actual problem. I will share my mail id there&nbsp;

crystalhowat · Answer

DevendraJain&nbsp;No, it is not updating column L on the MASTER worksheet when we add a row to the GR worksheet.&nbsp;

crystalhowat · Answer

Here is where I am currently at. I don't get any errors, but I'm not getting any action either:&nbsp;Sub FindValues()

Dim lookUpSheet As Worksheet, updateSheet As Worksheet
Dim valueToSearch As String
Dim i As Integer, t As Integer
Dim newstock As Integer
Dim instock As Integer

Set lookUpSheet = Worksheets("GR")
Set updateSheet = Worksheets("MASTER")

'get the number of the last row with data in sheet1 and in sheet2
lastRowLookup = lookUpSheet.Cells(Rows.Count, "A").End(xlUp).Row
lastRowUpdate = updateSheet.Cells(Rows.Count, "A").End(xlUp).Row

'for every value in column A of MASTER
For i = 1 To lastRowUpdate
     valueToSearch = updateSheet.Cells(i, 1)
     'look the value in column B of GR
     For t = 2 To lastRowLookup
        'if found a match, copy column B value to sheet1 and proceed to the next value
        If lookUpSheet.Cells(t, 2) = valueToSearch Then
            Set newstock = lookUpSheet.Cells(t, 8)
            Set instock = updateSheet.Cells(i, 12)
            updateSheet.Cells(i, 12).Value = Excel.WorksheetFunction.Sum(newstock + instock)
            Exit For
        End If
     Next t
Next i

End Sub&nbsp;

slick180 · Answer

Sydney2153&nbsp;This is good work but for me i would need to seach a base list containing 1 or more rows for multiple criteria 2 or 3 then update 2 fields in two differnt sheets.&nbsp; I think i get your method.&nbsp; I would be search for a Date then a Name then if it finds it update a cell

Forum Discussion

Using VBA to search for a matching value on another sheet when a row is added

5 Replies

Resources