problem with FIND

cjsmith22

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

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
Have you looked at the .FindNext Method? That will continue a search that was begun with the Find method.

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
Do
Set c = .FindNext(c)

End With
End If``````
but says object variable or with variable not set - any ideas?

Replies
1
Views
331
Replies
13
Views
763
Replies
2
Views
394
Replies
1
Views
287
Replies
3
Views
407

1,216,540
Messages
6,131,255
Members
449,638
Latest member
ygdalvi

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back