"HH" wrote:

On 28 Jan., 17:03, Smallweed
wrote:
This should do it - I was a bit unsure why the E range finished at row 10
while the D range went on to 999. I'm assuming the E range is the same:

Sub DoIt()
Dim rng1 As Range
Dim rng2 As Range
Dim lngCtr As Long
For Each rng2 In Worksheets("Sheet2").Range("D2:D999").Cells
For Each rng1 In Worksheets("Sheet1").Range("B2:B5000").Cells
If InStr(rng1.Value, rng2.Value)<0 Then 'found
lngCtr=lngCtr+1

Worksheets("Sheet3").Cells(lngCtr,1).Value=rng2.Of fset(0,1).Value
End If
Next rng1
Next rng2
End Sub

This is just Perfect!
Now I feel inspired ;-)

1)
What if I want the search to begin at a certain row? I know how to
find the row number, but how do I use it in the range?

If x is the row to start in, the following amendment looks at every cell in
the range between Dx (e.g. D5 if x=5) and D999 (Cells(x,y) is row x, column
y).

For Each rng2 In Worksheets("Sheet2").Range(Cells(x, 4), Cells(999, 4).Cells

2)
The advanced and perfect solution would be to offset the sheet1 value
to another column
I can make the value returned from sheet 2 (column E) to be the number
of columns that the value should be offset - is there a way of making
this happen?


I returned the values to Sheet3, offset down a cell each time so you get a
column of results. Offset(row, col) is useful. E.g., instead of:
Worksheets("Sheet3").Cells(lngCtr,1).Value=rng2.Of fset(0,1).Value in the
above you could do:
rng1.Offset(0, rng2.Value).Value=whatever
Thanks !!!