Well, after some investigating I found I can load the cells into an array.




   Sub Insert_into_Offset_Cells()
    
    Dim oCell As Range
    Dim i As Long
    Dim vSearch As Variant
    Dim vReplace As Variant
     



    '---------------------------------------
    'Load the Values into Arrays
    
    vSearch = ThisWorkbook.Worksheets("Data").Range("A1:A3").Value
    vReplace = ThisWorkbook.Worksheets("Data").Range("B1:B3").Value
    
    
    i = 1
   
    While i <= UBound(vSearch)
    
    
        For Each oCell In ThisWorkbook.Worksheets("offset").Range("A1:A8").Cells


        If InStr(1, oCell.Value, vSearch(i, 1)) > 0 Then

        oCell.Offset(, 2).Value = vReplace(i, 1)

        End If



    Next oCell
    i = i + 1
    Wend
    End Sub

It does the job, so i guess its ok. I dont know why the other one didnt work.

I see how i get on with this one.