FATS
New Member
- Joined
- Jan 16, 2009
- Messages
- 37
- Office Version
- 365
- Platform
- Windows
Hello,
I have a type of hyperthetical question?
I have been typing some new macros for an Excel file I have been creating. But, during the building of it, it began to show a lot of the screen updating, despite me calling Screen.Updating = False at the beginning of the macro, I have altered my macros (slightly), and it seems to work how I want it to for the moment, but I have no idea what caused the problem, or how I fixed it.
Any insight offered would be appreciated to help me avoid similar issues in the future. I am using Excel 2003, on XP. I have one complete sheet (Teams (sheet2), and one blank sheet (sheet3), sheet 1 was deleted. and 2 modules of VBA
Below is the the working code I currently have;-
I have a type of hyperthetical question?
I have been typing some new macros for an Excel file I have been creating. But, during the building of it, it began to show a lot of the screen updating, despite me calling Screen.Updating = False at the beginning of the macro, I have altered my macros (slightly), and it seems to work how I want it to for the moment, but I have no idea what caused the problem, or how I fixed it.
Any insight offered would be appreciated to help me avoid similar issues in the future. I am using Excel 2003, on XP. I have one complete sheet (Teams (sheet2), and one blank sheet (sheet3), sheet 1 was deleted. and 2 modules of VBA
Below is the the working code I currently have;-
Code:
Sub Auto_Open()
Worksheets("Teams").OnEntry = "SortNames1"
End Sub
Sub SortNames1()
LastCell = ActiveCell.Address
Application.ScreenUpdating = False
ActiveSheet.Unprotect
'Copy All Team Names Into Database
Range("L5").Value = Range("B5").Value: Range("L6").Value = Range("B6").Value
Range("L7").Value = Range("B7").Value: Range("L8").Value = Range("B8").Value
Range("L9").Value = Range("B9").Value: Range("L10").Value = Range("B10").Value
Range("L11").Value = Range("B11").Value: Range("L12").Value = Range("B12").Value
Range("L13").Value = Range("B13").Value: Range("L14").Value = Range("B14").Value
Range("L15").Value = Range("B15").Value: Range("L16").Value = Range("B16").Value
Range("L17").Value = Range("B17").Value: Range("L18").Value = Range("B18").Value
Range("L19").Value = Range("B19").Value
'Delete All Player Names From Teams That No Longer Exist
If Range("L5").Value = "" Then
Range("N5").Value = "": Range("O5").Value = "": Range("P5").Value = ""
Range("Q5").Value = "": Range("R5").Value = "": Range("S5").Value = ""
Range("T5").Value = "": Range("U5").Value = "": Range("V5").Value = ""
Range("W5").Value = "": Range("X5").Value = "": Range("Y5").Value = ""
Range("Z5").Value = "": Range("AA5").Value = "": Range("AB5").Value = ""
Range("AC5").Value = "": Range("AD5").Value = "": Range("AE5").Value = ""
Range("AF5").Value = "": Range("AG5").Value = "": Range("AH5").Value = ""
Range("AI5").Value = "": Range("AJ5").Value = "": Range("AK5").Value = ""
Range("AL5").Value = ""
End If
If Range("L6").Value = "" Then
Range("N6").Value = "": Range("O6").Value = "": Range("P6").Value = ""
Range("Q6").Value = "": Range("R6").Value = "": Range("S6").Value = ""
Range("T6").Value = "": Range("U6").Value = "": Range("V6").Value = ""
Range("W6").Value = "": Range("X6").Value = "": Range("Y6").Value = ""
Range("Z6").Value = "": Range("AA6").Value = "": Range("AB6").Value = ""
Range("AC6").Value = "": Range("AD6").Value = "": Range("AE6").Value = ""
Range("AF6").Value = "": Range("AG6").Value = "": Range("AH6").Value = ""
Range("AI6").Value = "": Range("AJ6").Value = "": Range("AK6").Value = ""
Range("AL6").Value = ""
End If
If Range("L7").Value = "" Then
Range("N7").Value = "": Range("O7").Value = "": Range("P7").Value = ""
Range("Q7").Value = "": Range("R7").Value = "": Range("S7").Value = ""
Range("T7").Value = "": Range("U7").Value = "": Range("V7").Value = ""
Range("W7").Value = "": Range("X7").Value = "": Range("Y7").Value = ""
Range("Z7").Value = "": Range("AA7").Value = "": Range("AB7").Value = ""
Range("AC7").Value = "": Range("AD7").Value = "": Range("AE7").Value = ""
Range("AF7").Value = "": Range("AG7").Value = "": Range("AH7").Value = ""
Range("AI7").Value = "": Range("AJ7").Value = "": Range("AK7").Value = ""
Range("AL7").Value = ""
End If
If Range("L8").Value = "" Then
Range("N8").Value = "": Range("O8").Value = "": Range("P8").Value = ""
Range("Q8").Value = "": Range("R8").Value = "": Range("S8").Value = ""
Range("T8").Value = "": Range("U8").Value = "": Range("V8").Value = ""
Range("W8").Value = "": Range("X8").Value = "": Range("Y8").Value = ""
Range("Z8").Value = "": Range("AA8").Value = "": Range("AB8").Value = ""
Range("AC8").Value = "": Range("AD8").Value = "": Range("AE8").Value = ""
Range("AF8").Value = "": Range("AG8").Value = "": Range("AH8").Value = ""
Range("AI8").Value = "": Range("AJ8").Value = "": Range("AK8").Value = ""
Range("AL8").Value = ""
End If
If Range("L9").Value = "" Then
Range("N9").Value = "": Range("O9").Value = "": Range("P9").Value = ""
Range("Q9").Value = "": Range("R9").Value = "": Range("S9").Value = ""
Range("T9").Value = "": Range("U9").Value = "": Range("V9").Value = ""
Range("W9").Value = "": Range("X9").Value = "": Range("Y9").Value = ""
Range("Z9").Value = "": Range("AA9").Value = "": Range("AB9").Value = ""
Range("AC9").Value = "": Range("AD9").Value = "": Range("AE9").Value = ""
Range("AF9").Value = "": Range("AG9").Value = "": Range("AH9").Value = ""
Range("AI9").Value = "": Range("AJ9").Value = "": Range("AK9").Value = ""
Range("AL9").Value = ""
End If
If Range("L10").Value = "" Then
Range("N10").Value = "": Range("O10").Value = "": Range("P10").Value = ""
Range("Q10").Value = "": Range("R10").Value = "": Range("S10").Value = ""
Range("T10").Value = "": Range("U10").Value = "": Range("V10").Value = ""
Range("W10").Value = "": Range("X10").Value = "": Range("Y10").Value = ""
Range("Z10").Value = "": Range("AA10").Value = "": Range("AB10").Value = ""
Range("AC10").Value = "": Range("AD10").Value = "": Range("AE10").Value = ""
Range("AF10").Value = "": Range("AG10").Value = "": Range("AH10").Value = ""
Range("AI10").Value = "": Range("AJ10").Value = "": Range("AK10").Value = ""
Range("AL10").Value = ""
End If
If Range("L11").Value = "" Then
Range("N11").Value = "": Range("O11").Value = "": Range("P11").Value = ""
Range("Q11").Value = "": Range("R11").Value = "": Range("S11").Value = ""
Range("T11").Value = "": Range("U11").Value = "": Range("V11").Value = ""
Range("W11").Value = "": Range("X11").Value = "": Range("Y11").Value = ""
Range("Z11").Value = "": Range("AA11").Value = "": Range("AB11").Value = ""
Range("AC11").Value = "": Range("AD11").Value = "": Range("AE11").Value = ""
Range("AF11").Value = "": Range("AG11").Value = "": Range("AH11").Value = ""
Range("AI11").Value = "": Range("AJ11").Value = "": Range("AK11").Value = ""
Range("AL11").Value = ""
End If
If Range("L12").Value = "" Then
Range("N12").Value = "": Range("O12").Value = "": Range("P12").Value = ""
Range("Q12").Value = "": Range("R12").Value = "": Range("S12").Value = ""
Range("T12").Value = "": Range("U12").Value = "": Range("V12").Value = ""
Range("W12").Value = "": Range("X12").Value = "": Range("Y12").Value = ""
Range("Z12").Value = "": Range("AA12").Value = "": Range("AB12").Value = ""
Range("AC12").Value = "": Range("AD12").Value = "": Range("AE12").Value = ""
Range("AF12").Value = "": Range("AG12").Value = "": Range("AH12").Value = ""
Range("AI12").Value = "": Range("AJ12").Value = "": Range("AK12").Value = ""
Range("AL12").Value = ""
End If
If Range("L13").Value = "" Then
Range("N13").Value = "": Range("O13").Value = "": Range("P13").Value = ""
Range("Q13").Value = "": Range("R13").Value = "": Range("S13").Value = ""
Range("T13").Value = "": Range("U13").Value = "": Range("V13").Value = ""
Range("W13").Value = "": Range("X13").Value = "": Range("Y13").Value = ""
Range("Z13").Value = "": Range("AA13").Value = "": Range("AB13").Value = ""
Range("AC13").Value = "": Range("AD13").Value = "": Range("AE13").Value = ""
Range("AF13").Value = "": Range("AG13").Value = "": Range("AH13").Value = ""
Range("AI13").Value = "": Range("AJ13").Value = "": Range("AK13").Value = ""
Range("AL13").Value = ""
End If
If Range("L14").Value = "" Then
Range("N14").Value = "": Range("O14").Value = "": Range("P14").Value = ""
Range("Q14").Value = "": Range("R14").Value = "": Range("S14").Value = ""
Range("T14").Value = "": Range("U14").Value = "": Range("V14").Value = ""
Range("W14").Value = "": Range("X14").Value = "": Range("Y14").Value = ""
Range("Z14").Value = "": Range("AA14").Value = "": Range("AB14").Value = ""
Range("AC14").Value = "": Range("AD14").Value = "": Range("AE14").Value = ""
Range("AF14").Value = "": Range("AG14").Value = "": Range("AH14").Value = ""
Range("AI14").Value = "": Range("AJ14").Value = "": Range("AK14").Value = ""
Range("AL14").Value = ""
End If
If Range("L15").Value = "" Then
Range("N15").Value = "": Range("O15").Value = "": Range("P15").Value = ""
Range("Q15").Value = "": Range("R15").Value = "": Range("S15").Value = ""
Range("T15").Value = "": Range("U15").Value = "": Range("V15").Value = ""
Range("W15").Value = "": Range("X15").Value = "": Range("Y15").Value = ""
Range("Z15").Value = "": Range("AA15").Value = "": Range("AB15").Value = ""
Range("AC15").Value = "": Range("AD15").Value = "": Range("AE15").Value = ""
Range("AF15").Value = "": Range("AG15").Value = "": Range("AH15").Value = ""
Range("AI15").Value = "": Range("AJ15").Value = "": Range("AK15").Value = ""
Range("AL15").Value = ""
End If
If Range("L16").Value = "" Then
Range("N16").Value = "": Range("O16").Value = "": Range("P16").Value = ""
Range("Q16").Value = "": Range("R16").Value = "": Range("S16").Value = ""
Range("T16").Value = "": Range("U16").Value = "": Range("V16").Value = ""
Range("W16").Value = "": Range("X16").Value = "": Range("Y16").Value = ""
Range("Z16").Value = "": Range("AA16").Value = "": Range("AB16").Value = ""
Range("AC16").Value = "": Range("AD16").Value = "": Range("AE16").Value = ""
Range("AF16").Value = "": Range("AG16").Value = "": Range("AH16").Value = ""
Range("AI16").Value = "": Range("AJ16").Value = "": Range("AK16").Value = ""
Range("AL16").Value = ""
End If
If Range("L17").Value = "" Then
Range("N17").Value = "": Range("O17").Value = "": Range("P17").Value = ""
Range("Q17").Value = "": Range("R17").Value = "": Range("S17").Value = ""
Range("T17").Value = "": Range("U17").Value = "": Range("V17").Value = ""
Range("W17").Value = "": Range("X17").Value = "": Range("Y17").Value = ""
Range("Z17").Value = "": Range("AA17").Value = "": Range("AB17").Value = ""
Range("AC17").Value = "": Range("AD17").Value = "": Range("AE17").Value = ""
Range("AF17").Value = "": Range("AG17").Value = "": Range("AH17").Value = ""
Range("AI17").Value = "": Range("AJ17").Value = "": Range("AK17").Value = ""
Range("AL17").Value = ""
End If
If Range("L18").Value = "" Then
Range("N18").Value = "": Range("O18").Value = "": Range("P18").Value = ""
Range("Q18").Value = "": Range("R18").Value = "": Range("S18").Value = ""
Range("T18").Value = "": Range("U18").Value = "": Range("V18").Value = ""
Range("W18").Value = "": Range("X18").Value = "": Range("Y18").Value = ""
Range("Z18").Value = "": Range("AA18").Value = "": Range("AB18").Value = ""
Range("AC18").Value = "": Range("AD18").Value = "": Range("AE18").Value = ""
Range("AF18").Value = "": Range("AG18").Value = "": Range("AH18").Value = ""
Range("AI18").Value = "": Range("AJ18").Value = "": Range("AK18").Value = ""
Range("AL18").Value = ""
End If
If Range("L19").Value = "" Then
Range("N19").Value = "": Range("O19").Value = "": Range("P19").Value = ""
Range("Q19").Value = "": Range("R19").Value = "": Range("S19").Value = ""
Range("T19").Value = "": Range("U19").Value = "": Range("V19").Value = ""
Range("W19").Value = "": Range("X19").Value = "": Range("Y19").Value = ""
Range("Z19").Value = "": Range("AA19").Value = "": Range("AB19").Value = ""
Range("AC19").Value = "": Range("AD19").Value = "": Range("AE19").Value = ""
Range("AF19").Value = "": Range("AG19").Value = "": Range("AH19").Value = ""
Range("AI19").Value = "": Range("AJ19").Value = "": Range("AK19").Value = ""
Range("AL19").Value = ""
End If
'Adjust To Highest Alphabetical Team When Current Team No Longer Exists
If Range("F3").Value > Range("B3").Value Then Range("F3").Value = Range("B3").Value
Range(LastCell).Select
Application.Run ("SortNames2")
End Sub
Sub SortNames2()
LastCell = ActiveCell.Address
'Sort All Teams Alphabetically
Range("L5:AL19").Select
Selection.Sort Key1:=Range("L5"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
'Re-Copy All Team Names From Database
Range("B5").Formula = "=IF(L5<>"""",L5,"""")": Range("B6").Formula = "=IF(L6<>"""",L6,"""")"
Range("B7").Formula = "=IF(L7<>"""",L7,"""")": Range("B8").Formula = "=IF(L8<>"""",L8,"""")"
Range("B9").Formula = "=IF(L9<>"""",L9,"""")": Range("B10").Formula = "=IF(L10<>"""",L10,"""")"
Range("B11").Formula = "=IF(L11<>"""",L11,"""")": Range("B12").Formula = "=IF(L12<>"""",L12,"""")"
Range("B13").Formula = "=IF(L13<>"""",L13,"""")": Range("B14").Formula = "=IF(L14<>"""",L14,"""")"
Range("B15").Formula = "=IF(L15<>"""",L15,"""")": Range("B16").Formula = "=IF(L16<>"""",L16,"""")"
Range("B17").Formula = "=IF(L17<>"""",L17,"""")": Range("B18").Formula = "=IF(L18<>"""",L18,"""")"
Range("B19").Formula = "=IF(L19<>"""",L19,"""")"
'Re-Copy All Player Names From Database
If Range("K3").Value = Range("B3").Value Then
Range("N4").Select
ActiveCell.Offset(Range("D3").Value, 0).Select
ActiveCell.Value = Range("D5").Value: ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Range("D6").Value: ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Range("D7").Value: ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Range("D8").Value: ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Range("D9").Value: ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Range("D10").Value: ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Range("D11").Value: ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Range("D12").Value: ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Range("D13").Value: ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Range("D14").Value: ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Range("D15").Value: ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Range("D16").Value: ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Range("D17").Value: ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Range("D18").Value: ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Range("D19").Value: ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Range("D20").Value: ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Range("D21").Value: ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Range("D22").Value: ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Range("D23").Value: ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Range("D24").Value: ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Range("D25").Value: ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Range("D26").Value: ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Range("D27").Value: ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Range("D28").Value: ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Range("D29").Value: ActiveCell.Offset(0, 1).Select
End If
If Range("K3").Value = Range("B3").Value Then
Range("N4").Select
ActiveCell.Offset(Range("F3").Value, 0).Select
ActiveCell.Value = Range("F5").Value: ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Range("F6").Value: ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Range("F7").Value: ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Range("F8").Value: ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Range("F9").Value: ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Range("F10").Value: ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Range("F11").Value: ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Range("F12").Value: ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Range("F13").Value: ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Range("F14").Value: ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Range("F15").Value: ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Range("F16").Value: ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Range("F17").Value: ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Range("F18").Value: ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Range("F19").Value: ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Range("F20").Value: ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Range("F21").Value: ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Range("F22").Value: ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Range("F23").Value: ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Range("F24").Value: ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Range("F25").Value: ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Range("F26").Value: ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Range("F27").Value: ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Range("F28").Value: ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Range("F29").Value: ActiveCell.Offset(0, 1).Select
End If
If Range("K3").Value = Range("B3").Value Then
Range("N4").Select
ActiveCell.Offset(Range("H3").Value, 0).Select
ActiveCell.Value = Range("H5").Value: ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Range("H6").Value: ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Range("H7").Value: ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Range("H8").Value: ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Range("H9").Value: ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Range("H10").Value: ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Range("H11").Value: ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Range("H12").Value: ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Range("H13").Value: ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Range("H14").Value: ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Range("H15").Value: ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Range("H16").Value: ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Range("H17").Value: ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Range("H18").Value: ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Range("H19").Value: ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Range("H20").Value: ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Range("H21").Value: ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Range("H22").Value: ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Range("H23").Value: ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Range("H24").Value: ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Range("H25").Value: ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Range("H26").Value: ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Range("H27").Value: ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Range("H28").Value: ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Range("H29").Value: ActiveCell.Offset(0, 1).Select
End If
'Sort All Player Names Alphabetically Corrosponding To Their Team
Range("N5:AL5").Select
Selection.Sort Key1:=Range("N5"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight, DataOption1:=xlSortNormal
Range("N6:AL6").Select
Selection.Sort Key1:=Range("N6"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight, DataOption1:=xlSortNormal
Range("N7:AL7").Select
Selection.Sort Key1:=Range("N7"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight, DataOption1:=xlSortNormal
Range("N8:AL8").Select
Selection.Sort Key1:=Range("N8"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight, DataOption1:=xlSortNormal
Range("N9:AL9").Select
Selection.Sort Key1:=Range("N9"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight, DataOption1:=xlSortNormal
Range("N10:AL10").Select
Selection.Sort Key1:=Range("N10"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight, DataOption1:=xlSortNormal
Range("N11:AL11").Select
Selection.Sort Key1:=Range("N11"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight, DataOption1:=xlSortNormal
Range("N12:AL12").Select
Selection.Sort Key1:=Range("N12"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight, DataOption1:=xlSortNormal
Range("N13:AL13").Select
Selection.Sort Key1:=Range("N13"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight, DataOption1:=xlSortNormal
Range("N14:AL14").Select
Selection.Sort Key1:=Range("N14"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight, DataOption1:=xlSortNormal
Range("N15:AL15").Select
Selection.Sort Key1:=Range("N15"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight, DataOption1:=xlSortNormal
Range("N16:AL16").Select
Selection.Sort Key1:=Range("N16"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight, DataOption1:=xlSortNormal
Range("N17:AL17").Select
Selection.Sort Key1:=Range("N17"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight, DataOption1:=xlSortNormal
Range("N18:AL18").Select
Selection.Sort Key1:=Range("N18"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight, DataOption1:=xlSortNormal
Range("N19:AL19").Select
Selection.Sort Key1:=Range("N19"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight, DataOption1:=xlSortNormal
'Set A Value To Later Indicate Whether A Team has Been Added Or Deleted
Range("K3").Value = Range("B3").Value
Range(LastCell).Select
Application.Run ("SortNames3")
End Sub
Sub SortNames3()
LastCell = ActiveCell.Address
'Re-Copy Player Names From Database
If Range("D3").Value = 1 Then
Range("D5").Formula = "=IF(N5<>"""",N5,"""")"
Range("D6").Formula = "=IF(O5<>"""",O5,"""")"
Range("D7").Formula = "=IF(P5<>"""",P5,"""")"
Range("D8").Formula = "=IF(Q5<>"""",Q5,"""")"
Range("D9").Formula = "=IF(R5<>"""",R5,"""")"
Range("D10").Formula = "=IF(S5<>"""",S5,"""")"
Range("D11").Formula = "=IF(T5<>"""",T5,"""")"
Range("D12").Formula = "=IF(U5<>"""",U5,"""")"
Range("D13").Formula = "=IF(V5<>"""",V5,"""")"
Range("D14").Formula = "=IF(W5<>"""",W5,"""")"
Range("D15").Formula = "=IF(X5<>"""",X5,"""")"
Range("D16").Formula = "=IF(Y5<>"""",Y5,"""")"
Range("D17").Formula = "=IF(Z5<>"""",Z5,"""")"
Range("D18").Formula = "=IF(AA5<>"""",AA5,"""")"
Range("D19").Formula = "=IF(AB5<>"""",AB5,"""")"
Range("D20").Formula = "=IF(AC5<>"""",AC5,"""")"
Range("D21").Formula = "=IF(AD5<>"""",AD5,"""")"
Range("D22").Formula = "=IF(AE5<>"""",AE5,"""")"
Range("D23").Formula = "=IF(AF5<>"""",AF5,"""")"
Range("D24").Formula = "=IF(AG5<>"""",AG5,"""")"
Range("D25").Formula = "=IF(AH5<>"""",AH5,"""")"
Range("D26").Formula = "=IF(AI5<>"""",AI5,"""")"
Range("D27").Formula = "=IF(AJ5<>"""",AJ5,"""")"
Range("D28").Formula = "=IF(AK5<>"""",AK5,"""")"
Range("D29").Formula = "=IF(AL5<>"""",AL5,"""")"
End If
If Range("D3").Value = 2 Then
Range("D5").Formula = "=IF(N6<>"""",N6,"""")"
Range("D6").Formula = "=IF(O6<>"""",O6,"""")"
Range("D7").Formula = "=IF(P6<>"""",P6,"""")"
Range("D8").Formula = "=IF(Q6<>"""",Q6,"""")"
Range("D9").Formula = "=IF(R6<>"""",R6,"""")"
Range("D10").Formula = "=IF(S6<>"""",S6,"""")"
Range("D11").Formula = "=IF(T6<>"""",T6,"""")"
Range("D12").Formula = "=IF(U6<>"""",U6,"""")"
Range("D13").Formula = "=IF(V6<>"""",V6,"""")"
Range("D14").Formula = "=IF(W6<>"""",W6,"""")"
Range("D15").Formula = "=IF(X6<>"""",X6,"""")"
Range("D16").Formula = "=IF(Y6<>"""",Y6,"""")"
Range("D17").Formula = "=IF(Z6<>"""",Z6,"""")"
Range("D18").Formula = "=IF(AA6<>"""",AA6,"""")"
Range("D19").Formula = "=IF(AB6<>"""",AB6,"""")"
Range("D20").Formula = "=IF(AC6<>"""",AC6,"""")"
Range("D21").Formula = "=IF(AD6<>"""",AD6,"""")"
Range("D22").Formula = "=IF(AE6<>"""",AE6,"""")"
Range("D23").Formula = "=IF(AF6<>"""",AF6,"""")"
Range("D24").Formula = "=IF(AG6<>"""",AG6,"""")"
Range("D25").Formula = "=IF(AH6<>"""",AH6,"""")"
Range("D26").Formula = "=IF(AI6<>"""",AI6,"""")"
Range("D27").Formula = "=IF(AJ6<>"""",AJ6,"""")"
Range("D28").Formula = "=IF(AK6<>"""",AK6,"""")"
Range("D29").Formula = "=IF(AL6<>"""",AL6,"""")"
End If
If Range("D3").Value = 3 Then
Range("D5").Formula = "=IF(N7<>"""",N7,"""")"
Range("D6").Formula = "=IF(O7<>"""",O7,"""")"
Range("D7").Formula = "=IF(P7<>"""",P7,"""")"
Range("D8").Formula = "=IF(Q7<>"""",Q7,"""")"
Range("D9").Formula = "=IF(R7<>"""",R7,"""")"
Range("D10").Formula = "=IF(S7<>"""",S7,"""")"
Range("D11").Formula = "=IF(T7<>"""",T7,"""")"
Range("D12").Formula = "=IF(U7<>"""",U7,"""")"
Range("D13").Formula = "=IF(V7<>"""",V7,"""")"
Range("D14").Formula = "=IF(W7<>"""",W7,"""")"
Range("D15").Formula = "=IF(X7<>"""",X7,"""")"
Range("D16").Formula = "=IF(Y7<>"""",Y7,"""")"
Range("D17").Formula = "=IF(Z7<>"""",Z7,"""")"
Range("D18").Formula = "=IF(AA7<>"""",AA7,"""")"
Range("D19").Formula = "=IF(AB7<>"""",AB7,"""")"
Range("D20").Formula = "=IF(AC7<>"""",AC7,"""")"
Range("D21").Formula = "=IF(AD7<>"""",AD7,"""")"
Range("D22").Formula = "=IF(AE7<>"""",AE7,"""")"
Range("D23").Formula = "=IF(AF7<>"""",AF7,"""")"
Range("D24").Formula = "=IF(AG7<>"""",AG7,"""")"
Range("D25").Formula = "=IF(AH7<>"""",AH7,"""")"
Range("D26").Formula = "=IF(AI7<>"""",AI7,"""")"
Range("D27").Formula = "=IF(AJ7<>"""",AJ7,"""")"
Range("D28").Formula = "=IF(AK7<>"""",AK7,"""")"
Range("D29").Formula = "=IF(AL7<>"""",AL7,"""")"
End If
If Range("D3").Value = 4 Then
Range("D5").Formula = "=IF(N8<>"""",N8,"""")"
Range("D6").Formula = "=IF(O8<>"""",O8,"""")"
Range("D7").Formula = "=IF(P8<>"""",P8,"""")"
Range("D8").Formula = "=IF(Q8<>"""",Q8,"""")"
Range("D9").Formula = "=IF(R8<>"""",R8,"""")"
Range("D10").Formula = "=IF(S8<>"""",S8,"""")"
Range("D11").Formula = "=IF(T8<>"""",T8,"""")"
Range("D12").Formula = "=IF(U8<>"""",U8,"""")"
Range("D13").Formula = "=IF(V8<>"""",V8,"""")"
Range("D14").Formula = "=IF(W8<>"""",W8,"""")"
Range("D15").Formula = "=IF(X8<>"""",X8,"""")"
Range("D16").Formula = "=IF(Y8<>"""",Y8,"""")"
Range("D17").Formula = "=IF(Z8<>"""",Z8,"""")"
Range("D18").Formula = "=IF(AA8<>"""",AA8,"""")"
Range("D19").Formula = "=IF(AB8<>"""",AB8,"""")"
Range("D20").Formula = "=IF(AC8<>"""",AC8,"""")"
Range("D21").Formula = "=IF(AD8<>"""",AD8,"""")"
Range("D22").Formula = "=IF(AE8<>"""",AE8,"""")"
Range("D23").Formula = "=IF(AF8<>"""",AF8,"""")"
Range("D24").Formula = "=IF(AG8<>"""",AG8,"""")"
Range("D25").Formula = "=IF(AH8<>"""",AH8,"""")"
Range("D26").Formula = "=IF(AI8<>"""",AI8,"""")"
Range("D27").Formula = "=IF(AJ8<>"""",AJ8,"""")"
Range("D28").Formula = "=IF(AK8<>"""",AK8,"""")"
Range("D29").Formula = "=IF(AL8<>"""",AL8,"""")"
End If
If Range("D3").Value = 5 Then
Range("D5").Formula = "=IF(N9<>"""",N9,"""")"
Range("D6").Formula = "=IF(O9<>"""",O9,"""")"
Range("D7").Formula = "=IF(P9<>"""",P9,"""")"
Range("D8").Formula = "=IF(Q9<>"""",Q9,"""")"
Range("D9").Formula = "=IF(R9<>"""",R9,"""")"
Range("D10").Formula = "=IF(S9<>"""",S9,"""")"
Range("D11").Formula = "=IF(T9<>"""",T9,"""")"
Range("D12").Formula = "=IF(U9<>"""",U9,"""")"
Range("D13").Formula = "=IF(V9<>"""",V9,"""")"
Range("D14").Formula = "=IF(W9<>"""",W9,"""")"
Range("D15").Formula = "=IF(X9<>"""",X9,"""")"
Range("D16").Formula = "=IF(Y9<>"""",Y9,"""")"
Range("D17").Formula = "=IF(Z9<>"""",Z9,"""")"
Range("D18").Formula = "=IF(AA9<>"""",AA9,"""")"
Range("D19").Formula = "=IF(AB9<>"""",AB9,"""")"
Range("D20").Formula = "=IF(AC9<>"""",AC9,"""")"
Range("D21").Formula = "=IF(AD9<>"""",AD9,"""")"
Range("D22").Formula = "=IF(AE9<>"""",AE9,"""")"
Range("D23").Formula = "=IF(AF9<>"""",AF9,"""")"
Range("D24").Formula = "=IF(AG9<>"""",AG9,"""")"
Range("D25").Formula = "=IF(AH9<>"""",AH9,"""")"
Range("D26").Formula = "=IF(AI9<>"""",AI9,"""")"
Range("D27").Formula = "=IF(AJ9<>"""",AJ9,"""")"
Range("D28").Formula = "=IF(AK9<>"""",AK9,"""")"
Range("D29").Formula = "=IF(AL9<>"""",AL9,"""")"
End If
If Range("D3").Value = 6 Then
Range("D5").Formula = "=IF(N10<>"""",N10,"""")"
Range("D6").Formula = "=IF(O10<>"""",O10,"""")"
Range("D7").Formula = "=IF(P10<>"""",P10,"""")"
Range("D8").Formula = "=IF(Q10<>"""",Q10,"""")"
Range("D9").Formula = "=IF(R10<>"""",R10,"""")"
Range("D10").Formula = "=IF(S10<>"""",S10,"""")"
Range("D11").Formula = "=IF(T10<>"""",T10,"""")"
Range("D12").Formula = "=IF(U10<>"""",U10,"""")"
Range("D13").Formula = "=IF(V10<>"""",V10,"""")"
Range("D14").Formula = "=IF(W10<>"""",W10,"""")"
Range("D15").Formula = "=IF(X10<>"""",X10,"""")"
Range("D16").Formula = "=IF(Y10<>"""",Y10,"""")"
Range("D17").Formula = "=IF(Z10<>"""",Z10,"""")"
Range("D18").Formula = "=IF(AA10<>"""",AA10,"""")"
Range("D19").Formula = "=IF(AB10<>"""",AB10,"""")"
Range("D20").Formula = "=IF(AC10<>"""",AC10,"""")"
Range("D21").Formula = "=IF(AD10<>"""",AD10,"""")"
Range("D22").Formula = "=IF(AE10<>"""",AE10,"""")"
Range("D23").Formula = "=IF(AF10<>"""",AF10,"""")"
Range("D24").Formula = "=IF(AG10<>"""",AG10,"""")"
Range("D25").Formula = "=IF(AH10<>"""",AH10,"""")"
Range("D26").Formula = "=IF(AI10<>"""",AI10,"""")"
Range("D27").Formula = "=IF(AJ10<>"""",AJ10,"""")"
Range("D28").Formula = "=IF(AK10<>"""",AK10,"""")"
Range("D29").Formula = "=IF(AL10<>"""",AL10,"""")"
End If
If Range("D3").Value = 7 Then
Range("D5").Formula = "=IF(N11<>"""",N11,"""")"
Range("D6").Formula = "=IF(O11<>"""",O11,"""")"
Range("D7").Formula = "=IF(P11<>"""",P11,"""")"
Range("D8").Formula = "=IF(Q11<>"""",Q11,"""")"
Range("D9").Formula = "=IF(R11<>"""",R11,"""")"
Range("D10").Formula = "=IF(S11<>"""",S11,"""")"
Range("D11").Formula = "=IF(T11<>"""",T11,"""")"
Range("D12").Formula = "=IF(U11<>"""",U11,"""")"
Range("D13").Formula = "=IF(V11<>"""",V11,"""")"
Range("D14").Formula = "=IF(W11<>"""",W11,"""")"
Range("D15").Formula = "=IF(X11<>"""",X11,"""")"
Range("D16").Formula = "=IF(Y11<>"""",Y11,"""")"
Range("D17").Formula = "=IF(Z11<>"""",Z11,"""")"
Range("D18").Formula = "=IF(AA11<>"""",AA11,"""")"
Range("D19").Formula = "=IF(AB11<>"""",AB11,"""")"
Range("D20").Formula = "=IF(AC11<>"""",AC11,"""")"
Range("D21").Formula = "=IF(AD11<>"""",AD11,"""")"
Range("D22").Formula = "=IF(AE11<>"""",AE11,"""")"
Range("D23").Formula = "=IF(AF11<>"""",AF11,"""")"
Range("D24").Formula = "=IF(AG11<>"""",AG11,"""")"
Range("D25").Formula = "=IF(AH11<>"""",AH11,"""")"
Range("D26").Formula = "=IF(AI11<>"""",AI11,"""")"
Range("D27").Formula = "=IF(AJ11<>"""",AJ11,"""")"
Range("D28").Formula = "=IF(AK11<>"""",AK11,"""")"
Range("D29").Formula = "=IF(AL11<>"""",AL11,"""")"
End If
If Range("D3").Value = 8 Then
Range("D5").Formula = "=IF(N12<>"""",N12,"""")"
Range("D6").Formula = "=IF(O12<>"""",O12,"""")"
Range("D7").Formula = "=IF(P12<>"""",P12,"""")"
Range("D8").Formula = "=IF(Q12<>"""",Q12,"""")"
Range("D9").Formula = "=IF(R12<>"""",R12,"""")"
Range("D10").Formula = "=IF(S12<>"""",S12,"""")"
Range("D11").Formula = "=IF(T12<>"""",T12,"""")"
Range("D12").Formula = "=IF(U12<>"""",U12,"""")"
Range("D13").Formula = "=IF(V12<>"""",V12,"""")"
Range("D14").Formula = "=IF(W12<>"""",W12,"""")"
Range("D15").Formula = "=IF(X12<>"""",X12,"""")"
Range("D16").Formula = "=IF(Y12<>"""",Y12,"""")"
Range("D17").Formula = "=IF(Z12<>"""",Z12,"""")"
Range("D18").Formula = "=IF(AA12<>"""",AA12,"""")"
Range("D19").Formula = "=IF(AB12<>"""",AB12,"""")"
Range("D20").Formula = "=IF(AC12<>"""",AC12,"""")"
Range("D21").Formula = "=IF(AD12<>"""",AD12,"""")"
Range("D22").Formula = "=IF(AE12<>"""",AE12,"""")"
Range("D23").Formula = "=IF(AF12<>"""",AF12,"""")"
Range("D24").Formula = "=IF(AG12<>"""",AG12,"""")"
Range("D25").Formula = "=IF(AH12<>"""",AH12,"""")"
Range("D26").Formula = "=IF(AI12<>"""",AI12,"""")"
Range("D27").Formula = "=IF(AJ12<>"""",AJ12,"""")"
Range("D28").Formula = "=IF(AK12<>"""",AK12,"""")"
Range("D29").Formula = "=IF(AL12<>"""",AL12,"""")"
End If
If Range("D3").Value = 9 Then
Range("D5").Formula = "=IF(N13<>"""",N13,"""")"
Range("D6").Formula = "=IF(O13<>"""",O13,"""")"
Range("D7").Formula = "=IF(P13<>"""",P13,"""")"
Range("D8").Formula = "=IF(Q13<>"""",Q13,"""")"
Range("D9").Formula = "=IF(R13<>"""",R13,"""")"
Range("D10").Formula = "=IF(S13<>"""",S13,"""")"
Range("D11").Formula = "=IF(T13<>"""",T13,"""")"
Range("D12").Formula = "=IF(U13<>"""",U13,"""")"
Range("D13").Formula = "=IF(V13<>"""",V13,"""")"
Range("D14").Formula = "=IF(W13<>"""",W13,"""")"
Range("D15").Formula = "=IF(X13<>"""",X13,"""")"
Range("D16").Formula = "=IF(Y13<>"""",Y13,"""")"
Range("D17").Formula = "=IF(Z13<>"""",Z13,"""")"
Range("D18").Formula = "=IF(AA13<>"""",AA13,"""")"
Range("D19").Formula = "=IF(AB13<>"""",AB13,"""")"
Range("D20").Formula = "=IF(AC13<>"""",AC13,"""")"
Range("D21").Formula = "=IF(AD13<>"""",AD13,"""")"
Range("D22").Formula = "=IF(AE13<>"""",AE13,"""")"
Range("D23").Formula = "=IF(AF13<>"""",AF13,"""")"
Range("D24").Formula = "=IF(AG13<>"""",AG13,"""")"
Range("D25").Formula = "=IF(AH13<>"""",AH13,"""")"
Range("D26").Formula = "=IF(AI13<>"""",AI13,"""")"
Range("D27").Formula = "=IF(AJ13<>"""",AJ13,"""")"
Range("D28").Formula = "=IF(AK13<>"""",AK13,"""")"
Range("D29").Formula = "=IF(AL13<>"""",AL13,"""")"
End If
If Range("D3").Value = 10 Then
Range("D5").Formula = "=IF(N14<>"""",N14,"""")"
Range("D6").Formula = "=IF(O14<>"""",O14,"""")"
Range("D7").Formula = "=IF(P14<>"""",P14,"""")"
Range("D8").Formula = "=IF(Q14<>"""",Q14,"""")"
Range("D9").Formula = "=IF(R14<>"""",R14,"""")"
Range("D10").Formula = "=IF(S14<>"""",S14,"""")"
Range("D11").Formula = "=IF(T14<>"""",T14,"""")"
Range("D12").Formula = "=IF(U14<>"""",U14,"""")"
Range("D13").Formula = "=IF(V14<>"""",V14,"""")"
Range("D14").Formula = "=IF(W14<>"""",W14,"""")"
Range("D15").Formula = "=IF(X14<>"""",X14,"""")"
Range("D16").Formula = "=IF(Y14<>"""",Y14,"""")"
Range("D17").Formula = "=IF(Z14<>"""",Z14,"""")"
Range("D18").Formula = "=IF(AA14<>"""",AA14,"""")"
Range("D19").Formula = "=IF(AB14<>"""",AB14,"""")"
Range("D20").Formula = "=IF(AC14<>"""",AC14,"""")"
Range("D21").Formula = "=IF(AD14<>"""",AD14,"""")"
Range("D22").Formula = "=IF(AE14<>"""",AE14,"""")"
Range("D23").Formula = "=IF(AF14<>"""",AF14,"""")"
Range("D24").Formula = "=IF(AG14<>"""",AG14,"""")"
Range("D25").Formula = "=IF(AH14<>"""",AH14,"""")"
Range("D26").Formula = "=IF(AI14<>"""",AI14,"""")"
Range("D27").Formula = "=IF(AJ14<>"""",AJ14,"""")"
Range("D28").Formula = "=IF(AK14<>"""",AK14,"""")"
Range("D29").Formula = "=IF(AL14<>"""",AL14,"""")"
End If
If Range("D3").Value = 11 Then
Range("D5").Formula = "=IF(N15<>"""",N15,"""")"
Range("D6").Formula = "=IF(O15<>"""",O15,"""")"
Range("D7").Formula = "=IF(P15<>"""",P15,"""")"
Range("D8").Formula = "=IF(Q15<>"""",Q15,"""")"
Range("D9").Formula = "=IF(R15<>"""",R15,"""")"
Range("D10").Formula = "=IF(S15<>"""",S15,"""")"
Range("D11").Formula = "=IF(T15<>"""",T15,"""")"
Range("D12").Formula = "=IF(U15<>"""",U15,"""")"
Range("D13").Formula = "=IF(V15<>"""",V15,"""")"
Range("D14").Formula = "=IF(W15<>"""",W15,"""")"
Range("D15").Formula = "=IF(X15<>"""",X15,"""")"
Range("D16").Formula = "=IF(Y15<>"""",Y15,"""")"
Range("D17").Formula = "=IF(Z15<>"""",Z15,"""")"
Range("D18").Formula = "=IF(AA15<>"""",AA15,"""")"
Range("D19").Formula = "=IF(AB15<>"""",AB15,"""")"
Range("D20").Formula = "=IF(AC15<>"""",AC15,"""")"
Range("D21").Formula = "=IF(AD15<>"""",AD15,"""")"
Range("D22").Formula = "=IF(AE15<>"""",AE15,"""")"
Range("D23").Formula = "=IF(AF15<>"""",AF15,"""")"
Range("D24").Formula = "=IF(AG15<>"""",AG15,"""")"
Range("D25").Formula = "=IF(AH15<>"""",AH15,"""")"
Range("D26").Formula = "=IF(AI15<>"""",AI15,"""")"
Range("D27").Formula = "=IF(AJ15<>"""",AJ15,"""")"
Range("D28").Formula = "=IF(AK15<>"""",AK15,"""")"
Range("D29").Formula = "=IF(AL15<>"""",AL15,"""")"
End If
If Range("D3").Value = 12 Then
Range("D5").Formula = "=IF(N16<>"""",N16,"""")"
Range("D6").Formula = "=IF(O16<>"""",O16,"""")"
Range("D7").Formula = "=IF(P16<>"""",P16,"""")"
Range("D8").Formula = "=IF(Q16<>"""",Q16,"""")"
Range("D9").Formula = "=IF(R16<>"""",R16,"""")"
Range("D10").Formula = "=IF(S16<>"""",S16,"""")"
Range("D11").Formula = "=IF(T16<>"""",T16,"""")"
Range("D12").Formula = "=IF(U16<>"""",U16,"""")"
Range("D13").Formula = "=IF(V16<>"""",V16,"""")"
Range("D14").Formula = "=IF(W16<>"""",W16,"""")"
Range("D15").Formula = "=IF(X16<>"""",X16,"""")"
Range("D16").Formula = "=IF(Y16<>"""",Y16,"""")"
Range("D17").Formula = "=IF(Z16<>"""",Z16,"""")"
Range("D18").Formula = "=IF(AA16<>"""",AA16,"""")"
Range("D19").Formula = "=IF(AB16<>"""",AB16,"""")"
Range("D20").Formula = "=IF(AC16<>"""",AC16,"""")"
Range("D21").Formula = "=IF(AD16<>"""",AD16,"""")"
Range("D22").Formula = "=IF(AE16<>"""",AE16,"""")"
Range("D23").Formula = "=IF(AF16<>"""",AF16,"""")"
Range("D24").Formula = "=IF(AG16<>"""",AG16,"""")"
Range("D25").Formula = "=IF(AH16<>"""",AH16,"""")"
Range("D26").Formula = "=IF(AI16<>"""",AI16,"""")"
Range("D27").Formula = "=IF(AJ16<>"""",AJ16,"""")"
Range("D28").Formula = "=IF(AK16<>"""",AK16,"""")"
Range("D29").Formula = "=IF(AL16<>"""",AL16,"""")"
End If
If Range("D3").Value = 13 Then
Range("D5").Formula = "=IF(N17<>"""",N17,"""")"
Range("D6").Formula = "=IF(O17<>"""",O17,"""")"
Range("D7").Formula = "=IF(P17<>"""",P17,"""")"
Range("D8").Formula = "=IF(Q17<>"""",Q17,"""")"
Range("D9").Formula = "=IF(R17<>"""",R17,"""")"
Range("D10").Formula = "=IF(S17<>"""",S17,"""")"
Range("D11").Formula = "=IF(T17<>"""",T17,"""")"
Range("D12").Formula = "=IF(U17<>"""",U17,"""")"
Range("D13").Formula = "=IF(V17<>"""",V17,"""")"
Range("D14").Formula = "=IF(W17<>"""",W17,"""")"
Range("D15").Formula = "=IF(X17<>"""",X17,"""")"
Range("D16").Formula = "=IF(Y17<>"""",Y17,"""")"
Range("D17").Formula = "=IF(Z17<>"""",Z17,"""")"
Range("D18").Formula = "=IF(AA17<>"""",AA17,"""")"
Range("D19").Formula = "=IF(AB17<>"""",AB17,"""")"
Range("D20").Formula = "=IF(AC17<>"""",AC17,"""")"
Range("D21").Formula = "=IF(AD17<>"""",AD17,"""")"
Range("D22").Formula = "=IF(AE17<>"""",AE17,"""")"
Range("D23").Formula = "=IF(AF17<>"""",AF17,"""")"
Range("D24").Formula = "=IF(AG17<>"""",AG17,"""")"
Range("D25").Formula = "=IF(AH17<>"""",AH17,"""")"
Range("D26").Formula = "=IF(AI17<>"""",AI17,"""")"
Range("D27").Formula = "=IF(AJ17<>"""",AJ17,"""")"
Range("D28").Formula = "=IF(AK17<>"""",AK17,"""")"
Range("D29").Formula = "=IF(AL17<>"""",AL17,"""")"
End If
If Range("D3").Value = 14 Then
Range("D5").Formula = "=IF(N18<>"""",N18,"""")"
Range("D6").Formula = "=IF(O18<>"""",O18,"""")"
Range("D7").Formula = "=IF(P18<>"""",P18,"""")"
Range("D8").Formula = "=IF(Q18<>"""",Q18,"""")"
Range("D9").Formula = "=IF(R18<>"""",R18,"""")"
Range("D10").Formula = "=IF(S18<>"""",S18,"""")"
Range("D11").Formula = "=IF(T18<>"""",T18,"""")"
Range("D12").Formula = "=IF(U18<>"""",U18,"""")"
Range("D13").Formula = "=IF(V18<>"""",V18,"""")"
Range("D14").Formula = "=IF(W18<>"""",W18,"""")"
Range("D15").Formula = "=IF(X18<>"""",X18,"""")"
Range("D16").Formula = "=IF(Y18<>"""",Y18,"""")"
Range("D17").Formula = "=IF(Z18<>"""",Z18,"""")"
Range("D18").Formula = "=IF(AA18<>"""",AA18,"""")"
Range("D19").Formula = "=IF(AB18<>"""",AB18,"""")"
Range("D20").Formula = "=IF(AC18<>"""",AC18,"""")"
Range("D21").Formula = "=IF(AD18<>"""",AD18,"""")"
Range("D22").Formula = "=IF(AE18<>"""",AE18,"""")"
Range("D23").Formula = "=IF(AF18<>"""",AF18,"""")"
Range("D24").Formula = "=IF(AG18<>"""",AG18,"""")"
Range("D25").Formula = "=IF(AH18<>"""",AH18,"""")"
Range("D26").Formula = "=IF(AI18<>"""",AI18,"""")"
Range("D27").Formula = "=IF(AJ18<>"""",AJ18,"""")"
Range("D28").Formula = "=IF(AK18<>"""",AK18,"""")"
Range("D29").Formula = "=IF(AL18<>"""",AL18,"""")"
End If
If Range("D3").Value = 15 Then
Range("D5").Formula = "=IF(N19<>"""",N19,"""")"
Range("D6").Formula = "=IF(O19<>"""",O19,"""")"
Range("D7").Formula = "=IF(P19<>"""",P19,"""")"
Range("D8").Formula = "=IF(Q19<>"""",Q19,"""")"
Range("D9").Formula = "=IF(R19<>"""",R19,"""")"
Range("D10").Formula = "=IF(S19<>"""",S19,"""")"
Range("D11").Formula = "=IF(T19<>"""",T19,"""")"
Range("D12").Formula = "=IF(U19<>"""",U19,"""")"
Range("D13").Formula = "=IF(V19<>"""",V19,"""")"
Range("D14").Formula = "=IF(W19<>"""",W19,"""")"
Range("D15").Formula = "=IF(X19<>"""",X19,"""")"
Range("D16").Formula = "=IF(Y19<>"""",Y19,"""")"
Range("D17").Formula = "=IF(Z19<>"""",Z19,"""")"
Range("D18").Formula = "=IF(AA19<>"""",AA19,"""")"
Range("D19").Formula = "=IF(AB19<>"""",AB19,"""")"
Range("D20").Formula = "=IF(AC19<>"""",AC19,"""")"
Range("D21").Formula = "=IF(AD19<>"""",AD19,"""")"
Range("D22").Formula = "=IF(AE19<>"""",AE19,"""")"
Range("D23").Formula = "=IF(AF19<>"""",AF19,"""")"
Range("D24").Formula = "=IF(AG19<>"""",AG19,"""")"
Range("D25").Formula = "=IF(AH19<>"""",AH19,"""")"
Range("D26").Formula = "=IF(AI19<>"""",AI19,"""")"
Range("D27").Formula = "=IF(AJ19<>"""",AJ19,"""")"
Range("D28").Formula = "=IF(AK19<>"""",AK19,"""")"
Range("D29").Formula = "=IF(AL19<>"""",AL19,"""")"
End If
If Range("F3").Value = 1 Then
Range("F5").Formula = "=IF(N5<>"""",N5,"""")"
Range("F6").Formula = "=IF(O5<>"""",O5,"""")"
Range("F7").Formula = "=IF(P5<>"""",P5,"""")"
Range("F8").Formula = "=IF(Q5<>"""",Q5,"""")"
Range("F9").Formula = "=IF(R5<>"""",R5,"""")"
Range("F10").Formula = "=IF(S5<>"""",S5,"""")"
Range("F11").Formula = "=IF(T5<>"""",T5,"""")"
Range("F12").Formula = "=IF(U5<>"""",U5,"""")"
Range("F13").Formula = "=IF(V5<>"""",V5,"""")"
Range("F14").Formula = "=IF(W5<>"""",W5,"""")"
Range("F15").Formula = "=IF(X5<>"""",X5,"""")"
Range("F16").Formula = "=IF(Y5<>"""",Y5,"""")"
Range("F17").Formula = "=IF(Z5<>"""",Z5,"""")"
Range("F18").Formula = "=IF(AA5<>"""",AA5,"""")"
Range("F19").Formula = "=IF(AB5<>"""",AB5,"""")"
Range("F20").Formula = "=IF(AC5<>"""",AC5,"""")"
Range("F21").Formula = "=IF(AD5<>"""",AD5,"""")"
Range("F22").Formula = "=IF(AE5<>"""",AE5,"""")"
Range("F23").Formula = "=IF(AF5<>"""",AF5,"""")"
Range("F24").Formula = "=IF(AG5<>"""",AG5,"""")"
Range("F25").Formula = "=IF(AH5<>"""",AH5,"""")"
Range("F26").Formula = "=IF(AI5<>"""",AI5,"""")"
Range("F27").Formula = "=IF(AJ5<>"""",AJ5,"""")"
Range("F28").Formula = "=IF(AK5<>"""",AK5,"""")"
Range("F29").Formula = "=IF(AL5<>"""",AL5,"""")"
End If
If Range("F3").Value = 2 Then
Range("F5").Formula = "=IF(N6<>"""",N6,"""")"
Range("F6").Formula = "=IF(O6<>"""",O6,"""")"
Range("F7").Formula = "=IF(P6<>"""",P6,"""")"
Range("F8").Formula = "=IF(Q6<>"""",Q6,"""")"
Range("F9").Formula = "=IF(R6<>"""",R6,"""")"
Range("F10").Formula = "=IF(S6<>"""",S6,"""")"
Range("F11").Formula = "=IF(T6<>"""",T6,"""")"
Range("F12").Formula = "=IF(U6<>"""",U6,"""")"
Range("F13").Formula = "=IF(V6<>"""",V6,"""")"
Range("F14").Formula = "=IF(W6<>"""",W6,"""")"
Range("F15").Formula = "=IF(X6<>"""",X6,"""")"
Range("F16").Formula = "=IF(Y6<>"""",Y6,"""")"
Range("F17").Formula = "=IF(Z6<>"""",Z6,"""")"
Range("F18").Formula = "=IF(AA6<>"""",AA6,"""")"
Range("F19").Formula = "=IF(AB6<>"""",AB6,"""")"
Range("F20").Formula = "=IF(AC6<>"""",AC6,"""")"
Range("F21").Formula = "=IF(AD6<>"""",AD6,"""")"
Range("F22").Formula = "=IF(AE6<>"""",AE6,"""")"
Range("F23").Formula = "=IF(AF6<>"""",AF6,"""")"
Range("F24").Formula = "=IF(AG6<>"""",AG6,"""")"
Range("F25").Formula = "=IF(AH6<>"""",AH6,"""")"
Range("F26").Formula = "=IF(AI6<>"""",AI6,"""")"
Range("F27").Formula = "=IF(AJ6<>"""",AJ6,"""")"
Range("F28").Formula = "=IF(AK6<>"""",AK6,"""")"
Range("F29").Formula = "=IF(AL6<>"""",AL6,"""")"
End If
If Range("F3").Value = 3 Then
Range("F5").Formula = "=IF(N7<>"""",N7,"""")"
Range("F6").Formula = "=IF(O7<>"""",O7,"""")"
Range("F7").Formula = "=IF(P7<>"""",P7,"""")"
Range("F8").Formula = "=IF(Q7<>"""",Q7,"""")"
Range("F9").Formula = "=IF(R7<>"""",R7,"""")"
Range("F10").Formula = "=IF(S7<>"""",S7,"""")"
Range("F11").Formula = "=IF(T7<>"""",T7,"""")"
Range("F12").Formula = "=IF(U7<>"""",U7,"""")"
Range("F13").Formula = "=IF(V7<>"""",V7,"""")"
Range("F14").Formula = "=IF(W7<>"""",W7,"""")"
Range("F15").Formula = "=IF(X7<>"""",X7,"""")"
Range("F16").Formula = "=IF(Y7<>"""",Y7,"""")"
Range("F17").Formula = "=IF(Z7<>"""",Z7,"""")"
Range("F18").Formula = "=IF(AA7<>"""",AA7,"""")"
Range("F19").Formula = "=IF(AB7<>"""",AB7,"""")"
Range("F20").Formula = "=IF(AC7<>"""",AC7,"""")"
Range("F21").Formula = "=IF(AD7<>"""",AD7,"""")"
Range("F22").Formula = "=IF(AE7<>"""",AE7,"""")"
Range("F23").Formula = "=IF(AF7<>"""",AF7,"""")"
Range("F24").Formula = "=IF(AG7<>"""",AG7,"""")"
Range("F25").Formula = "=IF(AH7<>"""",AH7,"""")"
Range("F26").Formula = "=IF(AI7<>"""",AI7,"""")"
Range("F27").Formula = "=IF(AJ7<>"""",AJ7,"""")"
Range("F28").Formula = "=IF(AK7<>"""",AK7,"""")"
Range("F29").Formula = "=IF(AL7<>"""",AL7,"""")"
End If
If Range("F3").Value = 4 Then
Range("F5").Formula = "=IF(N8<>"""",N8,"""")"
Range("F6").Formula = "=IF(O8<>"""",O8,"""")"
Range("F7").Formula = "=IF(P8<>"""",P8,"""")"
Range("F8").Formula = "=IF(Q8<>"""",Q8,"""")"
Range("F9").Formula = "=IF(R8<>"""",R8,"""")"
Range("F10").Formula = "=IF(S8<>"""",S8,"""")"
Range("F11").Formula = "=IF(T8<>"""",T8,"""")"
Range("F12").Formula = "=IF(U8<>"""",U8,"""")"
Range("F13").Formula = "=IF(V8<>"""",V8,"""")"
Range("F14").Formula = "=IF(W8<>"""",W8,"""")"
Range("F15").Formula = "=IF(X8<>"""",X8,"""")"
Range("F16").Formula = "=IF(Y8<>"""",Y8,"""")"
Range("F17").Formula = "=IF(Z8<>"""",Z8,"""")"
Range("F18").Formula = "=IF(AA8<>"""",AA8,"""")"
Range("F19").Formula = "=IF(AB8<>"""",AB8,"""")"
Range("F20").Formula = "=IF(AC8<>"""",AC8,"""")"
Range("F21").Formula = "=IF(AD8<>"""",AD8,"""")"
Range("F22").Formula = "=IF(AE8<>"""",AE8,"""")"
Range("F23").Formula = "=IF(AF8<>"""",AF8,"""")"
Range("F24").Formula = "=IF(AG8<>"""",AG8,"""")"
Range("F25").Formula = "=IF(AH8<>"""",AH8,"""")"
Range("F26").Formula = "=IF(AI8<>"""",AI8,"""")"
Range("F27").Formula = "=IF(AJ8<>"""",AJ8,"""")"
Range("F28").Formula = "=IF(AK8<>"""",AK8,"""")"
Range("F29").Formula = "=IF(AL8<>"""",AL8,"""")"
End If
If Range("F3").Value = 5 Then
Range("F5").Formula = "=IF(N9<>"""",N9,"""")"
Range("F6").Formula = "=IF(O9<>"""",O9,"""")"
Range("F7").Formula = "=IF(P9<>"""",P9,"""")"
Range("F8").Formula = "=IF(Q9<>"""",Q9,"""")"
Range("F9").Formula = "=IF(R9<>"""",R9,"""")"
Range("F10").Formula = "=IF(S9<>"""",S9,"""")"
Range("F11").Formula = "=IF(T9<>"""",T9,"""")"
Range("F12").Formula = "=IF(U9<>"""",U9,"""")"
Range("F13").Formula = "=IF(V9<>"""",V9,"""")"
Range("F14").Formula = "=IF(W9<>"""",W9,"""")"
Range("F15").Formula = "=IF(X9<>"""",X9,"""")"
Range("F16").Formula = "=IF(Y9<>"""",Y9,"""")"
Range("F17").Formula = "=IF(Z9<>"""",Z9,"""")"
Range("F18").Formula = "=IF(AA9<>"""",AA9,"""")"
Range("F19").Formula = "=IF(AB9<>"""",AB9,"""")"
Range("F20").Formula = "=IF(AC9<>"""",AC9,"""")"
Range("F21").Formula = "=IF(AD9<>"""",AD9,"""")"
Range("F22").Formula = "=IF(AE9<>"""",AE9,"""")"
Range("F23").Formula = "=IF(AF9<>"""",AF9,"""")"
Range("F24").Formula = "=IF(AG9<>"""",AG9,"""")"
Range("F25").Formula = "=IF(AH9<>"""",AH9,"""")"
Range("F26").Formula = "=IF(AI9<>"""",AI9,"""")"
Range("F27").Formula = "=IF(AJ9<>"""",AJ9,"""")"
Range("F28").Formula = "=IF(AK9<>"""",AK9,"""")"
Range("F29").Formula = "=IF(AL9<>"""",AL9,"""")"
End If
If Range("F3").Value = 6 Then
Range("F5").Formula = "=IF(N10<>"""",N10,"""")"
Range("F6").Formula = "=IF(O10<>"""",O10,"""")"
Range("F7").Formula = "=IF(P10<>"""",P10,"""")"
Range("F8").Formula = "=IF(Q10<>"""",Q10,"""")"
Range("F9").Formula = "=IF(R10<>"""",R10,"""")"
Range("F10").Formula = "=IF(S10<>"""",S10,"""")"
Range("F11").Formula = "=IF(T10<>"""",T10,"""")"
Range("F12").Formula = "=IF(U10<>"""",U10,"""")"
Range("F13").Formula = "=IF(V10<>"""",V10,"""")"
Range("F14").Formula = "=IF(W10<>"""",W10,"""")"
Range("F15").Formula = "=IF(X10<>"""",X10,"""")"
Range("F16").Formula = "=IF(Y10<>"""",Y10,"""")"
Range("F17").Formula = "=IF(Z10<>"""",Z10,"""")"
Range("F18").Formula = "=IF(AA10<>"""",AA10,"""")"
Range("F19").Formula = "=IF(AB10<>"""",AB10,"""")"
Range("F20").Formula = "=IF(AC10<>"""",AC10,"""")"
Range("F21").Formula = "=IF(AD10<>"""",AD10,"""")"
Range("F22").Formula = "=IF(AE10<>"""",AE10,"""")"
Range("F23").Formula = "=IF(AF10<>"""",AF10,"""")"
Range("F24").Formula = "=IF(AG10<>"""",AG10,"""")"
Range("F25").Formula = "=IF(AH10<>"""",AH10,"""")"
Range("F26").Formula = "=IF(AI10<>"""",AI10,"""")"
Range("F27").Formula = "=IF(AJ10<>"""",AJ10,"""")"
Range("F28").Formula = "=IF(AK10<>"""",AK10,"""")"
Range("F29").Formula = "=IF(AL10<>"""",AL10,"""")"
End If
If Range("F3").Value = 7 Then
Range("F5").Formula = "=IF(N11<>"""",N11,"""")"
Range("F6").Formula = "=IF(O11<>"""",O11,"""")"
Range("F7").Formula = "=IF(P11<>"""",P11,"""")"
Range("F8").Formula = "=IF(Q11<>"""",Q11,"""")"
Range("F9").Formula = "=IF(R11<>"""",R11,"""")"
Range("F10").Formula = "=IF(S11<>"""",S11,"""")"
Range("F11").Formula = "=IF(T11<>"""",T11,"""")"
Range("F12").Formula = "=IF(U11<>"""",U11,"""")"
Range("F13").Formula = "=IF(V11<>"""",V11,"""")"
Range("F14").Formula = "=IF(W11<>"""",W11,"""")"
Range("F15").Formula = "=IF(X11<>"""",X11,"""")"
Range("F16").Formula = "=IF(Y11<>"""",Y11,"""")"
Range("F17").Formula = "=IF(Z11<>"""",Z11,"""")"
Range("F18").Formula = "=IF(AA11<>"""",AA11,"""")"
Range("F19").Formula = "=IF(AB11<>"""",AB11,"""")"
Range("F20").Formula = "=IF(AC11<>"""",AC11,"""")"
Range("F21").Formula = "=IF(AD11<>"""",AD11,"""")"
Range("F22").Formula = "=IF(AE11<>"""",AE11,"""")"
Range("F23").Formula = "=IF(AF11<>"""",AF11,"""")"
Range("F24").Formula = "=IF(AG11<>"""",AG11,"""")"
Range("F25").Formula = "=IF(AH11<>"""",AH11,"""")"
Range("F26").Formula = "=IF(AI11<>"""",AI11,"""")"
Range("F27").Formula = "=IF(AJ11<>"""",AJ11,"""")"
Range("F28").Formula = "=IF(AK11<>"""",AK11,"""")"
Range("F29").Formula = "=IF(AL11<>"""",AL11,"""")"
End If
If Range("F3").Value = 8 Then
Range("F5").Formula = "=IF(N12<>"""",N12,"""")"
Range("F6").Formula = "=IF(O12<>"""",O12,"""")"
Range("F7").Formula = "=IF(P12<>"""",P12,"""")"
Range("F8").Formula = "=IF(Q12<>"""",Q12,"""")"
Range("F9").Formula = "=IF(R12<>"""",R12,"""")"
Range("F10").Formula = "=IF(S12<>"""",S12,"""")"
Range("F11").Formula = "=IF(T12<>"""",T12,"""")"
Range("F12").Formula = "=IF(U12<>"""",U12,"""")"
Range("F13").Formula = "=IF(V12<>"""",V12,"""")"
Range("F14").Formula = "=IF(W12<>"""",W12,"""")"
Range("F15").Formula = "=IF(X12<>"""",X12,"""")"
Range("F16").Formula = "=IF(Y12<>"""",Y12,"""")"
Range("F17").Formula = "=IF(Z12<>"""",Z12,"""")"
Range("F18").Formula = "=IF(AA12<>"""",AA12,"""")"
Range("F19").Formula = "=IF(AB12<>"""",AB12,"""")"
Range("F20").Formula = "=IF(AC12<>"""",AC12,"""")"
Range("F21").Formula = "=IF(AD12<>"""",AD12,"""")"
Range("F22").Formula = "=IF(AE12<>"""",AE12,"""")"
Range("F23").Formula = "=IF(AF12<>"""",AF12,"""")"
Range("F24").Formula = "=IF(AG12<>"""",AG12,"""")"
Range("F25").Formula = "=IF(AH12<>"""",AH12,"""")"
Range("F26").Formula = "=IF(AI12<>"""",AI12,"""")"
Range("F27").Formula = "=IF(AJ12<>"""",AJ12,"""")"
Range("F28").Formula = "=IF(AK12<>"""",AK12,"""")"
Range("F29").Formula = "=IF(AL12<>"""",AL12,"""")"
End If
If Range("F3").Value = 9 Then
Range("F5").Formula = "=IF(N13<>"""",N13,"""")"
Range("F6").Formula = "=IF(O13<>"""",O13,"""")"
Range("F7").Formula = "=IF(P13<>"""",P13,"""")"
Range("F8").Formula = "=IF(Q13<>"""",Q13,"""")"
Range("F9").Formula = "=IF(R13<>"""",R13,"""")"
Range("F10").Formula = "=IF(S13<>"""",S13,"""")"
Range("F11").Formula = "=IF(T13<>"""",T13,"""")"
Range("F12").Formula = "=IF(U13<>"""",U13,"""")"
Range("F13").Formula = "=IF(V13<>"""",V13,"""")"
Range("F14").Formula = "=IF(W13<>"""",W13,"""")"
Range("F15").Formula = "=IF(X13<>"""",X13,"""")"
Range("F16").Formula = "=IF(Y13<>"""",Y13,"""")"
Range("F17").Formula = "=IF(Z13<>"""",Z13,"""")"
Range("F18").Formula = "=IF(AA13<>"""",AA13,"""")"
Range("F19").Formula = "=IF(AB13<>"""",AB13,"""")"
Range("F20").Formula = "=IF(AC13<>"""",AC13,"""")"
Range("F21").Formula = "=IF(AD13<>"""",AD13,"""")"
Range("F22").Formula = "=IF(AE13<>"""",AE13,"""")"
Range("F23").Formula = "=IF(AF13<>"""",AF13,"""")"
Range("F24").Formula = "=IF(AG13<>"""",AG13,"""")"
Range("F25").Formula = "=IF(AH13<>"""",AH13,"""")"
Range("F26").Formula = "=IF(AI13<>"""",AI13,"""")"
Range("F27").Formula = "=IF(AJ13<>"""",AJ13,"""")"
Range("F28").Formula = "=IF(AK13<>"""",AK13,"""")"
Range("F29").Formula = "=IF(AL13<>"""",AL13,"""")"
End If
If Range("F3").Value = 10 Then
Range("F5").Formula = "=IF(N14<>"""",N14,"""")"
Range("F6").Formula = "=IF(O14<>"""",O14,"""")"
Range("F7").Formula = "=IF(P14<>"""",P14,"""")"
Range("F8").Formula = "=IF(Q14<>"""",Q14,"""")"
Range("F9").Formula = "=IF(R14<>"""",R14,"""")"
Range("F10").Formula = "=IF(S14<>"""",S14,"""")"
Range("F11").Formula = "=IF(T14<>"""",T14,"""")"
Range("F12").Formula = "=IF(U14<>"""",U14,"""")"
Range("F13").Formula = "=IF(V14<>"""",V14,"""")"
Range("F14").Formula = "=IF(W14<>"""",W14,"""")"
Range("F15").Formula = "=IF(X14<>"""",X14,"""")"
Range("F16").Formula = "=IF(Y14<>"""",Y14,"""")"
Range("F17").Formula = "=IF(Z14<>"""",Z14,"""")"
Range("F18").Formula = "=IF(AA14<>"""",AA14,"""")"
Range("F19").Formula = "=IF(AB14<>"""",AB14,"""")"
Range("F20").Formula = "=IF(AC14<>"""",AC14,"""")"
Range("F21").Formula = "=IF(AD14<>"""",AD14,"""")"
Range("F22").Formula = "=IF(AE14<>"""",AE14,"""")"
Range("F23").Formula = "=IF(AF14<>"""",AF14,"""")"
Range("F24").Formula = "=IF(AG14<>"""",AG14,"""")"
Range("F25").Formula = "=IF(AH14<>"""",AH14,"""")"
Range("F26").Formula = "=IF(AI14<>"""",AI14,"""")"
Range("F27").Formula = "=IF(AJ14<>"""",AJ14,"""")"
Range("F28").Formula = "=IF(AK14<>"""",AK14,"""")"
Range("F29").Formula = "=IF(AL14<>"""",AL14,"""")"
End If
If Range("F3").Value = 11 Then
Range("F5").Formula = "=IF(N15<>"""",N15,"""")"
Range("F6").Formula = "=IF(O15<>"""",O15,"""")"
Range("F7").Formula = "=IF(P15<>"""",P15,"""")"
Range("F8").Formula = "=IF(Q15<>"""",Q15,"""")"
Range("F9").Formula = "=IF(R15<>"""",R15,"""")"
Range("F10").Formula = "=IF(S15<>"""",S15,"""")"
Range("F11").Formula = "=IF(T15<>"""",T15,"""")"
Range("F12").Formula = "=IF(U15<>"""",U15,"""")"
Range("F13").Formula = "=IF(V15<>"""",V15,"""")"
Range("F14").Formula = "=IF(W15<>"""",W15,"""")"
Range("F15").Formula = "=IF(X15<>"""",X15,"""")"
Range("F16").Formula = "=IF(Y15<>"""",Y15,"""")"
Range("F17").Formula = "=IF(Z15<>"""",Z15,"""")"
Range("F18").Formula = "=IF(AA15<>"""",AA15,"""")"
Range("F19").Formula = "=IF(AB15<>"""",AB15,"""")"
Range("F20").Formula = "=IF(AC15<>"""",AC15,"""")"
Range("F21").Formula = "=IF(AD15<>"""",AD15,"""")"
Range("F22").Formula = "=IF(AE15<>"""",AE15,"""")"
Range("F23").Formula = "=IF(AF15<>"""",AF15,"""")"
Range("F24").Formula = "=IF(AG15<>"""",AG15,"""")"
Range("F25").Formula = "=IF(AH15<>"""",AH15,"""")"
Range("F26").Formula = "=IF(AI15<>"""",AI15,"""")"
Range("F27").Formula = "=IF(AJ15<>"""",AJ15,"""")"
Range("F28").Formula = "=IF(AK15<>"""",AK15,"""")"
Range("F29").Formula = "=IF(AL15<>"""",AL15,"""")"
End If
If Range("F3").Value = 12 Then
Range("F5").Formula = "=IF(N16<>"""",N16,"""")"
Range("F6").Formula = "=IF(O16<>"""",O16,"""")"
Range("F7").Formula = "=IF(P16<>"""",P16,"""")"
Range("F8").Formula = "=IF(Q16<>"""",Q16,"""")"
Range("F9").Formula = "=IF(R16<>"""",R16,"""")"
Range("F10").Formula = "=IF(S16<>"""",S16,"""")"
Range("F11").Formula = "=IF(T16<>"""",T16,"""")"
Range("F12").Formula = "=IF(U16<>"""",U16,"""")"
Range("F13").Formula = "=IF(V16<>"""",V16,"""")"
Range("F14").Formula = "=IF(W16<>"""",W16,"""")"
Range("F15").Formula = "=IF(X16<>"""",X16,"""")"
Range("F16").Formula = "=IF(Y16<>"""",Y16,"""")"
Range("F17").Formula = "=IF(Z16<>"""",Z16,"""")"
Range("F18").Formula = "=IF(AA16<>"""",AA16,"""")"
Range("F19").Formula = "=IF(AB16<>"""",AB16,"""")"
Range("F20").Formula = "=IF(AC16<>"""",AC16,"""")"
Range("F21").Formula = "=IF(AD16<>"""",AD16,"""")"
Range("F22").Formula = "=IF(AE16<>"""",AE16,"""")"
Range("F23").Formula = "=IF(AF16<>"""",AF16,"""")"
Range("F24").Formula = "=IF(AG16<>"""",AG16,"""")"
Range("F25").Formula = "=IF(AH16<>"""",AH16,"""")"
Range("F26").Formula = "=IF(AI16<>"""",AI16,"""")"
Range("F27").Formula = "=IF(AJ16<>"""",AJ16,"""")"
Range("F28").Formula = "=IF(AK16<>"""",AK16,"""")"
Range("F29").Formula = "=IF(AL16<>"""",AL16,"""")"
End If
If Range("F3").Value = 13 Then
Range("F5").Formula = "=IF(N17<>"""",N17,"""")"
Range("F6").Formula = "=IF(O17<>"""",O17,"""")"
Range("F7").Formula = "=IF(P17<>"""",P17,"""")"
Range("F8").Formula = "=IF(Q17<>"""",Q17,"""")"
Range("F9").Formula = "=IF(R17<>"""",R17,"""")"
Range("F10").Formula = "=IF(S17<>"""",S17,"""")"
Range("F11").Formula = "=IF(T17<>"""",T17,"""")"
Range("F12").Formula = "=IF(U17<>"""",U17,"""")"
Range("F13").Formula = "=IF(V17<>"""",V17,"""")"
Range("F14").Formula = "=IF(W17<>"""",W17,"""")"
Range("F15").Formula = "=IF(X17<>"""",X17,"""")"
Range("F16").Formula = "=IF(Y17<>"""",Y17,"""")"
Range("F17").Formula = "=IF(Z17<>"""",Z17,"""")"
Range("F18").Formula = "=IF(AA17<>"""",AA17,"""")"
Range("F19").Formula = "=IF(AB17<>"""",AB17,"""")"
Range("F20").Formula = "=IF(AC17<>"""",AC17,"""")"
Range("F21").Formula = "=IF(AD17<>"""",AD17,"""")"
Range("F22").Formula = "=IF(AE17<>"""",AE17,"""")"
Range("F23").Formula = "=IF(AF17<>"""",AF17,"""")"
Range("F24").Formula = "=IF(AG17<>"""",AG17,"""")"
Range("F25").Formula = "=IF(AH17<>"""",AH17,"""")"
Range("F26").Formula = "=IF(AI17<>"""",AI17,"""")"
Range("F27").Formula = "=IF(AJ17<>"""",AJ17,"""")"
Range("F28").Formula = "=IF(AK17<>"""",AK17,"""")"
Range("F29").Formula = "=IF(AL17<>"""",AL17,"""")"
End If
If Range("F3").Value = 14 Then
Range("F5").Formula = "=IF(N18<>"""",N18,"""")"
Range("F6").Formula = "=IF(O18<>"""",O18,"""")"
Range("F7").Formula = "=IF(P18<>"""",P18,"""")"
Range("F8").Formula = "=IF(Q18<>"""",Q18,"""")"
Range("F9").Formula = "=IF(R18<>"""",R18,"""")"
Range("F10").Formula = "=IF(S18<>"""",S18,"""")"
Range("F11").Formula = "=IF(T18<>"""",T18,"""")"
Range("F12").Formula = "=IF(U18<>"""",U18,"""")"
Range("F13").Formula = "=IF(V18<>"""",V18,"""")"
Range("F14").Formula = "=IF(W18<>"""",W18,"""")"
Range("F15").Formula = "=IF(X18<>"""",X18,"""")"
Range("F16").Formula = "=IF(Y18<>"""",Y18,"""")"
Range("F17").Formula = "=IF(Z18<>"""",Z18,"""")"
Range("F18").Formula = "=IF(AA18<>"""",AA18,"""")"
Range("F19").Formula = "=IF(AB18<>"""",AB18,"""")"
Range("F20").Formula = "=IF(AC18<>"""",AC18,"""")"
Range("F21").Formula = "=IF(AD18<>"""",AD18,"""")"
Range("F22").Formula = "=IF(AE18<>"""",AE18,"""")"
Range("F23").Formula = "=IF(AF18<>"""",AF18,"""")"
Range("F24").Formula = "=IF(AG18<>"""",AG18,"""")"
Range("F25").Formula = "=IF(AH18<>"""",AH18,"""")"
Range("F26").Formula = "=IF(AI18<>"""",AI18,"""")"
Range("F27").Formula = "=IF(AJ18<>"""",AJ18,"""")"
Range("F28").Formula = "=IF(AK18<>"""",AK18,"""")"
Range("F29").Formula = "=IF(AL18<>"""",AL18,"""")"
End If
If Range("F3").Value = 15 Then
Range("F5").Formula = "=IF(N19<>"""",N19,"""")"
Range("F6").Formula = "=IF(O19<>"""",O19,"""")"
Range("F7").Formula = "=IF(P19<>"""",P19,"""")"
Range("F8").Formula = "=IF(Q19<>"""",Q19,"""")"
Range("F9").Formula = "=IF(R19<>"""",R19,"""")"
Range("F10").Formula = "=IF(S19<>"""",S19,"""")"
Range("F11").Formula = "=IF(T19<>"""",T19,"""")"
Range("F12").Formula = "=IF(U19<>"""",U19,"""")"
Range("F13").Formula = "=IF(V19<>"""",V19,"""")"
Range("F14").Formula = "=IF(W19<>"""",W19,"""")"
Range("F15").Formula = "=IF(X19<>"""",X19,"""")"
Range("F16").Formula = "=IF(Y19<>"""",Y19,"""")"
Range("F17").Formula = "=IF(Z19<>"""",Z19,"""")"
Range("F18").Formula = "=IF(AA19<>"""",AA19,"""")"
Range("F19").Formula = "=IF(AB19<>"""",AB19,"""")"
Range("F20").Formula = "=IF(AC19<>"""",AC19,"""")"
Range("F21").Formula = "=IF(AD19<>"""",AD19,"""")"
Range("F22").Formula = "=IF(AE19<>"""",AE19,"""")"
Range("F23").Formula = "=IF(AF19<>"""",AF19,"""")"
Range("F24").Formula = "=IF(AG19<>"""",AG19,"""")"
Range("F25").Formula = "=IF(AH19<>"""",AH19,"""")"
Range("F26").Formula = "=IF(AI19<>"""",AI19,"""")"
Range("F27").Formula = "=IF(AJ19<>"""",AJ19,"""")"
Range("F28").Formula = "=IF(AK19<>"""",AK19,"""")"
Range("F29").Formula = "=IF(AL19<>"""",AL19,"""")"
End If
If Range("H3").Value = 1 Then
Range("H5").Formula = "=IF(N5<>"""",N5,"""")"
Range("H6").Formula = "=IF(O5<>"""",O5,"""")"
Range("H7").Formula = "=IF(P5<>"""",P5,"""")"
Range("H8").Formula = "=IF(Q5<>"""",Q5,"""")"
Range("H9").Formula = "=IF(R5<>"""",R5,"""")"
Range("H10").Formula = "=IF(S5<>"""",S5,"""")"
Range("H11").Formula = "=IF(T5<>"""",T5,"""")"
Range("H12").Formula = "=IF(U5<>"""",U5,"""")"
Range("H13").Formula = "=IF(V5<>"""",V5,"""")"
Range("H14").Formula = "=IF(W5<>"""",W5,"""")"
Range("H15").Formula = "=IF(X5<>"""",X5,"""")"
Range("H16").Formula = "=IF(Y5<>"""",Y5,"""")"
Range("H17").Formula = "=IF(Z5<>"""",Z5,"""")"
Range("H18").Formula = "=IF(AA5<>"""",AA5,"""")"
Range("H19").Formula = "=IF(AB5<>"""",AB5,"""")"
Range("H20").Formula = "=IF(AC5<>"""",AC5,"""")"
Range("H21").Formula = "=IF(AD5<>"""",AD5,"""")"
Range("H22").Formula = "=IF(AE5<>"""",AE5,"""")"
Range("H23").Formula = "=IF(AF5<>"""",AF5,"""")"
Range("H24").Formula = "=IF(AG5<>"""",AG5,"""")"
Range("H25").Formula = "=IF(AH5<>"""",AH5,"""")"
Range("H26").Formula = "=IF(AI5<>"""",AI5,"""")"
Range("H27").Formula = "=IF(AJ5<>"""",AJ5,"""")"
Range("H28").Formula = "=IF(AK5<>"""",AK5,"""")"
Range("H29").Formula = "=IF(AL5<>"""",AL5,"""")"
End If
If Range("H3").Value = 2 Then
Range("H5").Formula = "=IF(N6<>"""",N6,"""")"
Range("H6").Formula = "=IF(O6<>"""",O6,"""")"
Range("H7").Formula = "=IF(P6<>"""",P6,"""")"
Range("H8").Formula = "=IF(Q6<>"""",Q6,"""")"
Range("H9").Formula = "=IF(R6<>"""",R6,"""")"
Range("H10").Formula = "=IF(S6<>"""",S6,"""")"
Range("H11").Formula = "=IF(T6<>"""",T6,"""")"
Range("H12").Formula = "=IF(U6<>"""",U6,"""")"
Range("H13").Formula = "=IF(V6<>"""",V6,"""")"
Range("H14").Formula = "=IF(W6<>"""",W6,"""")"
Range("H15").Formula = "=IF(X6<>"""",X6,"""")"
Range("H16").Formula = "=IF(Y6<>"""",Y6,"""")"
Range("H17").Formula = "=IF(Z6<>"""",Z6,"""")"
Range("H18").Formula = "=IF(AA6<>"""",AA6,"""")"
Range("H19").Formula = "=IF(AB6<>"""",AB6,"""")"
Range("H20").Formula = "=IF(AC6<>"""",AC6,"""")"
Range("H21").Formula = "=IF(AD6<>"""",AD6,"""")"
Range("H22").Formula = "=IF(AE6<>"""",AE6,"""")"
Range("H23").Formula = "=IF(AF6<>"""",AF6,"""")"
Range("H24").Formula = "=IF(AG6<>"""",AG6,"""")"
Range("H25").Formula = "=IF(AH6<>"""",AH6,"""")"
Range("H26").Formula = "=IF(AI6<>"""",AI6,"""")"
Range("H27").Formula = "=IF(AJ6<>"""",AJ6,"""")"
Range("H28").Formula = "=IF(AK6<>"""",AK6,"""")"
Range("H29").Formula = "=IF(AL6<>"""",AL6,"""")"
End If
If Range("H3").Value = 3 Then
Range("H5").Formula = "=IF(N7<>"""",N7,"""")"
Range("H6").Formula = "=IF(O7<>"""",O7,"""")"
Range("H7").Formula = "=IF(P7<>"""",P7,"""")"
Range("H8").Formula = "=IF(Q7<>"""",Q7,"""")"
Range("H9").Formula = "=IF(R7<>"""",R7,"""")"
Range("H10").Formula = "=IF(S7<>"""",S7,"""")"
Range("H11").Formula = "=IF(T7<>"""",T7,"""")"
Range("H12").Formula = "=IF(U7<>"""",U7,"""")"
Range("H13").Formula = "=IF(V7<>"""",V7,"""")"
Range("H14").Formula = "=IF(W7<>"""",W7,"""")"
Range("H15").Formula = "=IF(X7<>"""",X7,"""")"
Range("H16").Formula = "=IF(Y7<>"""",Y7,"""")"
Range("H17").Formula = "=IF(Z7<>"""",Z7,"""")"
Range("H18").Formula = "=IF(AA7<>"""",AA7,"""")"
Range("H19").Formula = "=IF(AB7<>"""",AB7,"""")"
Range("H20").Formula = "=IF(AC7<>"""",AC7,"""")"
Range("H21").Formula = "=IF(AD7<>"""",AD7,"""")"
Range("H22").Formula = "=IF(AE7<>"""",AE7,"""")"
Range("H23").Formula = "=IF(AF7<>"""",AF7,"""")"
Range("H24").Formula = "=IF(AG7<>"""",AG7,"""")"
Range("H25").Formula = "=IF(AH7<>"""",AH7,"""")"
Range("H26").Formula = "=IF(AI7<>"""",AI7,"""")"
Range("H27").Formula = "=IF(AJ7<>"""",AJ7,"""")"
Range("H28").Formula = "=IF(AK7<>"""",AK7,"""")"
Range("H29").Formula = "=IF(AL7<>"""",AL7,"""")"
End If
If Range("H3").Value = 4 Then
Range("H5").Formula = "=IF(N8<>"""",N8,"""")"
Range("H6").Formula = "=IF(O8<>"""",O8,"""")"
Range("H7").Formula = "=IF(P8<>"""",P8,"""")"
Range("H8").Formula = "=IF(Q8<>"""",Q8,"""")"
Range("H9").Formula = "=IF(R8<>"""",R8,"""")"
Range("H10").Formula = "=IF(S8<>"""",S8,"""")"
Range("H11").Formula = "=IF(T8<>"""",T8,"""")"
Range("H12").Formula = "=IF(U8<>"""",U8,"""")"
Range("H13").Formula = "=IF(V8<>"""",V8,"""")"
Range("H14").Formula = "=IF(W8<>"""",W8,"""")"
Range("H15").Formula = "=IF(X8<>"""",X8,"""")"
Range("H16").Formula = "=IF(Y8<>"""",Y8,"""")"
Range("H17").Formula = "=IF(Z8<>"""",Z8,"""")"
Range("H18").Formula = "=IF(AA8<>"""",AA8,"""")"
Range("H19").Formula = "=IF(AB8<>"""",AB8,"""")"
Range("H20").Formula = "=IF(AC8<>"""",AC8,"""")"
Range("H21").Formula = "=IF(AD8<>"""",AD8,"""")"
Range("H22").Formula = "=IF(AE8<>"""",AE8,"""")"
Range("H23").Formula = "=IF(AF8<>"""",AF8,"""")"
Range("H24").Formula = "=IF(AG8<>"""",AG8,"""")"
Range("H25").Formula = "=IF(AH8<>"""",AH8,"""")"
Range("H26").Formula = "=IF(AI8<>"""",AI8,"""")"
Range("H27").Formula = "=IF(AJ8<>"""",AJ8,"""")"
Range("H28").Formula = "=IF(AK8<>"""",AK8,"""")"
Range("H29").Formula = "=IF(AL8<>"""",AL8,"""")"
End If
If Range("H3").Value = 5 Then
Range("H5").Formula = "=IF(N9<>"""",N9,"""")"
Range("H6").Formula = "=IF(O9<>"""",O9,"""")"
Range("H7").Formula = "=IF(P9<>"""",P9,"""")"
Range("H8").Formula = "=IF(Q9<>"""",Q9,"""")"
Range("H9").Formula = "=IF(R9<>"""",R9,"""")"
Range("H10").Formula = "=IF(S9<>"""",S9,"""")"
Range("H11").Formula = "=IF(T9<>"""",T9,"""")"
Range("H12").Formula = "=IF(U9<>"""",U9,"""")"
Range("H13").Formula = "=IF(V9<>"""",V9,"""")"
Range("H14").Formula = "=IF(W9<>"""",W9,"""")"
Range("H15").Formula = "=IF(X9<>"""",X9,"""")"
Range("H16").Formula = "=IF(Y9<>"""",Y9,"""")"
Range("H17").Formula = "=IF(Z9<>"""",Z9,"""")"
Range("H18").Formula = "=IF(AA9<>"""",AA9,"""")"
Range("H19").Formula = "=IF(AB9<>"""",AB9,"""")"
Range("H20").Formula = "=IF(AC9<>"""",AC9,"""")"
Range("H21").Formula = "=IF(AD9<>"""",AD9,"""")"
Range("H22").Formula = "=IF(AE9<>"""",AE9,"""")"
Range("H23").Formula = "=IF(AF9<>"""",AF9,"""")"
Range("H24").Formula = "=IF(AG9<>"""",AG9,"""")"
Range("H25").Formula = "=IF(AH9<>"""",AH9,"""")"
Range("H26").Formula = "=IF(AI9<>"""",AI9,"""")"
Range("H27").Formula = "=IF(AJ9<>"""",AJ9,"""")"
Range("H28").Formula = "=IF(AK9<>"""",AK9,"""")"
Range("H29").Formula = "=IF(AL9<>"""",AL9,"""")"
End If
If Range("H3").Value = 6 Then
Range("H5").Formula = "=IF(N10<>"""",N10,"""")"
Range("H6").Formula = "=IF(O10<>"""",O10,"""")"
Range("H7").Formula = "=IF(P10<>"""",P10,"""")"
Range("H8").Formula = "=IF(Q10<>"""",Q10,"""")"
Range("H9").Formula = "=IF(R10<>"""",R10,"""")"
Range("H10").Formula = "=IF(S10<>"""",S10,"""")"
Range("H11").Formula = "=IF(T10<>"""",T10,"""")"
Range("H12").Formula = "=IF(U10<>"""",U10,"""")"
Range("H13").Formula = "=IF(V10<>"""",V10,"""")"
Range("H14").Formula = "=IF(W10<>"""",W10,"""")"
Range("H15").Formula = "=IF(X10<>"""",X10,"""")"
Range("H16").Formula = "=IF(Y10<>"""",Y10,"""")"
Range("H17").Formula = "=IF(Z10<>"""",Z10,"""")"
Range("H18").Formula = "=IF(AA10<>"""",AA10,"""")"
Range("H19").Formula = "=IF(AB10<>"""",AB10,"""")"
Range("H20").Formula = "=IF(AC10<>"""",AC10,"""")"
Range("H21").Formula = "=IF(AD10<>"""",AD10,"""")"
Range("H22").Formula = "=IF(AE10<>"""",AE10,"""")"
Range("H23").Formula = "=IF(AF10<>"""",AF10,"""")"
Range("H24").Formula = "=IF(AG10<>"""",AG10,"""")"
Range("H25").Formula = "=IF(AH10<>"""",AH10,"""")"
Range("H26").Formula = "=IF(AI10<>"""",AI10,"""")"
Range("H27").Formula = "=IF(AJ10<>"""",AJ10,"""")"
Range("H28").Formula = "=IF(AK10<>"""",AK10,"""")"
Range("H29").Formula = "=IF(AL10<>"""",AL10,"""")"
End If
If Range("H3").Value = 7 Then
Range("H5").Formula = "=IF(N11<>"""",N11,"""")"
Range("H6").Formula = "=IF(O11<>"""",O11,"""")"
Range("H7").Formula = "=IF(P11<>"""",P11,"""")"
Range("H8").Formula = "=IF(Q11<>"""",Q11,"""")"
Range("H9").Formula = "=IF(R11<>"""",R11,"""")"
Range("H10").Formula = "=IF(S11<>"""",S11,"""")"
Range("H11").Formula = "=IF(T11<>"""",T11,"""")"
Range("H12").Formula = "=IF(U11<>"""",U11,"""")"
Range("H13").Formula = "=IF(V11<>"""",V11,"""")"
Range("H14").Formula = "=IF(W11<>"""",W11,"""")"
Range("H15").Formula = "=IF(X11<>"""",X11,"""")"
Range("H16").Formula = "=IF(Y11<>"""",Y11,"""")"
Range("H17").Formula = "=IF(Z11<>"""",Z11,"""")"
Range("H18").Formula = "=IF(AA11<>"""",AA11,"""")"
Range("H19").Formula = "=IF(AB11<>"""",AB11,"""")"
Range("H20").Formula = "=IF(AC11<>"""",AC11,"""")"
Range("H21").Formula = "=IF(AD11<>"""",AD11,"""")"
Range("H22").Formula = "=IF(AE11<>"""",AE11,"""")"
Range("H23").Formula = "=IF(AF11<>"""",AF11,"""")"
Range("H24").Formula = "=IF(AG11<>"""",AG11,"""")"
Range("H25").Formula = "=IF(AH11<>"""",AH11,"""")"
Range("H26").Formula = "=IF(AI11<>"""",AI11,"""")"
Range("H27").Formula = "=IF(AJ11<>"""",AJ11,"""")"
Range("H28").Formula = "=IF(AK11<>"""",AK11,"""")"
Range("H29").Formula = "=IF(AL11<>"""",AL11,"""")"
End If
If Range("H3").Value = 8 Then
Range("H5").Formula = "=IF(N12<>"""",N12,"""")"
Range("H6").Formula = "=IF(O12<>"""",O12,"""")"
Range("H7").Formula = "=IF(P12<>"""",P12,"""")"
Range("H8").Formula = "=IF(Q12<>"""",Q12,"""")"
Range("H9").Formula = "=IF(R12<>"""",R12,"""")"
Range("H10").Formula = "=IF(S12<>"""",S12,"""")"
Range("H11").Formula = "=IF(T12<>"""",T12,"""")"
Range("H12").Formula = "=IF(U12<>"""",U12,"""")"
Range("H13").Formula = "=IF(V12<>"""",V12,"""")"
Range("H14").Formula = "=IF(W12<>"""",W12,"""")"
Range("H15").Formula = "=IF(X12<>"""",X12,"""")"
Range("H16").Formula = "=IF(Y12<>"""",Y12,"""")"
Range("H17").Formula = "=IF(Z12<>"""",Z12,"""")"
Range("H18").Formula = "=IF(AA12<>"""",AA12,"""")"
Range("H19").Formula = "=IF(AB12<>"""",AB12,"""")"
Range("H20").Formula = "=IF(AC12<>"""",AC12,"""")"
Range("H21").Formula = "=IF(AD12<>"""",AD12,"""")"
Range("H22").Formula = "=IF(AE12<>"""",AE12,"""")"
Range("H23").Formula = "=IF(AF12<>"""",AF12,"""")"
Range("H24").Formula = "=IF(AG12<>"""",AG12,"""")"
Range("H25").Formula = "=IF(AH12<>"""",AH12,"""")"
Range("H26").Formula = "=IF(AI12<>"""",AI12,"""")"
Range("H27").Formula = "=IF(AJ12<>"""",AJ12,"""")"
Range("H28").Formula = "=IF(AK12<>"""",AK12,"""")"
Range("H29").Formula = "=IF(AL12<>"""",AL12,"""")"
End If
If Range("H3").Value = 9 Then
Range("H5").Formula = "=IF(N13<>"""",N13,"""")"
Range("H6").Formula = "=IF(O13<>"""",O13,"""")"
Range("H7").Formula = "=IF(P13<>"""",P13,"""")"
Range("H8").Formula = "=IF(Q13<>"""",Q13,"""")"
Range("H9").Formula = "=IF(R13<>"""",R13,"""")"
Range("H10").Formula = "=IF(S13<>"""",S13,"""")"
Range("H11").Formula = "=IF(T13<>"""",T13,"""")"
Range("H12").Formula = "=IF(U13<>"""",U13,"""")"
Range("H13").Formula = "=IF(V13<>"""",V13,"""")"
Range("H14").Formula = "=IF(W13<>"""",W13,"""")"
Range("H15").Formula = "=IF(X13<>"""",X13,"""")"
Range("H16").Formula = "=IF(Y13<>"""",Y13,"""")"
Range("H17").Formula = "=IF(Z13<>"""",Z13,"""")"
Range("H18").Formula = "=IF(AA13<>"""",AA13,"""")"
Range("H19").Formula = "=IF(AB13<>"""",AB13,"""")"
Range("H20").Formula = "=IF(AC13<>"""",AC13,"""")"
Range("H21").Formula = "=IF(AD13<>"""",AD13,"""")"
Range("H22").Formula = "=IF(AE13<>"""",AE13,"""")"
Range("H23").Formula = "=IF(AF13<>"""",AF13,"""")"
Range("H24").Formula = "=IF(AG13<>"""",AG13,"""")"
Range("H25").Formula = "=IF(AH13<>"""",AH13,"""")"
Range("H26").Formula = "=IF(AI13<>"""",AI13,"""")"
Range("H27").Formula = "=IF(AJ13<>"""",AJ13,"""")"
Range("H28").Formula = "=IF(AK13<>"""",AK13,"""")"
Range("H29").Formula = "=IF(AL13<>"""",AL13,"""")"
End If
If Range("H3").Value = 10 Then
Range("H5").Formula = "=IF(N14<>"""",N14,"""")"
Range("H6").Formula = "=IF(O14<>"""",O14,"""")"
Range("H7").Formula = "=IF(P14<>"""",P14,"""")"
Range("H8").Formula = "=IF(Q14<>"""",Q14,"""")"
Range("H9").Formula = "=IF(R14<>"""",R14,"""")"
Range("H10").Formula = "=IF(S14<>"""",S14,"""")"
Range("H11").Formula = "=IF(T14<>"""",T14,"""")"
Range("H12").Formula = "=IF(U14<>"""",U14,"""")"
Range("H13").Formula = "=IF(V14<>"""",V14,"""")"
Range("H14").Formula = "=IF(W14<>"""",W14,"""")"
Range("H15").Formula = "=IF(X14<>"""",X14,"""")"
Range("H16").Formula = "=IF(Y14<>"""",Y14,"""")"
Range("H17").Formula = "=IF(Z14<>"""",Z14,"""")"
Range("H18").Formula = "=IF(AA14<>"""",AA14,"""")"
Range("H19").Formula = "=IF(AB14<>"""",AB14,"""")"
Range("H20").Formula = "=IF(AC14<>"""",AC14,"""")"
Range("H21").Formula = "=IF(AD14<>"""",AD14,"""")"
Range("H22").Formula = "=IF(AE14<>"""",AE14,"""")"
Range("H23").Formula = "=IF(AF14<>"""",AF14,"""")"
Range("H24").Formula = "=IF(AG14<>"""",AG14,"""")"
Range("H25").Formula = "=IF(AH14<>"""",AH14,"""")"
Range("H26").Formula = "=IF(AI14<>"""",AI14,"""")"
Range("H27").Formula = "=IF(AJ14<>"""",AJ14,"""")"
Range("H28").Formula = "=IF(AK14<>"""",AK14,"""")"
Range("H29").Formula = "=IF(AL14<>"""",AL14,"""")"
End If
If Range("H3").Value = 11 Then
Range("H5").Formula = "=IF(N15<>"""",N15,"""")"
Range("H6").Formula = "=IF(O15<>"""",O15,"""")"
Range("H7").Formula = "=IF(P15<>"""",P15,"""")"
Range("H8").Formula = "=IF(Q15<>"""",Q15,"""")"
Range("H9").Formula = "=IF(R15<>"""",R15,"""")"
Range("H10").Formula = "=IF(S15<>"""",S15,"""")"
Range("H11").Formula = "=IF(T15<>"""",T15,"""")"
Range("H12").Formula = "=IF(U15<>"""",U15,"""")"
Range("H13").Formula = "=IF(V15<>"""",V15,"""")"
Range("H14").Formula = "=IF(W15<>"""",W15,"""")"
Range("H15").Formula = "=IF(X15<>"""",X15,"""")"
Range("H16").Formula = "=IF(Y15<>"""",Y15,"""")"
Range("H17").Formula = "=IF(Z15<>"""",Z15,"""")"
Range("H18").Formula = "=IF(AA15<>"""",AA15,"""")"
Range("H19").Formula = "=IF(AB15<>"""",AB15,"""")"
Range("H20").Formula = "=IF(AC15<>"""",AC15,"""")"
Range("H21").Formula = "=IF(AD15<>"""",AD15,"""")"
Range("H22").Formula = "=IF(AE15<>"""",AE15,"""")"
Range("H23").Formula = "=IF(AF15<>"""",AF15,"""")"
Range("H24").Formula = "=IF(AG15<>"""",AG15,"""")"
Range("H25").Formula = "=IF(AH15<>"""",AH15,"""")"
Range("H26").Formula = "=IF(AI15<>"""",AI15,"""")"
Range("H27").Formula = "=IF(AJ15<>"""",AJ15,"""")"
Range("H28").Formula = "=IF(AK15<>"""",AK15,"""")"
Range("H29").Formula = "=IF(AL15<>"""",AL15,"""")"
End If
If Range("H3").Value = 12 Then
Range("H5").Formula = "=IF(N16<>"""",N16,"""")"
Range("H6").Formula = "=IF(O16<>"""",O16,"""")"
Range("H7").Formula = "=IF(P16<>"""",P16,"""")"
Range("H8").Formula = "=IF(Q16<>"""",Q16,"""")"
Range("H9").Formula = "=IF(R16<>"""",R16,"""")"
Range("H10").Formula = "=IF(S16<>"""",S16,"""")"
Range("H11").Formula = "=IF(T16<>"""",T16,"""")"
Range("H12").Formula = "=IF(U16<>"""",U16,"""")"
Range("H13").Formula = "=IF(V16<>"""",V16,"""")"
Range("H14").Formula = "=IF(W16<>"""",W16,"""")"
Range("H15").Formula = "=IF(X16<>"""",X16,"""")"
Range("H16").Formula = "=IF(Y16<>"""",Y16,"""")"
Range("H17").Formula = "=IF(Z16<>"""",Z16,"""")"
Range("H18").Formula = "=IF(AA16<>"""",AA16,"""")"
Range("H19").Formula = "=IF(AB16<>"""",AB16,"""")"
Range("H20").Formula = "=IF(AC16<>"""",AC16,"""")"
Range("H21").Formula = "=IF(AD16<>"""",AD16,"""")"
Range("H22").Formula = "=IF(AE16<>"""",AE16,"""")"
Range("H23").Formula = "=IF(AF16<>"""",AF16,"""")"
Range("H24").Formula = "=IF(AG16<>"""",AG16,"""")"
Range("H25").Formula = "=IF(AH16<>"""",AH16,"""")"
Range("H26").Formula = "=IF(AI16<>"""",AI16,"""")"
Range("H27").Formula = "=IF(AJ16<>"""",AJ16,"""")"
Range("H28").Formula = "=IF(AK16<>"""",AK16,"""")"
Range("H29").Formula = "=IF(AL16<>"""",AL16,"""")"
End If
If Range("H3").Value = 13 Then
Range("H5").Formula = "=IF(N17<>"""",N17,"""")"
Range("H6").Formula = "=IF(O17<>"""",O17,"""")"
Range("H7").Formula = "=IF(P17<>"""",P17,"""")"
Range("H8").Formula = "=IF(Q17<>"""",Q17,"""")"
Range("H9").Formula = "=IF(R17<>"""",R17,"""")"
Range("H10").Formula = "=IF(S17<>"""",S17,"""")"
Range("H11").Formula = "=IF(T17<>"""",T17,"""")"
Range("H12").Formula = "=IF(U17<>"""",U17,"""")"
Range("H13").Formula = "=IF(V17<>"""",V17,"""")"
Range("H14").Formula = "=IF(W17<>"""",W17,"""")"
Range("H15").Formula = "=IF(X17<>"""",X17,"""")"
Range("H16").Formula = "=IF(Y17<>"""",Y17,"""")"
Range("H17").Formula = "=IF(Z17<>"""",Z17,"""")"
Range("H18").Formula = "=IF(AA17<>"""",AA17,"""")"
Range("H19").Formula = "=IF(AB17<>"""",AB17,"""")"
Range("H20").Formula = "=IF(AC17<>"""",AC17,"""")"
Range("H21").Formula = "=IF(AD17<>"""",AD17,"""")"
Range("H22").Formula = "=IF(AE17<>"""",AE17,"""")"
Range("H23").Formula = "=IF(AF17<>"""",AF17,"""")"
Range("H24").Formula = "=IF(AG17<>"""",AG17,"""")"
Range("H25").Formula = "=IF(AH17<>"""",AH17,"""")"
Range("H26").Formula = "=IF(AI17<>"""",AI17,"""")"
Range("H27").Formula = "=IF(AJ17<>"""",AJ17,"""")"
Range("H28").Formula = "=IF(AK17<>"""",AK17,"""")"
Range("H29").Formula = "=IF(AL17<>"""",AL17,"""")"
End If
If Range("H3").Value = 14 Then
Range("H5").Formula = "=IF(N18<>"""",N18,"""")"
Range("H6").Formula = "=IF(O18<>"""",O18,"""")"
Range("H7").Formula = "=IF(P18<>"""",P18,"""")"
Range("H8").Formula = "=IF(Q18<>"""",Q18,"""")"
Range("H9").Formula = "=IF(R18<>"""",R18,"""")"
Range("H10").Formula = "=IF(S18<>"""",S18,"""")"
Range("H11").Formula = "=IF(T18<>"""",T18,"""")"
Range("H12").Formula = "=IF(U18<>"""",U18,"""")"
Range("H13").Formula = "=IF(V18<>"""",V18,"""")"
Range("H14").Formula = "=IF(W18<>"""",W18,"""")"
Range("H15").Formula = "=IF(X18<>"""",X18,"""")"
Range("H16").Formula = "=IF(Y18<>"""",Y18,"""")"
Range("H17").Formula = "=IF(Z18<>"""",Z18,"""")"
Range("H18").Formula = "=IF(AA18<>"""",AA18,"""")"
Range("H19").Formula = "=IF(AB18<>"""",AB18,"""")"
Range("H20").Formula = "=IF(AC18<>"""",AC18,"""")"
Range("H21").Formula = "=IF(AD18<>"""",AD18,"""")"
Range("H22").Formula = "=IF(AE18<>"""",AE18,"""")"
Range("H23").Formula = "=IF(AF18<>"""",AF18,"""")"
Range("H24").Formula = "=IF(AG18<>"""",AG18,"""")"
Range("H25").Formula = "=IF(AH18<>"""",AH18,"""")"
Range("H26").Formula = "=IF(AI18<>"""",AI18,"""")"
Range("H27").Formula = "=IF(AJ18<>"""",AJ18,"""")"
Range("H28").Formula = "=IF(AK18<>"""",AK18,"""")"
Range("H29").Formula = "=IF(AL18<>"""",AL18,"""")"
End If
If Range("H3").Value = 15 Then
Range("H5").Formula = "=IF(N19<>"""",N19,"""")"
Range("H6").Formula = "=IF(O19<>"""",O19,"""")"
Range("H7").Formula = "=IF(P19<>"""",P19,"""")"
Range("H8").Formula = "=IF(Q19<>"""",Q19,"""")"
Range("H9").Formula = "=IF(R19<>"""",R19,"""")"
Range("H10").Formula = "=IF(S19<>"""",S19,"""")"
Range("H11").Formula = "=IF(T19<>"""",T19,"""")"
Range("H12").Formula = "=IF(U19<>"""",U19,"""")"
Range("H13").Formula = "=IF(V19<>"""",V19,"""")"
Range("H14").Formula = "=IF(W19<>"""",W19,"""")"
Range("H15").Formula = "=IF(X19<>"""",X19,"""")"
Range("H16").Formula = "=IF(Y19<>"""",Y19,"""")"
Range("H17").Formula = "=IF(Z19<>"""",Z19,"""")"
Range("H18").Formula = "=IF(AA19<>"""",AA19,"""")"
Range("H19").Formula = "=IF(AB19<>"""",AB19,"""")"
Range("H20").Formula = "=IF(AC19<>"""",AC19,"""")"
Range("H21").Formula = "=IF(AD19<>"""",AD19,"""")"
Range("H22").Formula = "=IF(AE19<>"""",AE19,"""")"
Range("H23").Formula = "=IF(AF19<>"""",AF19,"""")"
Range("H24").Formula = "=IF(AG19<>"""",AG19,"""")"
Range("H25").Formula = "=IF(AH19<>"""",AH19,"""")"
Range("H26").Formula = "=IF(AI19<>"""",AI19,"""")"
Range("H27").Formula = "=IF(AJ19<>"""",AJ19,"""")"
Range("H28").Formula = "=IF(AK19<>"""",AK19,"""")"
Range("H29").Formula = "=IF(AL19<>"""",AL19,"""")"
End If
'Show Arrows When Its Necessary To Scroll Records
If (Range("B3").Value > 3) Then
ActiveSheet.Shapes("LeftArrow1").Visible = True
ActiveSheet.Shapes("RightArrow1").Visible = True
Else
ActiveSheet.Shapes("LeftArrow1").Visible = False
ActiveSheet.Shapes("RightArrow1").Visible = False
End If
Range(LastCell).Select
Application.ScreenUpdating = True
ActiveSheet.Protect
End Sub
Code:
Declare Function sndPlaySound32 Lib "winmm.dll" Alias "sndPlaySoundA" _
(ByVal lpszSoundName As String, ByVal uFlags As Long) As Long
Sub LeftClick()
LastCell = ActiveCell.Address
Application.ScreenUpdating = False
ActiveSheet.Unprotect
If Range("F3").Value - 1 = 0 Then
Range("F3").Value = Range("B3").Value
Else
Range("F3").Value = Range("F3").Value - 1
End If
Call sndPlaySound32("C:\Program Files\Microsoft Office\OFFICE11\MEDIA\CLICK.wav", 0)
Range(LastCell).Select
Application.Run ("SortNames3")
End Sub
Sub RightClick()
LastCell = ActiveCell.Address
Application.ScreenUpdating = False
ActiveSheet.Unprotect
If Range("F3").Value + 1 > Range("B3") Then
Range("F3").Value = 1
Else
Range("F3").Value = Range("F3").Value + 1
End If
Call sndPlaySound32("C:\Program Files\Microsoft Office\OFFICE11\MEDIA\CLICK.wav", 0)
Range(LastCell).Select
Application.Run ("SortNames3")
End Sub
Sub LockClick()
LastCell = ActiveCell.Address
Application.ScreenUpdating = False
ActiveSheet.Unprotect
'Lock All Teams To Prevent Accidental Deletion
Range("B5:B19").Select: Selection.Locked = True
'Show Locked Graphic
ActiveSheet.Shapes("Locked").Visible = True
ActiveSheet.Shapes("Unlocked").Visible = False
Call sndPlaySound32("C:\Program Files\Microsoft Office\OFFICE11\MEDIA\HAMMER.wav", 0)
Range(LastCell).Select
Application.ScreenUpdating = True
ActiveSheet.Protect
End Sub
Sub UnlockClick()
LastCell = ActiveCell.Address
Application.ScreenUpdating = False
ActiveSheet.Unprotect
'Unlock All Teams To Enable Editing
Range("B5:B19").Select: Selection.Locked = False
'Show Unlocked Graphic
ActiveSheet.Shapes("Locked").Visible = False
ActiveSheet.Shapes("Unlocked").Visible = True
Call sndPlaySound32("C:\Program Files\Microsoft Office\OFFICE11\MEDIA\HAMMER.wav", 0)
Range(LastCell).Select
Application.ScreenUpdating = True
ActiveSheet.Protect
End Sub
