problem with FIND

cjsmith22

New Member
Joined
Nov 2, 2005
Messages
22
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
 

Some videos you may like

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

Tazguy37

MrExcel MVP
Joined
May 28, 2004
Messages
4,237
Have you looked at the .FindNext Method? That will continue a search that was begun with the Find method.
 

cjsmith22

New Member
Joined
Nov 2, 2005
Messages
22
thanks tazguy - 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?
 

Watch MrExcel Video

Forum statistics

Threads
1,119,144
Messages
5,576,336
Members
412,718
Latest member
dragosm
Top