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.

27069


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.