VBA array problem

C

cjsmith22

i have some code which updates a football league table based on a set o
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 myteam
-------------------


with this i will have to go through the above code for every instanc
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 o
them from there, so rather than saying -
If ActiveCell = "Arsenal" Then....
it would say something like - If ActiveCell = "whatever's first in th
array" Then.... ?
 
R

RB Smissaert

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
 
B

Bob Phillips

Take a look at http://www.xldynamic.com/source/xld.LeagueTable.html

--

HTH

RP
(remove nothere from the email address if mailing direct)


cjsmith22 said:
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) +
Click to expand...
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) +
Click to expand...
ActiveCell.Offset(0, 10) - ActiveCell.Offset(0, 6) - ActiveCell.Offset(0,
11)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

problem with FIND 3
Copy filtered data to sheet 2 1
Loop Repeats - PLS HELP 2
problem with macro - its case sensitive! - help please 8
"Object Library Invalid" Error occurring...why? 1
How to use the worksheetfunction Vlookup 7
Almost there with procedure error 10
Userform-allow numerical input? 2

Top