Hi Mark,
Wow... U r a real magician... The macro ran on 46000 rows in 6 minutes ...
Thanks a lot..
I may have some practical problems after running this macro..but I will have
to see what it is... I will post back if any further help is required.
Thanks a lot for all your help and the wonderful URL u have sent. I will
sure go thru it.
Rashid Khan
"Mark Thorpe" wrote in message
...
Hi Rashid -
This code should work for you. I tried to put in plenty of comments to
help
you learn more about Excel VBA:
Sub SearchNames()
Dim lRowCount As Long
Dim lRow As Long
Dim iRowAA As Integer
Dim sFullName As String
Dim sMatch As String ' match found in column AA
' Count number of used rows in the sheet:
lRowCount = ActiveSheet.UsedRange.Rows.Count
' Outer loop: look at each name in column A:
For lRow = 1 To lRowCount
sFullName = Cells(lRow, 1).Value
iRowAA = 1
sMatch = ""
' Inner loop: look at each name in column AA
' until either a match is found, or run out
' of names:
While Cells(iRowAA, 27).Value < "" And sMatch = ""
' InStr function returns value greater than
' zero if string2 is found within string1:
If InStr(sFullName, Cells(iRowAA, 27).Value) 0 Then
sMatch = Cells(iRowAA, 27).Value
End If
iRowAA = iRowAA + 1
Wend
' Place the match in column B:
Cells(lRow, 2).Value = sMatch
Next lRow
End Sub
Also, you might take a look at a series of articles called "Excel VBA: No
Experience Necessary" on TechTrax online magazine. I'm not trying to
advertise, but you might find them helpful.
http://pubs.logicalexpressions.com/P...ssue.asp?ISI=0
Mark