Forum Discussion
I need someone that know NFL Playoffs Brackets works
I have code on file called 2019 NFL playoffs brackets have vba code.
look at picture of 2020 NFL playoffs system first before you look at 2020 NFL playoffs brackets this file need vba codes just like file call 2019 NFL playoffs Brackets. be newer code
The vba code before is from file called 2019 NFL Brackets
'Sort Teams Code
If Range("c4").Value > Range("c6").Value Then 'decides who will play 1 seed in NFC 2nd round
Range("f9").Value = Range("b4").Value
Range("e9").Value = Range("a4").Value
ElseIf Range("c9").Value > Range("c11").Value Then
Range("f9").Value = Range("b9").Value
Range("e9").Value = Range("a9").Value
ElseIf Range("c9").Value < Range("c11").Value Then
Range("f9").Value = Range("b11").Value
Range("e9").Value = Range("a11").Value
Else
Range("F9").Value = ""
Range("e9").Value = ""
End If
If Range("c6").Value > Range("c4").Value Then 'decides who will play 2 seed in NFC 2nd round
Range("f4").Value = Range("b6").Value
Range("e4").Value = Range("a6").Value
ElseIf Range("c9").Value < Range("c11").Value Then
Range("f4").Value = Range("b11").Value
Range("e4").Value = Range("a11").Value
ElseIf Range("c9").Value > Range("c11").Value Then
Range("f4").Value = Range("b9").Value
Range("e4").Value = Range("a9").Value
Else
Range("F4").Value = ""
Range("e4").Value = ""
End If
If Range("c17").Value > Range("c19").Value Then 'decides who will play 1 seed in AFC 2nd round
Range("f22").Value = Range("b17").Value
Range("e22").Value = Range("a17").Value
ElseIf Range("c22").Value > Range("c24").Value Then
Range("f22").Value = Range("b22").Value
Range("e22").Value = Range("a22").Value
ElseIf Range("c22").Value < Range("c24").Value Then
Range("f22").Value = Range("b24").Value
Range("e22").Value = Range("a24").Value
Else
Range("F22").Value = ""
Range("e22").Value = ""
End If
If Range("c19").Value > Range("c17").Value Then 'decides who will play 2 seed in AFC 2nd round
Range("f17").Value = Range("b19").Value
Range("e17").Value = Range("a19").Value
ElseIf Range("c22").Value < Range("c24").Value Then
Range("f17").Value = Range("b24").Value
Range("e17").Value = Range("a24").Value
ElseIf Range("c22").Value > Range("c24").Value Then
Range("f17").Value = Range("b22").Value
Range("e17").Value = Range("a22").Value
Else
Range("F17").Value = ""
Range("e17").Value = ""
End If
'AFC Championship Round
If [g24].Value > [G22].Value Then
[j24].Value = [f24].Value
[I24].Value = [E24].Value
ElseIf [g19].Value > [G17].Value Then
[j24].Value = [f19].Value
[I24].Value = [E19].Value
ElseIf [g19].Value < [G17].Value Then
[j24].Value = [f17].Value
[I24].Value = [e17].Value
Else
[j24].Value = ""
[I24].Value = ""
End If
If [G22].Value > [g24].Value Then
[j19].Value = [f22].Value
[I19].Value = [E22].Value
ElseIf [g19].Value > [G17].Value Then
[j19].Value = [f19].Value
[I19].Value = [E19].Value
ElseIf [g19].Value < [G17].Value Then
[j19].Value = [f17].Value
[I19].Value = [e17].Value
Else
[j19].Value = ""
[I19].Value = ""
End If
'NFC Championship Round
If [g11].Value > [G9].Value Then
[j11].Value = [f11].Value
[I11].Value = [E11].Value
ElseIf [g6].Value > [G4].Value Then
[j11].Value = [f6].Value
[I11].Value = [E6].Value
ElseIf [g6].Value < [G4].Value Then
[j11].Value = [f4].Value
[I11].Value = [E4].Value
Else
[j11].Value = ""
[I11].Value = ""
End If
If [G9].Value > [g11].Value Then
[j6].Value = [f9].Value
[I6].Value = [e9].Value
ElseIf [g6].Value > [G4].Value Then
[j6].Value = [f6].Value
[I6].Value = [E6].Value
ElseIf [g6].Value < [G4].Value Then
[j6].Value = [f4].Value
[I6].Value = [E4].Value
Else
[j6].Value = ""
[I6].Value = ""
End If
'Superbowl Round NFC TEAMS
If [K11].Value > [K6].Value Then
[n9].Value = [j11].Value
[m9].Value = [I11].Value
ElseIf [K6].Value > [K11].Value Then
[n9].Value = [j6].Value
[m9].Value = [I6].Value
Else
[n9].Value = ""
[m9].Value = ""
End If
'Superbowl Round AFC TEAMS
If [K24].Value > [K19].Value Then
[N22].Value = [j24].Value
[m22].Value = [I24].Value
ElseIf [K19].Value > [K24].Value Then
[N22].Value = [j19].Value
[m22].Value = [I19].Value
Else
[N22].Value = ""
[m22].Value = ""
End If
'Superbowl Champion
If [O9].Value > [O22].Value Then
[Q15].Value = [n9].Value
ElseIf [O22].Value > [O9].Value Then
[Q15].Value = [N22].Value
Else
[Q15].Value = ""
End If
[B4].Select
Application.ScreenUpdating = True
End Sub
Thanks You for any help
8 Replies
sf49ers19238597 Instead of VBA I just used spreadsheet formulas. see attached.
- I look at file. the formula is look at wrong place in divisional round it should look at score that are in green color area I put score .
Thanks Yousf49ers19238597 I looked at the file and did find a 'bug' but i don't think it is what you think. The RANK for the divisional round is actually looking at the cells to the right of the scores that LOOK blank but actually have formulas in them to pull the rank of the winner. Because of how you said it works, it doesn't fill it in until all 3 results are completed (i.e. not smart enough to say if rank 2 won that it must be the lowest rank ...). The 'bug' i did find was that when I copied the formulas from the upper bracket to the lower bracket I missed moving 1 range in each. That has been corrected in the attached.
- Thank you. I wounding how I do as vba code
Thanks You- I wouldn't.
a) why use VBA when worksheet functions do the job?
b) how would you 'trigger' the VBA? If you do it on every change on the worksheet you introduce a lot of overhead and if you add a button then that is an extra step
c) you could use 'onchange' and determine what has changed on the sheet and only update that part of the bracket, but that get pretty complicated and you are essentially trying to recreate what Excel already does with worksheet functions
