I'm trying to find and replace cells by looking at a value from one sheet, and replacing it with a value from another sheet.
I'm currently modifying a code from my last thread by jolivanes (thank you!)
I think I'm running into an issue using this code because some of the cells are combined, so it's not properly searching down the column. But I'm not sure.
The code should:
1. Find a name from (Sheet 1 column A) in (Sheet 2 column E)
2. If it's found, then replace (Sheet 2 Column K, same row) with the value from (Sheet 1 column B, same row)
Dim s1 As Worksheet
Dim s2 As Worksheet
Dim al As Range
Set s1 = Worksheets("Sheet1")
Set s2 = Worksheets("Sheet2")
For Each al In s1.Range("A2:A" & s1.Cells(Rows.Count, 1).End(xlUp).Row)
If WorksheetFunction.CountIf(s2.Range("E2:E" & s2.Cells(Rows.Count, 1).End(xlUp).Row), al.Value) <> 0 Then
s2.Range("E2:E" & s2.Cells(Rows.Count, 1).End(xlUp).Row).Find(al.Value, , , 1).Offset(, 4).Value = al.Offset(, 1).Value
End If
Next al
I've attached a sample worksheet to give you an idea of how the values might look.
Bookmarks