Array Concepts - Nested Arrays

[Coach3131]

I am doing a self project to strengthen my array concepts so that I can take the next step in programming. I have a project that I have completed and working using a hobby, but I know it isn't condensed in best form yet because I feel that with full use of arrays, it can be tighter.

The simplified version is this:
I am wanting to run through a sheet that has multiple teams (array 1), multiple positions (array 2), and a depth chart of players, multiple players (array 3).... I am wanting to compare it to sheet2, which is another sheet and log the changes... this is working as I have it now, but the code is really long and cumbersome and feel that it is best to see if there is a simplified loop that I can use.

For this example, I will say that there are 15 teams, 3 positions, and a variable depth of 3 to 15 player names for each position.

For Teams, use Team01, Team02, Team03, etc. (this is static)
For Positions use Pos01, Pos02, Pos03, etc. (this is static)
For PlayerDepth use Depth01, Depth02, Depth03, etc. (this is variable)
For PlayerName use Player01, Player02, Player03, etc. (this is variable, but same index as depth)

First, I would need to populate these arrays... so if Team01, has Pos01 with a Depth of 3 (three players deep at the position), what is Player01, Player02, Player03... then compare that to another set of arrays like this except with the old in front of it to track movement. Basically if player01 moves from Depth02 to Depth01 for Pos01 on Team01, I would like to log that data.

 

[ChrisM]

Not really sure what your question is without seeing some code, but I do have one observation.

Given:
For PlayerDepth use Depth01, Depth02, Depth03, etc. (this is variable)
For PlayerName use Player01, Player02, Player03, etc. (this is variable, but same index as depth)

essentially count of PlayerName is the same as Depth, so I think you can eliminate the depth dimension. If you have two player names, then depth = 2. Three player names, depth = 3. You can just calculate it on the fly, something like Ubound(myArray,1).

 

[Coach3131]

I have one sheet filled with team depth charts and another sheet filled with team depth charts. I am wanting to compare one sheet to another and log the changes. For example, that a player01 moved from Pos01(Depth02) to Pos(Depth01) and is now a starter. Basically, logging every change. Sheet2 will be the last run depth chart, Sheet1 will be the most current one and the objective is for all teams to log the change in depth.

I am trying to condense the following code, which is a snippet of it.... and all of it is really long because the arrays aren't efficient.

Sub CheckQBs()

Dim TeamQB01(1 To 32) As String
Dim TeamQB02(1 To 32) As String
Dim TeamQB03(1 To 32) As String
Dim TeamQB04(1 To 32) As String
Dim TeamQB05(1 To 32) As String
Dim TeamQB06(1 To 32) As String
Dim TeamQB07(1 To 32) As String
Dim TeamQB08(1 To 32) As String
Dim TeamQB09(1 To 32) As String
Dim TeamQB10(1 To 32) As String
Dim TeamQB11(1 To 32) As String
Dim TeamQB12(1 To 32) As String
Dim TeamQB13(1 To 32) As String
Dim TeamQB14(1 To 32) As String
Dim TeamQB15(1 To 32) As String

Dim oTeamQB01(1 To 32) As String
Dim oTeamQB02(1 To 32) As String
Dim oTeamQB03(1 To 32) As String
Dim oTeamQB04(1 To 32) As String
Dim oTeamQB05(1 To 32) As String
Dim oTeamQB06(1 To 32) As String
Dim oTeamQB07(1 To 32) As String
Dim oTeamQB08(1 To 32) As String
Dim oTeamQB09(1 To 32) As String
Dim oTeamQB10(1 To 32) As String
Dim oTeamQB11(1 To 32) As String
Dim oTeamQB12(1 To 32) As String
Dim oTeamQB13(1 To 32) As String
Dim oTeamQB14(1 To 32) As String
Dim oTeamQB15(1 To 32) As String

Dim NFLTeam() As Variant

NFLTeam = Array("BAL", "BUF", "CIN", "CLE", "DEN", "HOU", "IND", "JAX", "KCC", "MIA", "NEP", "NYJ", "OAK", "PIT", "SDC", "TEN", "ARI", "ATL", "CAR", "CHI", "DAL", "DET", "GBP", "MIN", "NOS", "NYG", "PHI", "STL", "SFO", "SEA", "TBB", "WAS")

Dim iCount As Integer

Sheets("CurrentDepthChart").Select

'For Each NFLTeam In Array(BAL, BUF, CIN, CLE, DEN, HOU, IND, JAX, KCC, MIA, NEP, NYJ, OAK, PIT, SDC, TEN, ARI, ATL, CAR, CHI, DAL, DET, GBP, Min, NOS, NYG, PHI, STL, SFO, SEA, TBB, WAS)
        For iCount = 1 To 32
        c = ((iCount - 1) * 12) + 2
        TeamQB01(iCount) = Range("B" & c).Value
        TeamQB02(iCount) = Range("C" & c).Value
        TeamQB03(iCount) = Range("D" & c).Value
        TeamQB04(iCount) = Range("E" & c).Value
        TeamQB05(iCount) = Range("F" & c).Value
        TeamQB06(iCount) = Range("G" & c).Value
        TeamQB07(iCount) = Range("H" & c).Value
        TeamQB08(iCount) = Range("I" & c).Value
        TeamQB09(iCount) = Range("J" & c).Value
        TeamQB10(iCount) = Range("K" & c).Value
        TeamQB11(iCount) = Range("L" & c).Value
        TeamQB12(iCount) = Range("M" & c).Value
        TeamQB13(iCount) = Range("N" & c).Value
        TeamQB14(iCount) = Range("O" & c).Value
        TeamQB15(iCount) = Range("P" & c).Value
        Next iCount
 '   Next NFLTeam

Sheets("LastRunDepthChart").Select

'For Each NFLTeam In Array(BAL, BUF, CIN, CLE, DEN, HOU, IND, JAX, KCC, MIA, NEP, NYJ, OAK, PIT, SDC, TEN, ARI, ATL, CAR, CHI, DAL, DET, GBP, Min, NOS, NYG, PHI, STL, SFO, SEA, TBB, WAS)
        For pCount = 1 To 32
        c = ((pCount - 1) * 12) + 2
        oTeamQB01(pCount) = Range("B" & c).Value
        oTeamQB02(pCount) = Range("C" & c).Value
        oTeamQB03(pCount) = Range("D" & c).Value
        oTeamQB04(pCount) = Range("E" & c).Value
        oTeamQB05(pCount) = Range("F" & c).Value
        oTeamQB06(pCount) = Range("G" & c).Value
        oTeamQB07(pCount) = Range("H" & c).Value
        oTeamQB08(pCount) = Range("I" & c).Value
        oTeamQB09(pCount) = Range("J" & c).Value
        oTeamQB10(pCount) = Range("K" & c).Value
        oTeamQB11(pCount) = Range("L" & c).Value
        oTeamQB12(pCount) = Range("M" & c).Value
        oTeamQB13(pCount) = Range("N" & c).Value
        oTeamQB14(pCount) = Range("O" & c).Value
        oTeamQB15(pCount) = Range("P" & c).Value
        Next pCount
 '   Next NFLTeam

Sheets("DepthChartChangeLog").Select
x = 0

'For Each NFLTeam In Array(BAL, BUF, CIN, CLE, DEN, HOU, IND, JAX, KCC, MIA, NEP, NYJ, OAK, PIT, SDC, TEN, ARI, ATL, CAR, CHI, DAL, DET, GBP, Min, NOS, NYG, PHI, STL, SFO, SEA, TBB, WAS)
For cCount = 1 To 32
        y = 1
        x = 0
        
        x = x + 1

        If TeamQB01(cCount) <> oTeamQB01(cCount) And oTeamQB01(cCount) <> "" Then
            For Each TeamQBPos In Array(TeamQB01(cCount), TeamQB02(cCount), TeamQB03(cCount), TeamQB04(cCount), TeamQB05(cCount), TeamQB06(cCount), TeamQB07(cCount), TeamQB08(cCount), TeamQB09(cCount), TeamQB10(cCount), TeamQB11(cCount), TeamQB12(cCount), TeamQB13(cCount), TeamQB14(cCount), TeamQB15(cCount))
                If oTeamQB01(cCount) = TeamQBPos Then
                    If x < 10 Then
                    OldPos = "QB0" & x
                    Else
                    OldPos = "QB" & x
                    End If
                    If y < 10 Then
                    NewPos = "QB0" & y
                    Else
                    NewPos = "QB" & y
                    End If
                    'MsgBox (oTeamQB01(cCount) & " was " & Pos & " and is now in posititon " & NewPos & " with " & NFLTeam(cCount - 1))
                    Sheets("DepthChartChangeLog").Select
                    r = ActiveSheet.UsedRange.Rows.Count + 1
                    Range("A" & r).Value = NFLTeam(cCount - 1)
                    Range("B" & r).Value = oTeamQB01(cCount)
                    Range("C" & r).Value = NewPos
                    Range("D" & r).Value = OldPos
                    If y < 2 Then
                        Range("E" & r).Value = "New Starter"
                    End If
                    If x < 2 And y > 1 Then
                        Range("E" & r).Value = "Lost Starter"
                    End If
                    If y > 1 And x - y > 0 And Range("E" & r).Value = "" Then
                        Range("E" & r).Value = "Bench Upgrade"
                    End If
                    If y > 1 And x - y < 0 And Range("E" & r).Value = "" Then
                        Range("E" & r).Value = "Bench Downgrade"
                    End If
                    Range("F" & r).Value = "=Today()"
                Else
                y = y + 1
                End If
            Next TeamQBPos
        End If

        y = 1
        x = x + 1

        If TeamQB02(cCount) <> oTeamQB02(cCount) And oTeamQB02(cCount) <> "" Then
            For Each TeamQBPos In Array(TeamQB01(cCount), TeamQB02(cCount), TeamQB03(cCount), TeamQB04(cCount), TeamQB05(cCount), TeamQB06(cCount), TeamQB07(cCount), TeamQB08(cCount), TeamQB09(cCount), TeamQB10(cCount), TeamQB11(cCount), TeamQB12(cCount), TeamQB13(cCount), TeamQB14(cCount), TeamQB15(cCount))
                If oTeamQB02(cCount) = TeamQBPos Then
                    If x < 10 Then
                    OldPos = "QB0" & x
                    Else
                    OldPos = "QB" & x
                    End If
                    If y < 10 Then
                    NewPos = "QB0" & y
                    Else
                    NewPos = "QB" & y
                    End If
                    'MsgBox (oTeamQB02(cCount) & " was " & Pos & " and is now in posititon " & NewPos & " with " & NFLTeam(cCount - 1))
                    Sheets("DepthChartChangeLog").Select
                    r = ActiveSheet.UsedRange.Rows.Count + 1
                    Range("A" & r).Value = NFLTeam(cCount - 1)
                    Range("B" & r).Value = oTeamQB02(cCount)
                    Range("C" & r).Value = NewPos
                    Range("D" & r).Value = OldPos
                    If y < 2 Then
                        Range("E" & r).Value = "New Starter"
                    End If
                    If x < 2 And y > 1 Then
                        Range("E" & r).Value = "Lost Starter"
                    End If
                    If y > 1 And x - y > 0 And Range("E" & r).Value = "" Then
                        Range("E" & r).Value = "Bench Upgrade"
                    End If
                    If y > 1 And x - y < 0 And Range("E" & r).Value = "" Then
                        Range("E" & r).Value = "Bench Downgrade"
                    End If
                    Range("F" & r).Value = "=Today()"
                Else
                y = y + 1
                End If
            Next TeamQBPos
        End If


        y = 1
        x = x + 1

This code continues to TeamQB03 all the way to TeamQB15. For now it is set as 15, but looking to redim and set that and condense this with arrays, which requires me to get a stronger grasp with arrays.

 

[Coach3131]

Anyone with a bit more experience willing to help teach someone new concepts?

 

[Coach3131]

Looks like this thread dropped a bit so picking it back up.

 

[Coach3131]

bump again

 

[Coach3131]

bump again

 

[Coach3131]

Still trying... persistence has to mean something

 

[mikerickson]

Mmmm. I'm not entirely clear what you are trying to achieve. BUT... It sounds like custom Classes would be a better approach than jagged arrays.

The conseptual picture "Every team has a variety of positions and Every postion has many "depths' " shouts for Object oriented programming.

Try this link http://www.cimaware.com/resources/article_39.html

 

[Coach3131]

Thanks for the link...

That said, I don't have a full grasp of the array concept so adding in objects, it will help me to see an example. I am tracking a log that shows changes in the depth chart. So, if QB WhatsHisName goes from QB02 to QB01, it tells me the team, name, his old status (QB02) and his new status (QB01) and in the status column would log "New Starter" and then log today's date. Basically, what this is doing is checking old depth charts to the current depth charts and logging all changes. I have this working and it is working fine... but it is very code intensive as you can see with just a sample... it is like that for 15 depths deep for all positions. I am trying to consolidate the code and learn new concepts, first get a deeper understanding of the array concept and now to see how it would be used in object form. I am not a programmer by nature, just learning. I would like to see how someone more refined and a professional would go about this concept by showing a sample that can be built on and help me also learn the concepts and advance my programming knowledge.

There are two sheets:
"Current Depth Chart"
"Previous Depth Chart"

The current depth chart is populated with a web query plus a little bit of data cleanup. The previous depth chart is the old data from the current depth chart.

On each sheet, are the positions... 12 rows make up a team's 11 positions being tracked along with the team name... the columns are the depth so B would be Pos01, C would be Pos02, etc.