Welcome to the forums
with block variable not set (error91)
This is caused by the worksheet object not being assigned to an object.
The most efficient method of completing the task you have described would be to use VLookup functions, whether in excel cells of in code.
Set worksheet = WorkSheets(1)
Let us assume column A has all of our values that are our PLC addresses. Column F has a very long list of plc addresses.
First action in code is to check whether there is a matching plc value in out long list contained in column F
Code is untested.
Dim r As Integer ' row counter
Dim sht As worksheet
Dim dataRange As Range
Set dataRange = Range("F:F")
r = 2 'to allow for column headings
Set sht = Worksheets(1) 'first sheet in workbook
Do While sht.Cells(r, 1).Value <> ""
If WorksheetFunction.CountA(dataRange, sht.Cells(r, 1).Value) > 0 Then 'we have a matching value in the target column
sht.Cells(r, 2) = WorksheetFunction.VLookup(sht.Cells(r, 1).Value, dataRange, 1, False)
r = r + 1
Set dataRange = Nothing
Set sht = Nothing
If this was me trying to complete this task I would use VLookup in excel cells and pull down through the range manually using the formula
=IFERROR(VLOOKUP($A2, $F$F, 1, FALSE), "")
assuming the target data was contained in column F.