Jun 29 2020 11:27 AM - edited Jun 29 2020 01:03 PM
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)
Jun 29 2020 11:40 AM
@crystalhowat Your sheet is working . Can you please text me in Private to explain your actual problem. I will share my mail id there
Jun 29 2020 01:02 PM
No, it is not updating column L on the MASTER worksheet when we add a row to the GR worksheet.
Jun 29 2020 03:09 PM
Here is where I am currently at. I don't get any errors, but I'm not getting any action either:
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
Dec 02 2020 10:18 PM
Hi crystalhowat, this is probably too late to help you but I've posted this in case it is of use to others following. I made some changes so that it works for me. tt is the subscript for the transaction file and mm is the subscript for the master file. I read each row in the transaction file and search for a match on the master then update the master. 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
Aug 25 2023 03:22 AM - edited Aug 25 2023 03:22 AM
@Sydney2153 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. I think i get your method. I would be search for a Date then a Name then if it finds it update a cell