Forum Discussion
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 Sub
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
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
