Issues with InStr

[neu2me]

I am trying to create a macro that looks at a string from one cell and
compares it to each cell in a list on another sheet. If the part of the
string in the list cell contains the contents in the first cell, the entire
contents of the list string is copied into a second area. The code I have is
below...

strKeyword = Sheet1.Cells(6, 2)
For lngRow = 2 To Sheet2.Cells(65536, 1).End(xlUp).Row
If InStr(strKeyword, Sheet2.Cells(lngRow, 1)) > 0 Then
Sheet1.Cells(Sheet1.Cells(65536, 2).End(xlUp).Row + 1, 2) =
Sheet2.Cells(lngRow, 1)
End If
Next lngRow

The problem I have is that the computer acts like each list item contains
the contents of my search cell... What am I missing?

 

[neu2me]

For some strange reason it still is not working... Would it matter if I used
part of a word (like "attor") to find full-length terms (like "Attorney -
Criminal" or "DUI Attorney")?

 

[JLGWhiz]

What I was trying to say before is that with InStr, unless you have a string
that is unique, not part of a word, but one that cannot be misread, there is
a possibility that it will be misread. You could add in vbTextCompare to
see if it helps any.

If InStr(strKeyword, Sheet2.Cells(lngRow, 1), vbTextCompare) > 0 Then

Again, not being able to see what you are trying to compare or what you are
comparing it to, I cannot trouble shoot the code. The basic code does what
it is supposed to do, but you could be using it in the wrong environment.