this updates a league table based on results.

Code:
--------------------
For Each myLeague In myLeague

Set C = myResults.Find(myLeague, lookat:=xlWhole)
If Not C Is Nothing Then
With C
myfor = C.Offset(0, 1)
myagainst = C.Offset(0, 5)
mygd = myfor - myagainst
If myfor myagainst Then
mywon = 1
mypoints = 3
myplayed = 1
End If
If myfor < myagainst Then
mylost = 1
myplayed = 1
End If
If myfor = myagainst Then
mydrawn = 1
mypoints = 1
myplayed = 1
End If
End With
End If

Set C = myLeague.Find(myLeague, lookat:=xlWhole)
If Not C Is Nothing Then
With C
C.Offset(0, 1) = C.Offset(0, 1) + myplayed
C.Offset(0, 2) = C.Offset(0, 2) + mywon
C.Offset(0, 3) = C.Offset(0, 3) + mydrawn
C.Offset(0, 4) = C.Offset(0, 4) + mylost
C.Offset(0, 5) = C.Offset(0, 5) + myfor
C.Offset(0, 6) = C.Offset(0, 6) + myagainst
C.Offset(0, 12) = C.Offset(0, 5) + C.Offset(0, 10) - C.Offset(0, 6) - C.Offset(0, 11)
C.Offset(0, 13) = C.Offset(0, 13) + mypoints
myplayed = 0
mywon = 0
mydrawn = 0
mylost = 0
myfor = 0
myagainst = 0
mypoints = 0

End With
End If

--------------------

the problem is that it stops when it finds the first correct match of
myLeague in myResults. how can i get it to find more than one correct
match?
eg. myLeague = "chelsea" , it finds only the first instance of myLeague
in myResults, i need it to look to see if chelsea occurs more than once
in myResults


--
cjsmith22
------------------------------------------------------------------------
cjsmith22's Profile: http://www.excelforum.com/member.php...o&userid=28528
View this thread: http://www.excelforum.com/showthread...hreadid=485753

There is a a good example of the FindNext Method in VBA help.

Regards
Rowan

cjsmith22 wrote:
this updates a league table based on results.

Code:
--------------------
For Each myLeague In myLeague

Set C = myResults.Find(myLeague, lookat:=xlWhole)
If Not C Is Nothing Then
With C
myfor = C.Offset(0, 1)
myagainst = C.Offset(0, 5)
mygd = myfor - myagainst
If myfor myagainst Then
mywon = 1
mypoints = 3
myplayed = 1
End If
If myfor < myagainst Then
mylost = 1
myplayed = 1
End If
If myfor = myagainst Then
mydrawn = 1
mypoints = 1
myplayed = 1
End If
End With
End If

Set C = myLeague.Find(myLeague, lookat:=xlWhole)
If Not C Is Nothing Then
With C
C.Offset(0, 1) = C.Offset(0, 1) + myplayed
C.Offset(0, 2) = C.Offset(0, 2) + mywon
C.Offset(0, 3) = C.Offset(0, 3) + mydrawn
C.Offset(0, 4) = C.Offset(0, 4) + mylost
C.Offset(0, 5) = C.Offset(0, 5) + myfor
C.Offset(0, 6) = C.Offset(0, 6) + myagainst
C.Offset(0, 12) = C.Offset(0, 5) + C.Offset(0, 10) - C.Offset(0, 6) - C.Offset(0, 11)
C.Offset(0, 13) = C.Offset(0, 13) + mypoints
myplayed = 0
mywon = 0
mydrawn = 0
mylost = 0
myfor = 0
myagainst = 0
mypoints = 0

End With
End If

--------------------

the problem is that it stops when it finds the first correct match of
myLeague in myResults. how can i get it to find more than one correct
match?
eg. myLeague = "chelsea" , it finds only the first instance of myLeague
in myResults, i need it to look to see if chelsea occurs more than once
in myResults

thanks Rowan - trying to use


Code:
--------------------
Set c = mynewResultsH.Find(myteams, lookat:=xlWhole)
If Not c Is Nothing Then
With c
myfor = c.Offset(0, 1)
myagainst = c.Offset(0, 5)
mygd = myfor - myagainst
If myfor myagainst Then
mywon = 1
mypoints = 3
myplayed = 1
End If
If myfor < myagainst Then
mylost = 1
myplayed = 1
End If
If myfor = myagainst Then
mydrawn = 1
mypoints = 1
myplayed = 1
End If
firstaddress = c.Address
Do
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < firstaddress

End With
End If
--------------------

but says object variable or with variable not set - any ideas?


--
cjsmith22
------------------------------------------------------------------------
cjsmith22's Profile: http://www.excelforum.com/member.php...o&userid=28528
View this thread: http://www.excelforum.com/showthread...hreadid=485753

Without seeing all your code I would guess that the problem may be that
mynewResultsH is not set to be a valid range.

Hope this helps or post more (or preferably all) of your code.
Rowan

cjsmith22 wrote:
thanks Rowan - trying to use


Code:
--------------------
Set c = mynewResultsH.Find(myteams, lookat:=xlWhole)
If Not c Is Nothing Then
With c
myfor = c.Offset(0, 1)
myagainst = c.Offset(0, 5)
mygd = myfor - myagainst
If myfor myagainst Then
mywon = 1
mypoints = 3
myplayed = 1
End If
If myfor < myagainst Then
mylost = 1
myplayed = 1
End If
If myfor = myagainst Then
mydrawn = 1
mypoints = 1
myplayed = 1
End If
firstaddress = c.Address
Do
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < firstaddress

End With
End If
--------------------

but says object variable or with variable not set - any ideas?