VBA遍历带有嵌套for循环的列 [英] VBA loop through columns with nested for loops
问题描述
我以前从未编写过代码，但是对于一个项目，我正在分析幻想篮球的统计数据以确定哪些球队将在对决中获胜.统计数据有9个类别，总共12个团队，在第1队和第2队之间的对决中，一个类别中比较好的一个队获得1，其他队获得0.平局给每一个.5.总计总和，获胜者是得分更高的团队.
我编写了一个宏，将每个类别的团队1与所有其他团队进行比较，并相应地为每个团队分配1或0.我在编写一个循环时遇到了麻烦，该循环然后从第2小组开始，并将其与所有其他小组进行比较.我尝试过在其他两个循环上放置一个for循环，但似乎无法正确抵消它或使其正常工作.任何帮助，将不胜感激.我的代码如下.谢谢！
Sub WhoWins()昏暗的团队Dim teamBcounter As Integer昏暗的teamAanswercounter为整数昏暗的teamBanswercounter为整数这些计数器跟踪我们在统计和答案中所处的位置Dim Number1单身昏暗的数字2作为单'这些是目前用于确定胜利的数字昏暗的答案1作为单身昏暗的answer2作为单身昏暗分裂为单答案1 = 1分割= 0.5答案2 = 0'这些用于存储答案中的赢/输/平局值teamAanswercounter = teamBcounter + 16teamBanswercounter = teamAanswercounter + 1Dim columncounter为整数teamAcounter = 3对于columncounter = 2到10对于teamBcounter = 4到14Number1 =单元格(teamAcounter，columncounter).ValueNumber2 =单元格(teamBcounter，columncounter).值如果Number1>然后2号单元格(teamAanswercounter，columncounter).值= 1'answer1单元格(teamBanswercounter，columncounter).值= 0'answer2否则，如果Number2>然后1号单元格(teamAanswercounter，columncounter).值= 0'answer2单元格(teamBanswercounter，columncounter).值= 1'answer1ElseIf Number1 = Number2然后单元格(teamAanswercounter，columncounter).值=拆分单元格(teamBanswercounter，columncounter).值=拆分万一teamAanswercounter = teamAanswercounter + 3teamBanswercounter = teamAanswercounter + 1下一个团队'teamBcounter = 4'teamAcounter = 3teamAanswercounter = teamBcounter + 1teamBanswercounter = teamAanswercounter + 1下一栏计数器结束子
因为我自己也是幻想篮球迷，所以花了我两分钱.这是我使用的代码，已根据您的个人设置进行了调整.
函数GetStats(TeamName作为字符串)作为对象'这将返回一个字典对象.昏暗的WS作为工作表昏暗的TeamNameRange作为范围，TeamNameCell作为范围昏暗的TeamNameRow只要长昏暗StatsRange作为范围，StatsCell作为范围昏暗的TeamDict作为对象设置WS = ThisWorkbook.Sheets("Sheet1")使用WS设置TeamNameRange = .Range("A2:A13")设置StatsRange = .Range("B1:J1")结束于设置TeamDict = CreateObject("Scripting.Dictionary")对于TeamNameRange中的每个TeamNameCell如果TeamNameCell.Value = TeamName，则TeamNameRow = TeamNameCell.Row退出万一下一个与TeamDict对于StatsRange中的每个StatsCell添加StatsCell.Value，StatsCell.Offset(TeamNameRow-1，0).Value下一个结束于设置GetStats = TeamDict结束功能函数MatchUp(HomeTeamName作为字符串，AwayTeamName作为字符串)作为字符串昏暗的HomeTeamStats作为对象，AwayTeamStats作为对象昏暗HomeTeamScore为Double，AwayTeamScore为Double暗淡的Res作为字符串设置HomeTeamStats = GetStats(HomeTeamName)设置AwayTeamStats = GetStats(AwayTeamName)主队得分= 0AwayTeamScore = 0对于HomeTeamStats.Keys中的每个键如果HomeTeamStats(Key)>AwayTeamStats(Key)然后HomeTeamScore = HomeTeamScore +1ElseIf HomeTeamStats(Key)<AwayTeamStats(Key)然后AwayTeamScore = AwayTeamScore + 1否则，如果HomeTeamStats(Key)= AwayTeamStats(Key)，则HomeTeamScore = HomeTeamScore + 0.5AwayTeamScore = AwayTeamScore + 0.5万一下一个RES = HomeTeamScore&-"&客队得分如果HomeTeamScore>AwayTeamScore然后Res ="W"&Res&"L"否则，如果HomeTeamScore<AwayTeamScore然后Res ="L"&Res&"W"否则，如果HomeTeamScore = AwayTeamScore，则Res ="T"&Res&"T"万一对决= Res结束功能函数MatchUpTwo(HomeTeamName作为字符串，AwayTeamName作为字符串)作为字符串昏暗的HomeTeamStats作为对象，AwayTeamStats作为对象昏暗HomeTeamScore为Double，AwayTeamScore为Double暗淡的Res作为字符串设置HomeTeamStats = GetStats(HomeTeamName)设置AwayTeamStats = GetStats(AwayTeamName)主队得分= 0AwayTeamScore = 0对于HomeTeamStats.Keys中的每个键如果HomeTeamStats(Key)>AwayTeamStats(Key)然后HomeTeamScore = HomeTeamScore +1ElseIf HomeTeamStats(Key)<AwayTeamStats(Key)然后AwayTeamScore = AwayTeamScore + 1否则，如果HomeTeamStats(Key)= AwayTeamStats(Key)，则HomeTeamScore = HomeTeamScore + 0.5AwayTeamScore = AwayTeamScore + 0.5万一下一个如果HomeTeamScore>AwayTeamScore然后RES ="WIN"否则，如果HomeTeamScore<AwayTeamScore然后Res ="LOSE"否则，如果HomeTeamScore = AwayTeamScore，则Res ="TIE"万一MatchUpTwo = Res结束功能 将以上代码粘贴到常规模块中.您可以将其用作 = MatchUp("Team1"，"Team2")或 = MatchUpTwo("Team1"，"Team2")格式的公式..>
MatchUp 和 MatchUpTwo 之间的区别在于，后者输出的是单词而不是分数.基本上，主队是第一个参数，客队是后一个参数.如果输出 WIN ，则主队获胜. LOSE ，你明白了.
以上两个变体都使用 GetStats 函数，该函数创建了一个统计字典.因此，您可以在左侧添加更多的统计信息，而在更多的团队中则需要压缩，这样便可以正确缩放.
有关如何以表格格式最好地使用它的一个很好的应用程序，请参见以下屏幕截图:
如您所见，我的参考表在 A1 中.我上面的匹配表使用 MatchUp 函数，而下面的表使用 MatchUpTwo 函数以及其他条件格式.检查编辑栏如何设置公式.只需输入并拖动即可.
似乎像 Team 1 那样，对我来说影响最大.;)
享受，让我们知道是否有帮助.
I've never written code before but for a project I'm analyzing fantasy basketball stats to determine which teams would win in matchups. There are nine stat categories, 12 teams total, and in a matchup between teams 1 and 2, whichever team is better in a category gets a 1, and the other gets a 0. Ties give each .5. The totals are summed, and the winner is the team with more points.
I've written a macro that compares team 1 to all the others for each category and gives them a 1 or 0 for each accordingly. I'm having trouble writing a loop that then starts with team 2 and compares it to all the others. I've tried putting a for loop around the other two, but I can't seem to offset it properly or get it to work. Any help would be appreciated. My code is below. Thanks!
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
Throwing in my two cents, as I am a fan of fantasy basketball myself. Here's the code I use, adjusted for your personal set-up.
Function GetStats(TeamName As String) As Object
'This returns a dictionary object.
Dim WS As Worksheet
Dim TeamNameRange As Range, TeamNameCell As Range
Dim TeamNameRow As Long
Dim StatsRange As Range, StatsCell As Range
Dim TeamDict As Object
Set WS = ThisWorkbook.Sheets("Sheet1")
With WS
Set TeamNameRange = .Range("A2:A13")
Set StatsRange = .Range("B1:J1")
End With
Set TeamDict = CreateObject("Scripting.Dictionary")
For Each TeamNameCell In TeamNameRange
If TeamNameCell.Value = TeamName Then
TeamNameRow = TeamNameCell.Row
Exit For
End If
Next
With TeamDict
For Each StatsCell In StatsRange
.Add StatsCell.Value, StatsCell.Offset(TeamNameRow - 1, 0).Value
Next
End With
Set GetStats = TeamDict
End Function
Function MatchUp(HomeTeamName As String, AwayTeamName As String) As String
Dim HomeTeamStats As Object, AwayTeamStats As Object
Dim HomeTeamScore As Double, AwayTeamScore As Double
Dim Res As String
Set HomeTeamStats = GetStats(HomeTeamName)
Set AwayTeamStats = GetStats(AwayTeamName)
HomeTeamScore = 0
AwayTeamScore = 0
For Each Key In HomeTeamStats.Keys
If HomeTeamStats(Key) > AwayTeamStats(Key) Then
HomeTeamScore = HomeTeamScore + 1
ElseIf HomeTeamStats(Key) < AwayTeamStats(Key) Then
AwayTeamScore = AwayTeamScore + 1
ElseIf HomeTeamStats(Key) = AwayTeamStats(Key) Then
HomeTeamScore = HomeTeamScore + 0.5
AwayTeamScore = AwayTeamScore + 0.5
End If
Next
Res = HomeTeamScore & " - " & AwayTeamScore
If HomeTeamScore > AwayTeamScore Then
Res = "W " & Res & " L"
ElseIf HomeTeamScore < AwayTeamScore Then
Res = "L " & Res & " W"
ElseIf HomeTeamScore = AwayTeamScore Then
Res = "T " & Res & " T"
End If
MatchUp = Res
End Function
Function MatchUpTwo(HomeTeamName As String, AwayTeamName As String) As String
Dim HomeTeamStats As Object, AwayTeamStats As Object
Dim HomeTeamScore As Double, AwayTeamScore As Double
Dim Res As String
Set HomeTeamStats = GetStats(HomeTeamName)
Set AwayTeamStats = GetStats(AwayTeamName)
HomeTeamScore = 0
AwayTeamScore = 0
For Each Key In HomeTeamStats.Keys
If HomeTeamStats(Key) > AwayTeamStats(Key) Then
HomeTeamScore = HomeTeamScore + 1
ElseIf HomeTeamStats(Key) < AwayTeamStats(Key) Then
AwayTeamScore = AwayTeamScore + 1
ElseIf HomeTeamStats(Key) = AwayTeamStats(Key) Then
HomeTeamScore = HomeTeamScore + 0.5
AwayTeamScore = AwayTeamScore + 0.5
End If
Next
If HomeTeamScore > AwayTeamScore Then
Res = "WIN"
ElseIf HomeTeamScore < AwayTeamScore Then
Res = "LOSE"
ElseIf HomeTeamScore = AwayTeamScore Then
Res = "TIE"
End If
MatchUpTwo = Res
End Function
Paste the above code to a regular module. You can use it as formula in the format of =MatchUp("Team1", "Team2") or =MatchUpTwo("Team1", "Team2").
The difference between MatchUp and MatchUpTwo is that the latter outputs a word rather than a score. Basically, the home team is the first argument and the away team is the latter argument. If it outputs WIN, then the home team won. LOSE, and you get the point.
The above two variants both use the GetStats function, which creates a dictionary of stats. So you can add more stats to the left, more teams down, and it will scale properly.
For a nice application of how this is best used in table format, see the following screenshot:
As you can see, my reference table is in A1. My upper match-up table uses the MatchUp function, while the one below uses the MatchUpTwo function, with an additional conditional formatting. Check the formula bar how to set up the formula. Just input and drag.
Seems like Team 1 sucks the most on my end. ;)
Enjoy and let us know if this helps.
这篇关于VBA遍历带有嵌套for循环的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
