Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > VBA array problem
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1
Default VBA array problem

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

--
cjsmith2
-----------------------------------------------------------------------
cjsmith22's Profile: http://www.excelforum.com/member.php...fo&userid=2852
View this thread: http://www.excelforum.com/showthread.php?threadid=48441

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 2,452
Default VBA array problem
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


Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 11,272
Default VBA array problem
Take a look at http://www.xldynamic.com/source/xld.LeagueTable.html

--

HTH

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


"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



Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1
Default VBA array problem

cheers fellas - RB that would be great


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

Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 2,452
Default VBA array problem
Not sure what your e-mail is.

RBS


"cjsmith22" wrote
in message ...

cheers fellas - RB that would be great


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


Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Problem with array ref (I think!) Nick H[_2_] Excel Worksheet Functions 10 June 28th 09 11:03 PM
Array problem, I think.. Steve Excel Worksheet Functions 3 June 11th 08 12:38 AM
Array problem: Key words-Variant Array, single-element, type mismatch error davidm Excel Programming 6 November 9th 05 05:54 AM
Array problem: Key words-Variant Array, single-element, type mismatch error davidm Excel Programming 1 November 8th 05 04:21 AM
Array Problem TAM Excel Programming 5 March 19th 05 08:34 AM


All times are GMT +1. The time now is 05:26 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement