Searching entire list of values in one sheet in another sheet and not found error help

[ExcelTze]

Hi everyone,

My first post here so please bear with me! I'm a total excel noob and have muddled through my coding needs using youtube and finding answered similar problems other people have had on this website and I've managed to muddle my way through just about.... until now

Basically what I'm trying to do is search for all the values that are listed in a column in sheet1, and find the matching values in column in sheet2, then pull the adjacent cell values from sheet2 to output it back into sheet1. If I explained it in a way that remotely makes any sense?

At first my code would only find the first value from the sheet1 list before stopping...
- So I've clumsily tried to add a loop.
- Then it would find the next few values but then stop with an error if it didn't find a match
- So I've most probably wrongly just said on error resume next. This allows it to work its way through the whole column but now I get incorrect values next to the not found items.

I can't seem to find a way to deal with the not found items, while still retaining the ability to work its way through the list...

Public Sub FindStaffIDSheets()

On Error Resume Next

'find StaffID from Tasking Number
Dim foundRng As Range
Dim Task As String
Dim rownumber As Long

'loop attempt
Dim i As Integer

For i = 3 To 77

Task = ThisWorkbook.Worksheets("sheet1").Cells(i, 2)

Set foundRng = ThisWorkbook.Worksheets("sheet2").Columns("E:E").Find(What:=Task, _
LookIn:=xlFormulas, lookat:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=True, SearchFormat:=False)

rownumber = rng.Row

'output the result
ThisWorkbook.Worksheets("sheet1").Cells(i, 3).Value = ThisWorkbook.Worksheets("sheet2").Cells(rownumber, 7).Value
Next i

End Sub

After some googling I found this code below I tried to work in but I kept getting a highlight on Next i and no For error..

If Nothing Then
'''''''''
Else
''''''''''
End If

I think I've gotten as far as a I can with my google-fu so any help would be most greatly appreciated!!

 

[kevin9999]

Perhaps

Option Explicit
Sub ExcelTze()
    Dim ws1 As Worksheet, ws2 As Worksheet
    Set ws1 = Worksheets("Sheet1")
    Set ws2 = Worksheets("Sheet2")
    
    Dim c As Range, rng As Range, fnd As Range
    Set rng = ws2.Range("E2", ws2.Cells(Rows.Count, "E").End(xlUp))
    
    For Each c In ws1.Range("B3", ws1.Cells(Rows.Count, "B").End(xlUp))
        If c <> "" Then
            Set fnd = rng.Find(c.Value, , xlFormulas, xlWhole)
            If Not fnd Is Nothing Then
                c.Offset(, 1).Value = fnd.Offset(, 2).Value
            End If
        End If
    Next c
End Sub

 

[ExcelTze]

Worked brilliantly! Thank you so much!

 

[kevin9999]

Worked brilliantly! Thank you so much!

Happy it worked as you wanted, and thanks for the feedback