Compare 2 columns & search for something

dgr

Board Regular
Joined
Apr 24, 2005
Messages
176
Dear All,
I've posted this message before on http://www.ozgrid.com/forum/showthread.php?t=43605 but I had no luck there.

This is my code:-
Code:
Sub surname_checker() 
     'This script searches for all words in column A which are also in column C.
     'It then cuts the found cells from column A & pastes it in column B next
     'to the searched cell in column C.
     
    Dim ws As Worksheet 
    Dim wb As Workbook 
     
    Set wb = ActiveWorkbook 
    Set ws = wb.Worksheets(1) 
     
    i = 1 
    Do 
        With ws 
            If .Cells(i, 3).Value = "" Then 
                Exit Do 
                 
            End If 
             'This is where I need help.
            Set valFound = .Columns("A").Find(.Cells(i, 3).Value, , , xlPart, MatchCase:=True) 
            If Not valFound Is Nothing Then 
                valFound.Cut .Range("B" & i & ":B" & i) 
            End If 
        End With 
        i = i + 1 
         
    Loop 
    With ws 
         
    End With 
End Sub
This is what I want to achieve:-
I would like to refine the search criteria to look for whole words within column A. I am not talking about simply changing the xlPart to xlWhole so to explain further here is an example:-

COLUMN A---------------------------------COLUMN C
1 BAKER JOHNSON--------------------------BAK
2 MICHAEL JORDAN-------------------------BAKER
3 JOHN WANAMAKER------------------------RATED
4 YAHOO INCORPORATED COMPANY------INCORPORATED
5 GOOGLE EARTH----------------------------SUN MICROSYSTEMS

In this example, I would like the script to find BAKER & INCORPORATED but it should not find BAK & RATED. Could you please help?

Additional info:-
The length of column A & C are not fixed. There could be blank spaces in both columns as well.

Thanks a lot for your advice.

ROBERT RAM
 

Some videos you may like

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

Watch MrExcel Video

Forum statistics

Threads
1,118,819
Messages
5,574,504
Members
412,599
Latest member
Schu94
Top