tricky search 2

[faxof]

hi
i have 2 columns
A:A contains a list of short names eg "rob", "chris"
B:B contains a list of long names eg "christopher jones", "robert smith"

i need a macro to check that a short name (from col A) appears in one or more of the long names (col B) if it does appear then put a flag in col C

i've used LIKE and this code so far - where am i going wrong?

Sub searchNames()
Dim searchString As String
Dim lastA As Integer
Dim lastB As Integer
lastA = Range("a65536").End(xlUp).Row
lastB = Range("b65536").End(xlUp).Row
For i = 2 To lastA
    searchString = Cells(i, 1)
    For x = 1 To lastB
        If searchString Like Cells(x, 2).Value Then
            Cells(i, 3).Value = "MATCH"
        End If
    Next x
    If Cells(i, 3).Value <> &quot;MATCH&quot; Then Cells(i, 3).Value = &quot;clear&quot;
Next i
End Sub

quizzed
faxof

 

[Zathras]

There are a couple of things that are not quite correct. The immediate problem is the use of Like. You have the syntax backwards and you do not have any wildcards for the pattern. If you change the line with "Like" to read this way:

  If Cells(x, 2).Value Like searchString & "*" Then

the routine will appear to work.

I say "appear" because if you have more than 32,768 rows in the worksheet, you need to Dim the LastA and LastB values as Long.

Also, you may need to provide a leading wildcard to the pattern as well, like this:

  If Cells(x, 2).Value Like "*" & searchString & "*" Then

Also, it seems inconsistent to look for short names starting with row 1 and long names starting with row 2.

In any event, I would recommend that you scrap the routine and use this instead:

Sub SearchNames2()
Dim ShortNames As Range
Dim LongNames As Range
Dim c As Range
Dim Found As Range
  Set ShortNames = Intersect(ActiveSheet.UsedRange, Range("a:a"))
  Set LongNames = Intersect(ActiveSheet.UsedRange, Range("b:b"))
  For Each c In ShortNames
    Set Found = LongNames.Find(c.Value, , xlValues, xlPart)
    If Found Is Nothing Then
      c.Offset(0, 2) = "clear"
    Else
      c.Offset(0, 2) = "MATCH"
    End If
  Next c
  Set Found = Nothing
  Set ShortNames = Nothing
  Set LongNames = Nothing
End Sub

It takes a little while to learn to live with "object oriented" coding, but it is worth the effort. Usually it results in cleaner code.

There may be a larger problem, however. Either routine will find out whether there is at least one entry in column B that matches the entry in column A. But neither routine addresses the issue of multiple matches for the same short name.

 

[TonyJollans]

Why a macro? The following formula in Column C will do just as well.

=IF(ISERROR(FIND(A1,B1)),"clear","MATCH&quot

Enjoy,
Tony

 

[faxof]

Tony

I'm alwys up for trying to find a more simple solution (I usually fall into the code trap before trying a formla).

The formula you suggest only writes "MATCH" if the 'match' is on the same row, I need it to search the whole column

Could your formula possibly work with a named range?

Thanx for your suggestion
Fax

 

[TonyJollans]

I should read the posts more carefully. My gut feeling is there ought to be a way but I have to admit to not knowing what it might be.