VBA array problem

[cjsmith22]

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

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

 

[sweater_vests_rock]

cjsmith.

you can load a range into an array quite simply (see the below code for an example). so all you need to do is have a range which contains the teams names, then you could loop through your code using the array.

Sub RangeToArray()

Dim MyArray() as Variant
Dim MyRange as Range

Set MyRange = Range("A1:A3")

For i = 1 to MyRange.Rows.Count
   Cells(i, 1).Value = i
Next i

MyArray = MyRange

For i = LBound(MyArray, 1) to UBound(MyArray, 1)
   MsgBox MyArray(i,1)
Next i

End Sub

hope this helps. ben.

 

[cjsmith22]

cheers ben !

 

[erik.van.geit]

Hi,
for your information
you don't need to loop if you want to find a value in a range: just use FIND (or MATCH)
example (not tested, but will be close)

Set c = myteams.Find("Arsenal", lookat:=xlWhole)
If Not c Is Nothing Then
With c
    .Offset(0, 1) = .Offset(0, 1) + myplayed
    .Offset(0, 7) = .Offset(0, 7) + mywonA
    .Offset(0, 8) = .Offset(0, 8) + mydrawnA
    .Offset(0, 9) = .Offset(0, 9) + mylostA
    .Offset(0, 10) = .Offset(0, 10) + myforA
    .Offset(0, 11) = .Offset(0, 11) + myagainstA
    .Offset(0, 12) = .Offset(0, 5) + .Offset(0, 10) - .Offset(0, 6) - .Offset(0, 11)
    .Offset(0, 13) = .Offset(0, 13) + mypointsA
End With

see also the use of "with"

kind regards,
Erik

 

[sweater_vests_rock]

erik.

fantastic point. thanks for the reminder!

ben.