excel - VBA循环遍历具有嵌套for循环的列

Question

我以前从未编写过代码，但对于一个项目，我正在分析梦幻篮球统计数据以确定哪些球队会在比赛中获胜。有 9 个统计类别，总共 12 支球队，在球队 1 和 2 之间的比赛中，在一个类别中表现更好的球队得到 1，另一支球队得到 0。平局给每个 0.5。总分相加，得分多的队伍获胜。

我编写了一个宏，将团队 1 与每个类别的所有其他团队进行比较，并相应地给他们一个 1 或 0。我无法编写一个循环，然后从团队 2 开始并将其与所有其他人进行比较。我已经尝试在其他两个周围放置一个 for 循环，但我似乎无法正确抵消它或让它工作。任何帮助，将不胜感激。我的代码如下。谢谢!

Sub WhoWins()

    Dim teamAcounter As Integer
    Dim teamBcounter As Integer
    Dim teamAanswercounter As Integer
    Dim teamBanswercounter As Integer
    'these counters keep track of where we are in the stats and answers


    Dim Number1 As Single
    Dim Number2 As Single
    'these are the numbers currently being used to determine a win


    Dim answer1 As Single
    Dim answer2 As Single
    Dim split As Single
    answer1 = 1
    split = 0.5
    answer2 = 0
    'these are used to store a winning/losing/draw value in answers

    teamAanswercounter = teamBcounter + 16
    teamBanswercounter = teamAanswercounter + 1


    Dim columncounter As Integer

    teamAcounter = 3

    For columncounter = 2 to 10


        For teamBcounter = 4 To 14



            Number1 = Cells(teamAcounter, columncounter).Value
            Number2 = Cells(teamBcounter, columncounter).Value

            If Number1 > Number2 Then
                Cells(teamAanswercounter, columncounter).Value = 1 'answer1
                Cells(teamBanswercounter, columncounter).Value = 0 'answer2

            ElseIf Number2 > Number1 Then
                Cells(teamAanswercounter, columncounter).Value = 0 'answer2
                Cells(teamBanswercounter, columncounter).Value = 1 'answer1

             ElseIf Number1 = Number2 Then
                Cells(teamAanswercounter, columncounter).Value = split
                Cells(teamBanswercounter, columncounter).Value = split

            End If

            teamAanswercounter = teamAanswercounter + 3
            teamBanswercounter = teamAanswercounter + 1

        Next teamBcounter
        'teamBcounter = 4
        'teamAcounter = 3
        teamAanswercounter = teamBcounter + 1
        teamBanswercounter = teamAanswercounter + 1


    Next columncounter


End Sub

Answer 1

这是我对您需要的代码的看法。我已经评论它以帮助理解。

Sub WhoWins()

    Dim ws As Worksheet
    Dim rngTeams As Range
    Dim rngStats As Range
    Dim arrTeams As Variant
    Dim arrStats As Variant
    Dim arrResults() As Variant
    Dim TeamAIndex As Long  'Think of this as the row for Team A
    Dim TeamBIndex As Long  'Think of this as the row for Team B
    Dim StatIndex As Long   'Think of this as the column
    Dim ResultIndex As Long
    Dim NumTeams As Long
    Dim NumStats As Long

    Set ws = ThisWorkbook.Sheets("Sheet1")  'Change sheetname if necessary

    'Get the list of teams
    Set rngTeams = ws.Range("A3", ws.Range("A3").End(xlDown))

    'Get the range of statistics
    Set rngStats = rngTeams.Offset(, 1).Resize(, WorksheetFunction.CountA(ws.Rows(rngTeams.Row)) - 1)

    'Convert the ranges into arrays
    arrTeams = Application.Transpose(rngTeams.Value)
    arrStats = rngStats.Value
    NumTeams = UBound(arrTeams) - LBound(arrTeams) + 1
    NumStats = UBound(arrStats, 2) - LBound(arrStats, 2) + 1

    'Ready the results array
    ReDim arrResults(1 To WorksheetFunction.Combin(NumTeams, 2), 1 To 5)
        'arrResults columns
        '1 = TeamAName
        '2 = TeamAScore
        '3 = TeamBName
        '4 = TeamBScore
        '5 = Winner

    For TeamAIndex = LBound(arrTeams) To NumTeams - 1
        For TeamBIndex = TeamAIndex + 1 To NumTeams
            ResultIndex = ResultIndex + 1
            arrResults(ResultIndex, 1) = arrTeams(TeamAIndex)
            arrResults(ResultIndex, 2) = 0
            arrResults(ResultIndex, 3) = arrTeams(TeamBIndex)
            arrResults(ResultIndex, 4) = 0
            For StatIndex = LBound(arrStats, 2) To UBound(arrStats, 2)
                If arrStats(TeamAIndex, StatIndex) > arrStats(TeamBIndex, StatIndex) Then
                    'Team A wins the stat
                    arrResults(ResultIndex, 2) = arrResults(ResultIndex, 2) + 1
                ElseIf arrStats(TeamBIndex, StatIndex) > arrStats(TeamAIndex, StatIndex) Then
                    'Team B wins the stat
                    arrResults(ResultIndex, 4) = arrResults(ResultIndex, 4) + 1
                Else
                    'Tie
                    arrResults(ResultIndex, 2) = arrResults(ResultIndex, 2) + 0.5
                    arrResults(ResultIndex, 4) = arrResults(ResultIndex, 4) + 0.5
                End If
            Next StatIndex
            If arrResults(ResultIndex, 2) > arrResults(ResultIndex, 4) Then
                'Team A Wins the game
                arrResults(ResultIndex, 5) = arrTeams(TeamAIndex)
            ElseIf arrResults(ResultIndex, 4) > arrResults(ResultIndex, 2) Then
                'Team B Wins the game
                arrResults(ResultIndex, 5) = arrTeams(TeamBIndex)
            Else
                'Tie
                arrResults(ResultIndex, 5) = "Tie"
            End If
        Next TeamBIndex
    Next TeamAIndex

    'Output the results
    With ws.Cells(rngTeams.Row + rngTeams.Rows.Count + 1, "A").Resize(, UBound(arrResults, 2))
        .Value = Array("Team A", "Team A Score", "Team B", "Team B Score", "Winner")    'Column headers for the results
        .Offset(1).Resize(ResultIndex).Value = arrResults  'Results data
    End With

End Sub