I have an Excel workbook that handles the English Premiership and that works
with arrays.
If you are interested I will post the file.

RBS


"cjsmith22" wrote
in message ...

i have some code which updates a football league table based on a set of
results:


Code:
--------------------
Dim myteams As Range
Dim myplayed As Integer
Dim mypoints As Integer
Dim myfor As Integer
Dim myagainst As Integer
Dim mywon As Integer
Dim mydrawn As Integer
Dim mylost As Integer
Dim mypointsA As Integer
Dim myforA As Integer
Dim myagainstA As Integer
Dim mywonA As Integer
Dim mydrawnA As Integer
Dim mylostA As Integer


Set myteams = mynewTable.Offset(2, 0).Resize(mynewTable.Rows.Count - 2,
mynewTable.Columns.Count - 13)
myteams.Activate

For Each mynewResultsH In mynewResultsH
mynewResultsH.Activate
If ActiveCell = "Arsenal" Then

myfor = mynewResultsH.Offset(0, 1)
myagainst = mynewResultsH.Offset(0, 5)
myplayed = 1
If myfor myagainst Then
mywon = 1
mypoints = 3
End If
If myfor < myagainst Then
mylost = 1
End If
If myfor = myagainst Then
mydrawn = 1
mypoints = 1
End If
End If
Next mynewResultsH
For Each myteams In myteams
myteams.Activate
If ActiveCell = "Arsenal" Then
ActiveCell.Offset(0, 1) = ActiveCell.Offset(0, 1) + myplayed
ActiveCell.Offset(0, 2) = ActiveCell.Offset(0, 2) + mywon
ActiveCell.Offset(0, 3) = ActiveCell.Offset(0, 3) + mydrawn
ActiveCell.Offset(0, 4) = ActiveCell.Offset(0, 4) + mylost
ActiveCell.Offset(0, 5) = ActiveCell.Offset(0, 5) + myfor
ActiveCell.Offset(0, 6) = ActiveCell.Offset(0, 6) + myagainst
ActiveCell.Offset(0, 12) = ActiveCell.Offset(0, 5) + ActiveCell.Offset(0,
10) - ActiveCell.Offset(0, 6) - ActiveCell.Offset(0, 11)
ActiveCell.Offset(0, 13) = ActiveCell.Offset(0, 13) + mypoints
End If
Next myteams


For Each mynewResultsA In mynewResultsA
mynewResultsA.Activate
If mynewResultsA = "Arsenal" Then

myforA = mynewResultsA.Offset(0, 1)
myagainstA = mynewResultsA.Offset(0, -1)


myplayed = 1
If myforA myagainstA Then
mywonA = 1
mypointsA = 3
End If
If myforA < myagainstA Then
mylostA = 1
End If
If myforA = myagainstA Then
mydrawnA = 1
mypointsA = 1
End If
End If
Next mynewResultsA
Set myteams = mynewTable.Offset(2, 0).Resize(mynewTable.Rows.Count - 2,
mynewTable.Columns.Count - 13)
myteams.Activate
For Each myteams In myteams
myteams.Activate
If ActiveCell = "Arsenal" Then
ActiveCell.Offset(0, 1) = ActiveCell.Offset(0, 1) + myplayed
ActiveCell.Offset(0, 7) = ActiveCell.Offset(0, 7) + mywonA
ActiveCell.Offset(0, 8) = ActiveCell.Offset(0, 8) + mydrawnA
ActiveCell.Offset(0, 9) = ActiveCell.Offset(0, 9) + mylostA
ActiveCell.Offset(0, 10) = ActiveCell.Offset(0, 10) + myforA
ActiveCell.Offset(0, 11) = ActiveCell.Offset(0, 11) + myagainstA
ActiveCell.Offset(0, 12) = ActiveCell.Offset(0, 5) + ActiveCell.Offset(0,
10) - ActiveCell.Offset(0, 6) - ActiveCell.Offset(0, 11)
ActiveCell.Offset(0, 13) = ActiveCell.Offset(0, 13) + mypointsA
End If
Next myteams
--------------------


with this i will have to go through the above code for every instance
of a team name (i.e If ActiveCell = "Arsenal" Then...., If ActiveCell =
"Blackburn" Then....etc.)
is there any way of putting all of the teams in an array and looping on
them from there, so rather than saying -
If ActiveCell = "Arsenal" Then....
it would say something like - If ActiveCell = "whatever's first in the
array" Then.... ??


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