problem with FIND

[cjsmith22]

this updates a league table based on results.

Please Login or Register  to view this content.

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

[Rowan Drummond]

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

[cjsmith22]

thanks Rowan - trying to use

Please Login or Register  to view this content.

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

[Rowan Drummond]

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