Hi,

Looking to find the main location of a event.

Here's some examples of types of entries and the expected results

_CRITERIA
Chelsea
Liverpool
Manchester Utd
Old Trafford
Cardiff
Scotland
Italy
England
Greece

FA Cup Semi Final Chelsea v Liverpool @ Old Trafford = Result Ol
Trafford
FA Cup Semi Final Chelsea v Liverpool @ Old Trafford = Result Ol
Trafford
MOTD Liverpool v Manchester Utd = Result Liverpool
Concert Cardiff = Result Cardiff
Six Nations Ireland V England = Result Ireland
Six Nations Scotland V Italy = Result Scotland
World Rally Greece = Result Greece


I got the below code at the moment which finds the first team. Anyon
got any ideas to deal with the @ senario's ??



Code
-------------------

Dim SrchRange, SrchCriteria As Object
Dim ChckName As Object

Sub wholeprocedure()

Set SrchRange = Range(Cells(2, 10), Cells(8, 10))
For Each SrchCriteria In SrchRange
If SrchCriteria = blank Then
Exit Sub
Else
Call Checkcell
End If

Next

End Sub



Sub Checkcell()

With Worksheets(1).Range("B7:B500")

Set ChckName = .Find(SrchCriteria, LookIn:=xlValues)
If Not ChckName Is Nothing Then
firstAddress = ChckName.Address
Do
Position = ChckName.Offset(0, 3).Range("A1").Value
If Position = blank Then
ChckName.Offset(0, 2).Range("A1").Value = SrchCriteria
ChckName.Offset(0, 3).Range("A1").Value = Application.WorksheetFunction.Find(SrchCriteria, ChckName, 1)
ElseIf Application.WorksheetFunction.Find(SrchCriteria, ChckName, 1) < Position Then
ChckName.Offset(0, 2).Range("A1").Value = SrchCriteria
ChckName.Offset(0, 3).Range("A1").Value = Application.WorksheetFunction.Find(SrchCriteria, ChckName, 1)
End If
Set ChckName = .FindNext(ChckName)
Loop While Not ChckName Is Nothing And ChckName.Address < firstAddress
End If
End With

End Su
-------------------


Thanks in Advance

VBA Noo

--
VBA Noo
-----------------------------------------------------------------------
VBA Noob's Profile: http://www.excelforum.com/member.php...fo&userid=3383
View this thread: http://www.excelforum.com/showthread.php?threadid=53786