User Profile
crystalhowat
Copper Contributor
Joined 6 years ago
User Widgets
Recent Discussions
Re: Using VBA to search for a matching value on another sheet when a row is added
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 Sub85KViews0likes2CommentsUsing VBA to search for a matching value on another sheet when a row is added
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)89KViews0likes5Comments
Recent Blog Articles
No content to show