Search for Strings Listed in Column A and Insert Offset values in B

[dj44]

Good day folks,

I have a simple issue that I cant figure out.

I would like to insert some data into cells in another worksheet


In column A - I have listed the strings. In Column B the values to be inserted

In a separate worksheet I am looking for that string and then insert on the offset.

 
   Sub Insert_into_Cells()
  
  
   
    Dim oCell As Range
    Dim i As Long
    Dim ws As Worksheet
    Dim ows As Worksheet
   
 
    Set ws = ThisWorkbook.Worksheets("Data")             ' Worksheet that contains the data
   
   
    For i = 1 To ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
 
 
 
     '------------------
     For Each oCell In ThisWorkbook.Worksheets("Offset").Range("A1:A10").Cells
   
 
        If InStr(1, oCell.Value, ws.Cells(i, "A").Value) > 0 Then
       
        oCell.Offset(, 3).Value = ws.Cells(i, "B").Value
       
   
       
       
        End If
 
   
   
    Next oCell
    Next i
   
 
 
 
 End Sub

It looks ok but it refuses to insert the data.

If some one would be kind enough to have a look at my work book.

Insert Offset Cells.xlsm


Much appreciated

Thank you

[dj44]

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.