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
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,444
Messages
5,831,658
Members
430,081
Latest member
jppilot

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top