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:-
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
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
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