-3
\$\begingroup\$

This is used on an Excel order form to calculate quantities in a userform for variables based on various criteria on the worksheet. It's too much code for a single Sub, but I'm not sure how I can make it any smaller or break it up.

Private Sub UserForm_Activate()
Dim LastRow As Integer
LastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
Dim B_academy As Long, B_alice As Long, B_american As Long, B_apricot As Long, B_aqua As Long, B_beige As Long, B_black As Long, B_brightgold As Long, B_bronze As Long, B_brown As Long, B_burntorange As Long, B_cardinal As Long, B_citron As Long, B_coolgray As Long, B_copper As Long, B_cream As Long, B_drab As Long, B_forest As Long, B_gold As Long, B_gray As Long, B_kelly As Long, B_lemon As Long, B_ltblue As Long, B_lilac As Long, B_maize As Long, B_maroon As Long, B_navy As Long, B_nile As Long, B_oldgold As Long, B_olive As Long, B_orange As Long, B_peach As Long, B_peacock As Long, B_pink As Long, B_purple As Long, B_red As Long, B_sage As Long, B_salmon As Long, B_silver As Long, B_texasorange As Long, B_white As Long, B_wine As Long, B_yale As Long, _
M_academy As Long, M_alice As Long, M_american As Long, M_apricot As Long, M_aqua As Long, M_beige As Long, M_black As Long, M_brightgold As Long, M_bronze As Long, M_brown As Long, M_burntorange As Long, M_cardinal As Long, M_citron As Long, M_coolgray As Long, M_copper As Long, M_cream As Long, M_drab As Long, M_forest As Long, M_gold As Long, M_gray As Long, M_kelly As Long, M_lemon As Long, M_ltblue As Long, M_lilac As Long, M_maize As Long, M_maroon As Long, M_navy As Long, M_nile As Long, M_oldgold As Long, M_olive As Long, M_orange As Long, M_peach As Long, M_peacock As Long, M_pink As Long, M_purple As Long, M_red As Long, M_sage As Long, M_salmon As Long, M_silver As Long, M_texasorange As Long, M_white As Long, M_wine As Long, M_yale As Long, _
D_academy As Long, D_alice As Long, D_american As Long, D_apricot As Long, D_aqua As Long, D_beige As Long, D_black As Long, D_brightgold As Long, D_bronze As Long, D_brown As Long, D_burntorange As Long, D_cardinal As Long, D_citron As Long, D_coolgray As Long, D_copper As Long, D_cream As Long, D_drab As Long, D_forest As Long, D_gold As Long, D_gray As Long, D_kelly As Long, D_lemon As Long, D_ltblue As Long, D_lilac As Long, D_maize As Long, D_maroon As Long, D_navy As Long, D_nile As Long, D_oldgold As Long, D_olive As Long, D_orange As Long, D_peach As Long, D_peacock As Long, D_pink As Long, D_purple As Long, D_red As Long, D_sage As Long, D_salmon As Long, D_silver As Long, D_texasorange As Long, D_white As Long, D_wine As Long, D_yale As Long, _
A_academy As Long, A_alice As Long, A_american As Long, A_apricot As Long, A_aqua As Long, A_beige As Long, A_black As Long, A_brightgold As Long, A_bronze As Long, A_brown As Long, A_burntorange As Long, A_cardinal As Long, A_citron As Long, A_coolgray As Long, A_copper As Long, A_cream As Long, A_drab As Long, A_forest As Long, A_gold As Long, A_gray As Long, A_kelly As Long, A_lemon As Long, A_ltblue As Long, A_lilac As Long, A_maize As Long, A_maroon As Long, A_navy As Long, A_nile As Long, A_oldgold As Long, A_olive As Long, A_orange As Long, A_peach As Long, A_peacock As Long, A_pink As Long, A_purple As Long, A_red As Long, A_sage As Long, A_salmon As Long, A_silver As Long, A_texasorange As Long, A_white As Long, A_wine As Long, A_yale As Long, _
Mem_academy As Long, Mem_alice As Long, Mem_american As Long, Mem_apricot As Long, Mem_aqua As Long, Mem_beige As Long, Mem_black As Long, Mem_brightgold As Long, Mem_bronze As Long, Mem_brown As Long, Mem_burntorange As Long, Mem_cardinal As Long, Mem_citron As Long, Mem_coolgray As Long, Mem_copper As Long, Mem_cream As Long, Mem_drab As Long, Mem_forest As Long, Mem_gold As Long, Mem_gray As Long, Mem_kelly As Long, Mem_lemon As Long, Mem_ltblue As Long, Mem_lilac As Long, Mem_maize As Long, Mem_maroon As Long, Mem_navy As Long, Mem_nile As Long, Mem_oldgold As Long, Mem_olive As Long, Mem_orange As Long, Mem_peach As Long, Mem_peacock As Long, Mem_pink As Long, Mem_purple As Long, Mem_red As Long, Mem_sage As Long, Mem_salmon As Long, Mem_silver As Long, Mem_texasorange As Long, Mem_white As Long, Mem_wine As Long, Mem_yale As Long, _
B_tasselCount As String, M_tasselCount As String, D_tasselCount As String, A_tasselCount As String, Mem_tasselCount As String

B_alice = Application.Sum(Application.SumIfs(Range("G84:G" & LastRow), Range("G84:G" & LastRow), "1", Range("K84:K" & LastRow), "B*", Range("L84:L" & LastRow), Array("5 (macro)", "5")))
M_alice = Application.Sum(Application.SumIfs(Range("G84:G" & LastRow), Range("G84:G" & LastRow), "1", Range("K84:K" & LastRow), "M*", Range("L84:L" & LastRow), Array("5 (macro)", "5")))
D_alice = Application.Sum(Application.SumIfs(Range("G84:G" & LastRow), Range("G84:G" & LastRow), "1", Range("K84:K" & LastRow), "D*", Range("L84:L" & LastRow), Array("5 (macro)", "5")))
A_alice = Application.Sum(Application.SumIfs(Range("G84:G" & LastRow), Range("G84:G" & LastRow), "1", Range("K84:K" & LastRow), "A*", Range("L84:L" & LastRow), Array("5 (macro)", "5")))

B_apricot = Application.Sum(Application.SumIfs(Range("G84:G" & LastRow), Range("G84:G" & LastRow), "1", Range("K84:K" & LastRow), "B*", Range("L84:L" & LastRow), Array("58 (macro)", "58")))
M_apricot = Application.Sum(Application.SumIfs(Range("G84:G" & LastRow), Range("G84:G" & LastRow), "1", Range("K84:K" & LastRow), "M*", Range("L84:L" & LastRow), Array("58 (macro)", "58")))
D_apricot = Application.Sum(Application.SumIfs(Range("G84:G" & LastRow), Range("G84:G" & LastRow), "1", Range("K84:K" & LastRow), "D*", Range("L84:L" & LastRow), Array("58 (macro)", "58")))
A_apricot = Application.Sum(Application.SumIfs(Range("G84:G" & LastRow), Range("G84:G" & LastRow), "1", Range("K84:K" & LastRow), "A*", Range("L84:L" & LastRow), Array("58 (macro)", "58")))

B_aqua = Application.Sum(Application.SumIfs(Range("G84:G" & LastRow), Range("G84:G" & LastRow), "1", Range("K84:K" & LastRow), "B*", Range("L84:L" & LastRow), Array("60 (macro)", "60")))
M_aqua = Application.Sum(Application.SumIfs(Range("G84:G" & LastRow), Range("G84:G" & LastRow), "1", Range("K84:K" & LastRow), "M*", Range("L84:L" & LastRow), Array("60 (macro)", "60")))
D_aqua = Application.Sum(Application.SumIfs(Range("G84:G" & LastRow), Range("G84:G" & LastRow), "1", Range("K84:K" & LastRow), "D*", Range("L84:L" & LastRow), Array("60 (macro)", "60")))
A_aqua = Application.Sum(Application.SumIfs(Range("G84:G" & LastRow), Range("G84:G" & LastRow), "1", Range("K84:K" & LastRow), "A*", Range("L84:L" & LastRow), Array("60 (macro)", "60")))

B_black = Application.Sum(Application.SumIfs(Range("G84:G" & LastRow), Range("G84:G" & LastRow), "1", Range("K84:K" & LastRow), "B*", Range("L84:L" & LastRow), Array("98 (macro)", "98")))
M_black = Application.Sum(Application.SumIfs(Range("G84:G" & LastRow), Range("G84:G" & LastRow), "1", Range("K84:K" & LastRow), "M*", Range("L84:L" & LastRow), Array("98 (macro)", "98")))
D_black = Application.Sum(Application.SumIfs(Range("G84:G" & LastRow), Range("G84:G" & LastRow), "1", Range("K84:K" & LastRow), "D*", Range("L84:L" & LastRow), Array("98 (macro)", "98")))
A_black = Application.Sum(Application.SumIfs(Range("G84:G" & LastRow), Range("G84:G" & LastRow), "1", Range("K84:K" & LastRow), "A*", Range("L84:L" & LastRow), Array("98 (macro)", "98", "0 (macro)", "0")))

B_brown = Application.Sum(Application.SumIfs(Range("G84:G" & LastRow), Range("G84:G" & LastRow), "1", Range("K84:K" & LastRow), "B*", Range("L84:L" & LastRow), Array("30 (macro)", "30")))
M_brown = Application.Sum(Application.SumIfs(Range("G84:G" & LastRow), Range("G84:G" & LastRow), "1", Range("K84:K" & LastRow), "M*", Range("L84:L" & LastRow), Array("30 (macro)", "30")))
D_brown = Application.Sum(Application.SumIfs(Range("G84:G" & LastRow), Range("G84:G" & LastRow), "1", Range("K84:K" & LastRow), "D*", Range("L84:L" & LastRow), Array("30 (macro)", "30")))
A_brown = Application.Sum(Application.SumIfs(Range("G84:G" & LastRow), Range("G84:G" & LastRow), "1", Range("K84:K" & LastRow), "A*", Range("L84:L" & LastRow), Array("30 (macro)", "30")))

B_burntorange = Application.Sum(Application.SumIfs(Range("G84:G" & LastRow), Range("G84:G" & LastRow), "1", Range("K84:K" & LastRow), "B*", Range("L84:L" & LastRow), Array("32 (macro)", "32")))
M_burntorange = Application.Sum(Application.SumIfs(Range("G84:G" & LastRow), Range("G84:G" & LastRow), "1", Range("K84:K" & LastRow), "M*", Range("L84:L" & LastRow), Array("32 (macro)", "32")))
D_burntorange = Application.Sum(Application.SumIfs(Range("G84:G" & LastRow), Range("G84:G" & LastRow), "1", Range("K84:K" & LastRow), "D*", Range("L84:L" & LastRow), Array("32 (macro)", "32")))
A_burntorange = Application.Sum(Application.SumIfs(Range("G84:G" & LastRow), Range("G84:G" & LastRow), "1", Range("K84:K" & LastRow), "A*", Range("L84:L" & LastRow), Array("32 (macro)", "32")))

B_cardinal = Application.Sum(Application.SumIfs(Range("G84:G" & LastRow), Range("G84:G" & LastRow), "1", Range("K84:K" & LastRow), "B*", Range("L84:L" & LastRow), Array("48 (macro)", "48")))
M_cardinal = Application.Sum(Application.SumIfs(Range("G84:G" & LastRow), Range("G84:G" & LastRow), "1", Range("K84:K" & LastRow), "M*", Range("L84:L" & LastRow), Array("48 (macro)", "48")))
D_cardinal = Application.Sum(Application.SumIfs(Range("G84:G" & LastRow), Range("G84:G" & LastRow), "1", Range("K84:K" & LastRow), "D*", Range("L84:L" & LastRow), Array("48 (macro)", "48")))
A_cardinal = Application.Sum(Application.SumIfs(Range("G84:G" & LastRow), Range("G84:G" & LastRow), "1", Range("K84:K" & LastRow), "A*", Range("L84:L" & LastRow), Array("48 (macro)", "48")))

B_copper = Application.Sum(Application.SumIfs(Range("G84:G" & LastRow), Range("G84:G" & LastRow), "1", Range("K84:K" & LastRow), "B*", Range("L84:L" & LastRow), Array("20 (macro)", "20")))
M_copper = Application.Sum(Application.SumIfs(Range("G84:G" & LastRow), Range("G84:G" & LastRow), "1", Range("K84:K" & LastRow), "M*", Range("L84:L" & LastRow), Array("20 (macro)", "20")))
D_copper = Application.Sum(Application.SumIfs(Range("G84:G" & LastRow), Range("G84:G" & LastRow), "1", Range("K84:K" & LastRow), "D*", Range("L84:L" & LastRow), Array("20 (macro)", "20")))
A_copper = Application.Sum(Application.SumIfs(Range("G84:G" & LastRow), Range("G84:G" & LastRow), "1", Range("K84:K" & LastRow), "A*", Range("L84:L" & LastRow), Array("20 (macro)", "20")))
Mem_copper = Application.Sum(Application.SumIfs(Range("R84:R" & LastRow), Range("L84:L" & LastRow), Array("20 (macro)", "20")))

B_citron = Application.Sum(Application.SumIfs(Range("G84:G" & LastRow), Range("G84:G" & LastRow), "1", Range("K84:K" & LastRow), "B*", Range("L84:L" & LastRow), Array("84 (macro)", "84")))
M_citron = Application.Sum(Application.SumIfs(Range("G84:G" & LastRow), Range("G84:G" & LastRow), "1", Range("K84:K" & LastRow), "M*", Range("L84:L" & LastRow), Array("84 (macro)", "84")))
D_citron = Application.Sum(Application.SumIfs(Range("G84:G" & LastRow), Range("G84:G" & LastRow), "1", Range("K84:K" & LastRow), "D*", Range("L84:L" & LastRow), Array("84 (macro)", "84")))
A_citron = Application.Sum(Application.SumIfs(Range("G84:G" & LastRow), Range("G84:G" & LastRow), "1", Range("K84:K" & LastRow), "A*", Range("L84:L" & LastRow), Array("84 (macro)", "84")))
Mem_citron = Application.Sum(Application.SumIfs(Range("R84:R" & LastRow), Range("L84:L" & LastRow), Array("84 (macro)", "84")))

B_cream = Application.Sum(Application.SumIfs(Range("G84:G" & LastRow), Range("G84:G" & LastRow), "1", Range("K84:K" & LastRow), "B*", Range("L84:L" & LastRow), Array("99 (macro)", "99")))
M_cream = Application.Sum(Application.SumIfs(Range("G84:G" & LastRow), Range("G84:G" & LastRow), "1", Range("K84:K" & LastRow), "M*", Range("L84:L" & LastRow), Array("99 (macro)", "99")))
D_cream = Application.Sum(Application.SumIfs(Range("G84:G" & LastRow), Range("G84:G" & LastRow), "1", Range("K84:K" & LastRow), "D*", Range("L84:L" & LastRow), Array("99 (macro)", "99")))
A_cream = Application.Sum(Application.SumIfs(Range("G84:G" & LastRow), Range("G84:G" & LastRow), "1", Range("K84:K" & LastRow), "A*", Range("L84:L" & LastRow), Array("99 (macro)", "99")))
Mem_cream = Application.Sum(Application.SumIfs(Range("R84:R" & LastRow), Range("L84:L" & LastRow), Array("99 (macro)", "99")))

B_drab = Application.Sum(Application.SumIfs(Range("G84:G" & LastRow), Range("G84:G" & LastRow), "1", Range("K84:K" & LastRow), "B*", Range("L84:L" & LastRow), Array("8 (macro)", "8")))
M_drab = Application.Sum(Application.SumIfs(Range("G84:G" & LastRow), Range("G84:G" & LastRow), "1", Range("K84:K" & LastRow), "M*", Range("L84:L" & LastRow), Array("8 (macro)", "8")))
D_drab = Application.Sum(Application.SumIfs(Range("G84:G" & LastRow), Range("G84:G" & LastRow), "1", Range("K84:K" & LastRow), "D*", Range("L84:L" & LastRow), Array("8 (macro)", "8")))
A_drab = Application.Sum(Application.SumIfs(Range("G84:G" & LastRow), Range("G84:G" & LastRow), "1", Range("K84:K" & LastRow), "A*", Range("L84:L" & LastRow), Array("8 (macro)", "8")))
Mem_drab = Application.Sum(Application.SumIfs(Range("R84:R" & LastRow), Range("L84:L" & LastRow), Array("8 (macro)", "8")))

B_forest = Application.Sum(Application.SumIfs(Range("G84:G" & LastRow), Range("G84:G" & LastRow), "1", Range("K84:K" & LastRow), "B*", Range("L84:L" & LastRow), Array("81 (macro)", "81")))
M_forest = Application.Sum(Application.SumIfs(Range("G84:G" & LastRow), Range("G84:G" & LastRow), "1", Range("K84:K" & LastRow), "M*", Range("L84:L" & LastRow), Array("81 (macro)", "81")))
D_forest = Application.Sum(Application.SumIfs(Range("G84:G" & LastRow), Range("G84:G" & LastRow), "1", Range("K84:K" & LastRow), "D*", Range("L84:L" & LastRow), Array("81 (macro)", "81")))
A_forest = Application.Sum(Application.SumIfs(Range("G84:G" & LastRow), Range("G84:G" & LastRow), "1", Range("K84:K" & LastRow), "A*", Range("L84:L" & LastRow), Array("81 (macro)", "81")))
Mem_forest = Application.Sum(Application.SumIfs(Range("R84:R" & LastRow), Range("L84:L" & LastRow), Array("81 (macro)", "81")))

B_gold = Application.Sum(Application.SumIfs(Range("G84:G" & LastRow), Range("G84:G" & LastRow), "1", Range("K84:K" & LastRow), "B*", Range("L84:L" & LastRow), Array("80 (macro)", "80")))
M_gold = Application.Sum(Application.SumIfs(Range("G84:G" & LastRow), Range("G84:G" & LastRow), "1", Range("K84:K" & LastRow), "M*", Range("L84:L" & LastRow), Array("80 (macro)", "80")))
D_gold = Application.Sum(Application.SumIfs(Range("G84:G" & LastRow), Range("G84:G" & LastRow), "1", Range("K84:K" & LastRow), "D*", Range("L84:L" & LastRow), Array("80 (macro)", "80")))
A_gold = Application.Sum(Application.SumIfs(Range("G84:G" & LastRow), Range("G84:G" & LastRow), "1", Range("K84:K" & LastRow), "A*", Range("L84:L" & LastRow), Array("80 (macro)", "80")))
Mem_gold = Application.Sum(Application.SumIfs(Range("R84:R" & LastRow), Range("L84:L" & LastRow), Array("80 (macro)", "80")))

B_gray = Application.Sum(Application.SumIfs(Range("G84:G" & LastRow), Range("G84:G" & LastRow), "1", Range("K84:K" & LastRow), "B*", Range("L84:L" & LastRow), Array("88 (macro)", "88")))
M_gray = Application.Sum(Application.SumIfs(Range("G84:G" & LastRow), Range("G84:G" & LastRow), "1", Range("K84:K" & LastRow), "M*", Range("L84:L" & LastRow), Array("88 (macro)", "88")))
D_gray = Application.Sum(Application.SumIfs(Range("G84:G" & LastRow), Range("G84:G" & LastRow), "1", Range("K84:K" & LastRow), "D*", Range("L84:L" & LastRow), Array("88 (macro)", "88")))
A_gray = Application.Sum(Application.SumIfs(Range("G84:G" & LastRow), Range("G84:G" & LastRow), "1", Range("K84:K" & LastRow), "A*", Range("L84:L" & LastRow), Array("88 (macro)", "88")))
Mem_gray = Application.Sum(Application.SumIfs(Range("R84:R" & LastRow), Range("L84:L" & LastRow), Array("88 (macro)", "88")))

B_kelly = Application.Sum(Application.SumIfs(Range("G84:G" & LastRow), Range("G84:G" & LastRow), "1", Range("K84:K" & LastRow), "B*", Range("L84:L" & LastRow), Array("54 (macro)", "54")))
M_kelly = Application.Sum(Application.SumIfs(Range("G84:G" & LastRow), Range("G84:G" & LastRow), "1", Range("K84:K" & LastRow), "M*", Range("L84:L" & LastRow), Array("54 (macro)", "54")))
D_kelly = Application.Sum(Application.SumIfs(Range("G84:G" & LastRow), Range("G84:G" & LastRow), "1", Range("K84:K" & LastRow), "D*", Range("L84:L" & LastRow), Array("54 (macro)", "54")))
A_kelly = Application.Sum(Application.SumIfs(Range("G84:G" & LastRow), Range("G84:G" & LastRow), "1", Range("K84:K" & LastRow), "A*", Range("L84:L" & LastRow), Array("54 (macro)", "54")))
Mem_kelly = Application.Sum(Application.SumIfs(Range("R84:R" & LastRow), Range("L84:L" & LastRow), Array("54 (macro)", "54")))

B_lemon = Application.Sum(Application.SumIfs(Range("G84:G" & LastRow), Range("G84:G" & LastRow), "1", Range("K84:K" & LastRow), "B*", Range("L84:L" & LastRow), Array("52 (macro)", "52")))
M_lemon = Application.Sum(Application.SumIfs(Range("G84:G" & LastRow), Range("G84:G" & LastRow), "1", Range("K84:K" & LastRow), "M*", Range("L84:L" & LastRow), Array("52 (macro)", "52")))
D_lemon = Application.Sum(Application.SumIfs(Range("G84:G" & LastRow), Range("G84:G" & LastRow), "1", Range("K84:K" & LastRow), "D*", Range("L84:L" & LastRow), Array("52 (macro)", "52")))
A_lemon = Application.Sum(Application.SumIfs(Range("G84:G" & LastRow), Range("G84:G" & LastRow), "1", Range("K84:K" & LastRow), "A*", Range("L84:L" & LastRow), Array("52 (macro)", "52")))
Mem_lemon = Application.Sum(Application.SumIfs(Range("R84:R" & LastRow), Range("L84:L" & LastRow), Array("52 (macro)", "52")))

B_lilac = Application.Sum(Application.SumIfs(Range("G84:G" & LastRow), Range("G84:G" & LastRow), "1", Range("K84:K" & LastRow), "B*", Range("L84:L" & LastRow), Array("18 (macro)", "18"))) + Application.Sum(Application.SumIfs(Range("G84:G" & LastRow), Range("G84:G" & LastRow), "1", Range("K84:K" & LastRow), "B*", Range("L84:L" & LastRow), Array("6 (macro)", "6")))
M_lilac = Application.Sum(Application.SumIfs(Range("G84:G" & LastRow), Range("G84:G" & LastRow), "1", Range("K84:K" & LastRow), "M*", Range("L84:L" & LastRow), Array("18 (macro)", "18"))) + M_lilac = Application.Sum(Application.SumIfs(Range("G84:G" & LastRow), Range("G84:G" & LastRow), "1", Range("K84:K" & LastRow), "M*", Range("L84:L" & LastRow), Array("6 (macro)", "6")))
D_lilac = Application.Sum(Application.SumIfs(Range("G84:G" & LastRow), Range("G84:G" & LastRow), "1", Range("K84:K" & LastRow), "D*", Range("L84:L" & LastRow), Array("18 (macro)", "18"))) + D_lilac = Application.Sum(Application.SumIfs(Range("G84:G" & LastRow), Range("G84:G" & LastRow), "1", Range("K84:K" & LastRow), "D*", Range("L84:L" & LastRow), Array("6 (macro)", "6")))
A_lilac = Application.Sum(Application.SumIfs(Range("G84:G" & LastRow), Range("G84:G" & LastRow), "1", Range("K84:K" & LastRow), "A*", Range("L84:L" & LastRow), Array("18 (macro)", "18"))) + A_lilac = Application.Sum(Application.SumIfs(Range("G84:G" & LastRow), Range("G84:G" & LastRow), "1", Range("K84:K" & LastRow), "A*", Range("L84:L" & LastRow), Array("6 (macro)", "6")))
Mem_lilac = Application.Sum(Application.SumIfs(Range("R84:R" & LastRow), Range("L84:L" & LastRow), Array("18 (macro)", "18")))

B_ltblue = Application.Sum(Application.SumIfs(Range("G84:G" & LastRow), Range("G84:G" & LastRow), "1", Range("K84:K" & LastRow), "B*", Range("L84:L" & LastRow), Array("22 (macro)", "22")))
M_ltblue = Application.Sum(Application.SumIfs(Range("G84:G" & LastRow), Range("G84:G" & LastRow), "1", Range("K84:K" & LastRow), "M*", Range("L84:L" & LastRow), Array("22 (macro)", "22")))
D_ltblue = Application.Sum(Application.SumIfs(Range("G84:G" & LastRow), Range("G84:G" & LastRow), "1", Range("K84:K" & LastRow), "D*", Range("L84:L" & LastRow), Array("22 (macro)", "22")))
A_ltblue = Application.Sum(Application.SumIfs(Range("G84:G" & LastRow), Range("G84:G" & LastRow), "1", Range("K84:K" & LastRow), "A*", Range("L84:L" & LastRow), Array("22 (macro)", "22")))
Mem_ltblue = Application.Sum(Application.SumIfs(Range("R84:R" & LastRow), Range("L84:L" & LastRow), Array("22 (macro)", "22")))

B_maize = Application.Sum(Application.SumIfs(Range("G84:G" & LastRow), Range("G84:G" & LastRow), "1", Range("K84:K" & LastRow), "B*", Range("L84:L" & LastRow), Array("4 (macro)", "4")))
M_maize = Application.Sum(Application.SumIfs(Range("G84:G" & LastRow), Range("G84:G" & LastRow), "1", Range("K84:K" & LastRow), "M*", Range("L84:L" & LastRow), Array("4 (macro)", "4")))
D_maize = Application.Sum(Application.SumIfs(Range("G84:G" & LastRow), Range("G84:G" & LastRow), "1", Range("K84:K" & LastRow), "D*", Range("L84:L" & LastRow), Array("4 (macro)", "4")))
A_maize = Application.Sum(Application.SumIfs(Range("G84:G" & LastRow), Range("G84:G" & LastRow), "1", Range("K84:K" & LastRow), "A*", Range("L84:L" & LastRow), Array("4 (macro)", "4")))
Mem_maize = Application.Sum(Application.SumIfs(Range("R84:R" & LastRow), Range("L84:L" & LastRow), Array("4 (macro)", "4")))

B_maroon = Application.Sum(Application.SumIfs(Range("G84:G" & LastRow), Range("G84:G" & LastRow), "1", Range("K84:K" & LastRow), "B*", Range("L84:L" & LastRow), Array("40 (macro)", "40")))
M_maroon = Application.Sum(Application.SumIfs(Range("G84:G" & LastRow), Range("G84:G" & LastRow), "1", Range("K84:K" & LastRow), "M*", Range("L84:L" & LastRow), Array("40 (macro)", "40")))
D_maroon = Application.Sum(Application.SumIfs(Range("G84:G" & LastRow), Range("G84:G" & LastRow), "1", Range("K84:K" & LastRow), "D*", Range("L84:L" & LastRow), Array("40 (macro)", "40")))
A_maroon = Application.Sum(Application.SumIfs(Range("G84:G" & LastRow), Range("G84:G" & LastRow), "1", Range("K84:K" & LastRow), "A*", Range("L84:L" & LastRow), Array("40 (macro)", "40")))
Mem_maroon = Application.Sum(Application.SumIfs(Range("R84:R" & LastRow), Range("L84:L" & LastRow), Array("40 (macro)", "40")))

B_navy = Application.Sum(Application.SumIfs(Range("G84:G" & LastRow), Range("G84:G" & LastRow), "1", Range("K84:K" & LastRow), "B*", Range("L84:L" & LastRow), Array("41 (macro)", "41")))
M_navy = Application.Sum(Application.SumIfs(Range("G84:G" & LastRow), Range("G84:G" & LastRow), "1", Range("K84:K" & LastRow), "M*", Range("L84:L" & LastRow), Array("41 (macro)", "41")))
D_navy = Application.Sum(Application.SumIfs(Range("G84:G" & LastRow), Range("G84:G" & LastRow), "1", Range("K84:K" & LastRow), "D*", Range("L84:L" & LastRow), Array("41 (macro)", "41")))
A_navy = Application.Sum(Application.SumIfs(Range("G84:G" & LastRow), Range("G84:G" & LastRow), "1", Range("K84:K" & LastRow), "A*", Range("L84:L" & LastRow), Array("41 (macro)", "41")))
Mem_navy = Application.Sum(Application.SumIfs(Range("R84:R" & LastRow), Range("L84:L" & LastRow), Array("41 (macro)", "41")))

B_nile = Application.Sum(Application.SumIfs(Range("G84:G" & LastRow), Range("G84:G" & LastRow), "1", Range("K84:K" & LastRow), "B*", Range("L84:L" & LastRow), Array("74 (macro)", "74")))
M_nile = Application.Sum(Application.SumIfs(Range("G84:G" & LastRow), Range("G84:G" & LastRow), "1", Range("K84:K" & LastRow), "M*", Range("L84:L" & LastRow), Array("74 (macro)", "74")))
D_nile = Application.Sum(Application.SumIfs(Range("G84:G" & LastRow), Range("G84:G" & LastRow), "1", Range("K84:K" & LastRow), "D*", Range("L84:L" & LastRow), Array("74 (macro)", "74")))
A_nile = Application.Sum(Application.SumIfs(Range("G84:G" & LastRow), Range("G84:G" & LastRow), "1", Range("K84:K" & LastRow), "A*", Range("L84:L" & LastRow), Array("74 (macro)", "74")))
Mem_nile = Application.Sum(Application.SumIfs(Range("R84:R" & LastRow), Range("L84:L" & LastRow), Array("74 (macro)", "74")))

B_olive = Application.Sum(Application.SumIfs(Range("G84:G" & LastRow), Range("G84:G" & LastRow), "1", Range("K84:K" & LastRow), "B*", Range("L84:L" & LastRow), Array("72 (macro)", "72")))
M_olive = Application.Sum(Application.SumIfs(Range("G84:G" & LastRow), Range("G84:G" & LastRow), "1", Range("K84:K" & LastRow), "M*", Range("L84:L" & LastRow), Array("72 (macro)", "72")))
D_olive = Application.Sum(Application.SumIfs(Range("G84:G" & LastRow), Range("G84:G" & LastRow), "1", Range("K84:K" & LastRow), "D*", Range("L84:L" & LastRow), Array("72 (macro)", "72")))
A_olive = Application.Sum(Application.SumIfs(Range("G84:G" & LastRow), Range("G84:G" & LastRow), "1", Range("K84:K" & LastRow), "A*", Range("L84:L" & LastRow), Array("72 (macro)", "72")))
Mem_olive = Application.Sum(Application.SumIfs(Range("R84:R" & LastRow), Range("L84:L" & LastRow), Array("72 (macro)", "72")))

B_orange = Application.Sum(Application.SumIfs(Range("G84:G" & LastRow), Range("G84:G" & LastRow), "1", Range("K84:K" & LastRow), "B*", Range("L84:L" & LastRow), Array("28 (macro)", "28")))
M_orange = Application.Sum(Application.SumIfs(Range("G84:G" & LastRow), Range("G84:G" & LastRow), "1", Range("K84:K" & LastRow), "M*", Range("L84:L" & LastRow), Array("28 (macro)", "28")))
D_orange = Application.Sum(Application.SumIfs(Range("G84:G" & LastRow), Range("G84:G" & LastRow), "1", Range("K84:K" & LastRow), "D*", Range("L84:L" & LastRow), Array("28 (macro)", "28")))
A_orange = Application.Sum(Application.SumIfs(Range("G84:G" & LastRow), Range("G84:G" & LastRow), "1", Range("K84:K" & LastRow), "A*", Range("L84:L" & LastRow), Array("28 (macro)", "28")))
Mem_orange = Application.Sum(Application.SumIfs(Range("R84:R" & LastRow), Range("L84:L" & LastRow), Array("28 (macro)", "28")))

B_peacock = Application.Sum(Application.SumIfs(Range("G84:G" & LastRow), Range("G84:G" & LastRow), "1", Range("K84:K" & LastRow), "B*", Range("L84:L" & LastRow), Array("66 (macro)", "66")))
M_peacock = Application.Sum(Application.SumIfs(Range("G84:G" & LastRow), Range("G84:G" & LastRow), "1", Range("K84:K" & LastRow), "M*", Range("L84:L" & LastRow), Array("66 (macro)", "66")))
D_peacock = Application.Sum(Application.SumIfs(Range("G84:G" & LastRow), Range("G84:G" & LastRow), "1", Range("K84:K" & LastRow), "D*", Range("L84:L" & LastRow), Array("66 (macro)", "66")))
A_peacock = Application.Sum(Application.SumIfs(Range("G84:G" & LastRow), Range("G84:G" & LastRow), "1", Range("K84:K" & LastRow), "A*", Range("L84:L" & LastRow), Array("66 (macro)", "66")))
Mem_peacock = Application.Sum(Application.SumIfs(Range("R84:R" & LastRow), Range("L84:L" & LastRow), Array("66 (macro)", "66")))

B_pink = Application.Sum(Application.SumIfs(Range("G84:G" & LastRow), Range("G84:G" & LastRow), "1", Range("K84:K" & LastRow), "B*", Range("L84:L" & LastRow), Array("56 (macro)", "56")))
M_pink = Application.Sum(Application.SumIfs(Range("G84:G" & LastRow), Range("G84:G" & LastRow), "1", Range("K84:K" & LastRow), "M*", Range("L84:L" & LastRow), Array("56 (macro)", "56")))
D_pink = Application.Sum(Application.SumIfs(Range("G84:G" & LastRow), Range("G84:G" & LastRow), "1", Range("K84:K" & LastRow), "D*", Range("L84:L" & LastRow), Array("56 (macro)", "56")))
A_pink = Application.Sum(Application.SumIfs(Range("G84:G" & LastRow), Range("G84:G" & LastRow), "1", Range("K84:K" & LastRow), "A*", Range("L84:L" & LastRow), Array("56 (macro)", "56")))
Mem_pink = Application.Sum(Application.SumIfs(Range("R84:R" & LastRow), Range("L84:L" & LastRow), Array("56 (macro)", "56")))

B_purple = Application.Sum(Application.SumIfs(Range("G84:G" & LastRow), Range("G84:G" & LastRow), "1", Range("K84:K" & LastRow), "B*", Range("L84:L" & LastRow), Array("50 (macro)", "50")))
M_purple = Application.Sum(Application.SumIfs(Range("G84:G" & LastRow), Range("G84:G" & LastRow), "1", Range("K84:K" & LastRow), "M*", Range("L84:L" & LastRow), Array("50 (macro)", "50")))
D_purple = Application.Sum(Application.SumIfs(Range("G84:G" & LastRow), Range("G84:G" & LastRow), "1", Range("K84:K" & LastRow), "D*", Range("L84:L" & LastRow), Array("50 (macro)", "50")))
A_purple = Application.Sum(Application.SumIfs(Range("G84:G" & LastRow), Range("G84:G" & LastRow), "1", Range("K84:K" & LastRow), "A*", Range("L84:L" & LastRow), Array("50 (macro)", "50")))
Mem_purple = Application.Sum(Application.SumIfs(Range("R84:R" & LastRow), Range("L84:L" & LastRow), Array("50 (macro)", "50")))

B_red = Application.Sum(Application.SumIfs(Range("G84:G" & LastRow), Range("G84:G" & LastRow), "1", Range("K84:K" & LastRow), "B*", Range("L84:L" & LastRow), Array("16 (macro)", "16")))
M_red = Application.Sum(Application.SumIfs(Range("G84:G" & LastRow), Range("G84:G" & LastRow), "1", Range("K84:K" & LastRow), "M*", Range("L84:L" & LastRow), Array("16 (macro)", "16")))
D_red = Application.Sum(Application.SumIfs(Range("G84:G" & LastRow), Range("G84:G" & LastRow), "1", Range("K84:K" & LastRow), "D*", Range("L84:L" & LastRow), Array("16 (macro)", "16")))
A_red = Application.Sum(Application.SumIfs(Range("G84:G" & LastRow), Range("G84:G" & LastRow), "1", Range("K84:K" & LastRow), "A*", Range("L84:L" & LastRow), Array("16 (macro)", "16")))
Mem_reb = Application.Sum(Application.SumIfs(Range("R84:R" & LastRow), Range("L84:L" & LastRow), Array("16 (macro)", "16")))

B_sage = Application.Sum(Application.SumIfs(Range("G84:G" & LastRow), Range("G84:G" & LastRow), "1", Range("K84:K" & LastRow), "B*", Range("L84:L" & LastRow), Array("68 (macro)", "68")))
M_sage = Application.Sum(Application.SumIfs(Range("G84:G" & LastRow), Range("G84:G" & LastRow), "1", Range("K84:K" & LastRow), "M*", Range("L84:L" & LastRow), Array("68 (macro)", "68")))
D_sage = Application.Sum(Application.SumIfs(Range("G84:G" & LastRow), Range("G84:G" & LastRow), "1", Range("K84:K" & LastRow), "D*", Range("L84:L" & LastRow), Array("68 (macro)", "68")))
A_sage = Application.Sum(Application.SumIfs(Range("G84:G" & LastRow), Range("G84:G" & LastRow), "1", Range("K84:K" & LastRow), "A*", Range("L84:L" & LastRow), Array("68 (macro)", "68")))
Mem_sage = Application.Sum(Application.SumIfs(Range("R84:R" & LastRow), Range("L84:L" & LastRow), Array("68 (macro)", "68")))

B_salmon = Application.Sum(Application.SumIfs(Range("G84:G" & LastRow), Range("G84:G" & LastRow), "1", Range("K84:K" & LastRow), "B*", Range("L84:L" & LastRow), Array("70 (macro)", "70")))
M_salmon = Application.Sum(Application.SumIfs(Range("G84:G" & LastRow), Range("G84:G" & LastRow), "1", Range("K84:K" & LastRow), "M*", Range("L84:L" & LastRow), Array("70 (macro)", "70")))
D_salmon = Application.Sum(Application.SumIfs(Range("G84:G" & LastRow), Range("G84:G" & LastRow), "1", Range("K84:K" & LastRow), "D*", Range("L84:L" & LastRow), Array("70 (macro)", "70")))
A_salmon = Application.Sum(Application.SumIfs(Range("G84:G" & LastRow), Range("G84:G" & LastRow), "1", Range("K84:K" & LastRow), "A*", Range("L84:L" & LastRow), Array("70 (macro)", "70")))
Mem_salmon = Application.Sum(Application.SumIfs(Range("R84:R" & LastRow), Range("L84:L" & LastRow), Array("70 (macro)", "70")))

B_silver = Application.Sum(Application.SumIfs(Range("G84:G" & LastRow), Range("G84:G" & LastRow), "1", Range("K84:K" & LastRow), "B*", Range("L84:L" & LastRow), Array("62 (macro)", "62")))
M_silver = Application.Sum(Application.SumIfs(Range("G84:G" & LastRow), Range("G84:G" & LastRow), "1", Range("K84:K" & LastRow), "M*", Range("L84:L" & LastRow), Array("62 (macro)", "62")))
D_silver = Application.Sum(Application.SumIfs(Range("G84:G" & LastRow), Range("G84:G" & LastRow), "1", Range("K84:K" & LastRow), "D*", Range("L84:L" & LastRow), Array("62 (macro)", "62")))
A_silver = Application.Sum(Application.SumIfs(Range("G84:G" & LastRow), Range("G84:G" & LastRow), "1", Range("K84:K" & LastRow), "A*", Range("L84:L" & LastRow), Array("62 (macro)", "62")))
Mem_silver = Application.Sum(Application.SumIfs(Range("R84:R" & LastRow), Range("L84:L" & LastRow), Array("62 (macro)", "62")))

B_yale = Application.Sum(Application.SumIfs(Range("G84:G" & LastRow), Range("G84:G" & LastRow), "1", Range("K84:K" & LastRow), "B*", Range("L84:L" & LastRow), Array("71 (macro)", "71")))
M_yale = Application.Sum(Application.SumIfs(Range("G84:G" & LastRow), Range("G84:G" & LastRow), "1", Range("K84:K" & LastRow), "M*", Range("L84:L" & LastRow), Array("71 (macro)", "71")))
D_yale = Application.Sum(Application.SumIfs(Range("G84:G" & LastRow), Range("G84:G" & LastRow), "1", Range("K84:K" & LastRow), "D*", Range("L84:L" & LastRow), Array("71 (macro)", "71")))
A_yale = Application.Sum(Application.SumIfs(Range("G84:G" & LastRow), Range("G84:G" & LastRow), "1", Range("K84:K" & LastRow), "A*", Range("L84:L" & LastRow), Array("71 (macro)", "71")))
Mem_yale = Application.Sum(Application.SumIfs(Range("R84:R" & LastRow), Range("L84:L" & LastRow), Array("71 (macro)", "71")))

B_white = Application.Sum(Application.SumIfs(Range("G84:G" & LastRow), Range("G84:G" & LastRow), "1", Range("K84:K" & LastRow), "B*", Range("L84:L" & LastRow), Array("2 (macro)", "2")))
M_white = Application.Sum(Application.SumIfs(Range("G84:G" & LastRow), Range("G84:G" & LastRow), "1", Range("K84:K" & LastRow), "M*", Range("L84:L" & LastRow), Array("2 (macro)", "2")))
D_white = Application.Sum(Application.SumIfs(Range("G84:G" & LastRow), Range("G84:G" & LastRow), "1", Range("K84:K" & LastRow), "D*", Range("L84:L" & LastRow), Array("2 (macro)", "2")))
A_white = Application.Sum(Application.SumIfs(Range("G84:G" & LastRow), Range("G84:G" & LastRow), "1", Range("K84:K" & LastRow), "A*", Range("L84:L" & LastRow), Array("2 (macro)", "2")))
Mem_white = Application.Sum(Application.SumIfs(Range("R84:R" & LastRow), Range("L84:L" & LastRow), Array("2 (macro)", "2")))

If B_alice > 0 Then B_tasselCount = B_tasselCount & "Alice:" & vbTab & vbTab & B_alice & vbLf
If B_apricot > 0 Then B_tasselCount = B_tasselCount & "Apricot" & vbTab & vbTab & B_apricot & vbLf
If B_aqua > 0 Then B_tasselCount = B_tasselCount & "Aqua" & vbTab & vbTab & B_aqua & vbLf
If B_black > 0 Then B_tasselCount = B_tasselCount & "Black" & vbTab & vbTab & B_black & vbLf
If B_brown > 0 Then B_tasselCount = B_tasselCount & "Brown" & vbTab & vbTab & B_brown & vbLf
If B_burntorange > 0 Then B_tasselCount = B_tasselCount & "Burnt Orange" & vbTab & B_burntorange & vbLf
If B_cardinal > 0 Then B_tasselCount = B_tasselCount & "Cardinal/Wine" & vbTab & B_cardinal & vbLf
If B_copper > 0 Then B_tasselCount = B_tasselCount & "Copper" & vbTab & vbTab & B_copper & vbLf
If B_citron > 0 Then B_tasselCount = B_tasselCount & "Citron" & vbTab & vbTab & B_citron & vbLf
If B_cream > 0 Then B_tasselCount = B_tasselCount & "Cream" & vbTab & vbTab & B_cream & vbLf
If B_drab > 0 Then B_tasselCount = B_tasselCount & "Drab" & vbTab & vbTab & B_drab & vbLf
If B_forest > 0 Then B_tasselCount = B_tasselCount & "Forest Green" & vbTab & B_forest & vbLf
If B_gold > 0 Then B_tasselCount = B_tasselCount & "Gold" & vbTab & vbTab & B_gold & vbLf
If B_gray > 0 Then B_tasselCount = B_tasselCount & "Gray" & vbTab & vbTab & B_gray & vbLf
If B_kelly > 0 Then B_tasselCount = B_tasselCount & "Kelly Green" & vbTab & B_kelly & vbLf
If B_lemon > 0 Then B_tasselCount = B_tasselCount & "Lemon" & vbTab & vbTab & B_lemon & vbLf
If B_lilac > 0 Then B_tasselCount = B_tasselCount & "Lilac" & vbTab & vbTab & B_lilac & vbLf
If B_ltblue > 0 Then B_tasselCount = B_tasselCount & "Light Blue" & vbTab & B_ltblue & vbLf
If B_maize > 0 Then B_tasselCount = B_tasselCount & "Maize" & vbTab & vbTab & B_maize & vbLf
If B_maroon > 0 Then B_tasselCount = B_tasselCount & "Maroon" & vbTab & vbTab & B_maroon & vbLf
If B_navy > 0 Then B_tasselCount = B_tasselCount & "Navy" & vbTab & vbTab & B_navy & vbLf
If B_nile > 0 Then B_tasselCount = B_tasselCount & "Nile Green" & vbTab & B_nile & vbLf
If B_olive > 0 Then B_tasselCount = B_tasselCount & "Olive Green" & vbTab & B_olive & vbLf
If B_orange > 0 Then B_tasselCount = B_tasselCount & "Orange" & vbTab & vbTab & B_orange & vbLf
If B_peacock > 0 Then B_tasselCount = B_tasselCount & "Peacock Blue" & vbTab & B_peacock & vbLf
If B_pink > 0 Then B_tasselCount = B_tasselCount & "Pink" & vbTab & vbTab & B_pink & vbLf
If B_purple > 0 Then B_tasselCount = B_tasselCount & "Purple" & vbTab & vbTab & B_purple & vbLf
If B_red > 0 Then B_tasselCount = B_tasselCount & "Red" & vbTab & vbTab & B_red & vbLf
If B_sage > 0 Then B_tasselCount = B_tasselCount & "Sage Green" & vbTab & B_sage & vbLf
If B_salmon > 0 Then B_tasselCount = B_tasselCount & "Salmon" & vbTab & vbTab & B_salmon & vbLf
If B_silver > 0 Then B_tasselCount = B_tasselCount & "Silver" & vbTab & vbTab & B_silver & vbLf
If B_white > 0 Then B_tasselCount = B_tasselCount & "White" & vbTab & vbTab & B_white & vbLf
If B_yale > 0 Then B_tasselCount = B_tasselCount & "Yale" & vbTab & vbTab & B_yale & vbLf
If B_tasselCount = "" Then B_tasselCount = "N/A"

If M_alice > 0 Then M_tasselCount = M_tasselCount & "Alice:" & vbTab & vbTab & M_alice & vbLf
If M_apricot > 0 Then M_tasselCount = M_tasselCount & "Apricot" & vbTab & vbTab & M_apricot & vbLf
If M_aqua > 0 Then M_tasselCount = M_tasselCount & "Aqua" & vbTab & vbTab & M_aqua & vbLf
If M_black > 0 Then M_tasselCount = M_tasselCount & "Black" & vbTab & vbTab & M_black & vbLf
If M_brown > 0 Then M_tasselCount = M_tasselCount & "Brown" & vbTab & vbTab & M_brown & vbLf
If M_burntorange > 0 Then M_tasselCount = M_tasselCount & "Burnt Orange" & vbTab & M_burntorange & vbLf
If M_cardinal > 0 Then M_tasselCount = M_tasselCount & "Cardinal/Wine" & vbTab & M_cardinal & vbLf
If M_copper > 0 Then M_tasselCount = M_tasselCount & "Copper" & vbTab & vbTab & M_copper & vbLf
If M_citron > 0 Then M_tasselCount = M_tasselCount & "Citron" & vbTab & vbTab & M_citron & vbLf
If M_cream > 0 Then M_tasselCount = M_tasselCount & "Cream" & vbTab & vbTab & M_cream & vbLf
If M_drab > 0 Then M_tasselCount = M_tasselCount & "Drab" & vbTab & vbTab & M_drab & vbLf
If M_forest > 0 Then M_tasselCount = M_tasselCount & "Forest Green" & vbTab & M_forest & vbLf
If M_gold > 0 Then M_tasselCount = M_tasselCount & "Gold" & vbTab & vbTab & M_gold & vbLf
If M_gray > 0 Then M_tasselCount = M_tasselCount & "Gray" & vbTab & vbTab & M_gray & vbLf
If M_kelly > 0 Then M_tasselCount = M_tasselCount & "Kelly Green" & vbTab & M_kelly & vbLf
If M_lemon > 0 Then M_tasselCount = M_tasselCount & "Lemon" & vbTab & vbTab & M_lemon & vbLf
If M_lilac > 0 Then M_tasselCount = M_tasselCount & "Lilac" & vbTab & vbTab & M_lilac & vbLf
If M_ltblue > 0 Then M_tasselCount = M_tasselCount & "Light Blue" & vbTab & M_ltblue & vbLf
If M_maize > 0 Then M_tasselCount = M_tasselCount & "Maize" & vbTab & vbTab & M_maize & vbLf
If M_maroon > 0 Then M_tasselCount = M_tasselCount & "Maroon" & vbTab & vbTab & M_maroon & vbLf
If M_navy > 0 Then M_tasselCount = M_tasselCount & "Navy" & vbTab & vbTab & M_navy & vbLf
If M_nile > 0 Then M_tasselCount = M_tasselCount & "Nile Green" & vbTab & M_nile & vbLf
If M_olive > 0 Then M_tasselCount = M_tasselCount & "Olive Green" & vbTab & M_olive & vbLf
If M_orange > 0 Then M_tasselCount = M_tasselCount & "Orange" & vbTab & vbTab & M_orange & vbLf
If M_peacock > 0 Then M_tasselCount = M_tasselCount & "Peacock Blue" & vbTab & M_peacock & vbLf
If M_pink > 0 Then M_tasselCount = M_tasselCount & "Pink" & vbTab & vbTab & M_pink & vbLf
If M_purple > 0 Then M_tasselCount = M_tasselCount & "Purple" & vbTab & vbTab & M_purple & vbLf
If M_red > 0 Then M_tasselCount = M_tasselCount & "Red" & vbTab & vbTab & M_red & vbLf
If M_sage > 0 Then M_tasselCount = M_tasselCount & "Sage Green" & vbTab & M_sage & vbLf
If M_salmon > 0 Then M_tasselCount = M_tasselCount & "Salmon" & vbTab & vbTab & M_salmon & vbLf
If M_silver > 0 Then M_tasselCount = M_tasselCount & "Silver" & vbTab & vbTab & M_silver & vbLf
If M_white > 0 Then M_tasselCount = M_tasselCount & "White" & vbTab & vbTab & M_white & vbLf
If M_yale > 0 Then M_tasselCount = M_tasselCount & "Yale" & vbTab & vbTab & M_yale & vbLf
If M_tasselCount = "" Then M_tasselCount = "N/A"

If D_alice > 0 Then D_tasselCount = D_tasselCount & "Alice:" & vbTab & vbTab & D_alice & vbLf
If D_apricot > 0 Then D_tasselCount = D_tasselCount & "Apricot" & vbTab & vbTab & D_apricot & vbLf
If D_aqua > 0 Then D_tasselCount = D_tasselCount & "Aqua" & vbTab & vbTab & D_aqua & vbLf
If D_black > 0 Then D_tasselCount = D_tasselCount & "Black" & vbTab & vbTab & D_black & vbLf
If D_brown > 0 Then D_tasselCount = D_tasselCount & "Brown" & vbTab & vbTab & D_brown & vbLf
If D_burntorange > 0 Then D_tasselCount = D_tasselCount & "Burnt Orange" & vbTab & D_burntorange & vbLf
If D_cardinal > 0 Then D_tasselCount = D_tasselCount & "Cardinal/Wine" & vbTab & D_cardinal & vbLf
If D_copper > 0 Then D_tasselCount = D_tasselCount & "Copper" & vbTab & vbTab & D_copper & vbLf
If D_citron > 0 Then D_tasselCount = D_tasselCount & "Citron" & vbTab & vbTab & D_citron & vbLf
If D_cream > 0 Then D_tasselCount = D_tasselCount & "Cream" & vbTab & vbTab & D_cream & vbLf
If D_drab > 0 Then D_tasselCount = D_tasselCount & "Drab" & vbTab & vbTab & D_drab & vbLf
If D_forest > 0 Then D_tasselCount = D_tasselCount & "Forest Green" & vbTab & D_forest & vbLf
If D_gold > 0 Then D_tasselCount = D_tasselCount & "Gold" & vbTab & vbTab & D_gold & vbLf
If D_gray > 0 Then D_tasselCount = D_tasselCount & "Gray" & vbTab & vbTab & D_gray & vbLf
If D_kelly > 0 Then D_tasselCount = D_tasselCount & "Kelly Green" & vbTab & D_kelly & vbLf
If D_lemon > 0 Then D_tasselCount = D_tasselCount & "Lemon" & vbTab & vbTab & D_lemon & vbLf
If D_lilac > 0 Then D_tasselCount = D_tasselCount & "Lilac" & vbTab & vbTab & D_lilac & vbLf
If D_ltblue > 0 Then D_tasselCount = D_tasselCount & "Light Blue" & vbTab & D_ltblue & vbLf
If D_maize > 0 Then D_tasselCount = D_tasselCount & "Maize" & vbTab & vbTab & D_maize & vbLf
If D_maroon > 0 Then D_tasselCount = D_tasselCount & "Maroon" & vbTab & vbTab & D_maroon & vbLf
If D_navy > 0 Then D_tasselCount = D_tasselCount & "Navy" & vbTab & vbTab & D_navy & vbLf
If D_nile > 0 Then D_tasselCount = D_tasselCount & "Nile Green" & vbTab & D_nile & vbLf
If D_olive > 0 Then D_tasselCount = D_tasselCount & "Olive Green" & vbTab & D_olive & vbLf
If D_orange > 0 Then D_tasselCount = D_tasselCount & "Orange" & vbTab & vbTab & D_orange & vbLf
If D_peacock > 0 Then D_tasselCount = D_tasselCount & "Peacock Blue" & vbTab & D_peacock & vbLf
If D_pink > 0 Then D_tasselCount = D_tasselCount & "Pink" & vbTab & vbTab & D_pink & vbLf
If D_purple > 0 Then D_tasselCount = D_tasselCount & "Purple" & vbTab & vbTab & D_purple & vbLf
If D_red > 0 Then D_tasselCount = D_tasselCount & "Red" & vbTab & vbTab & D_red & vbLf
If D_sage > 0 Then D_tasselCount = D_tasselCount & "Sage Green" & vbTab & D_sage & vbLf
If D_salmon > 0 Then D_tasselCount = D_tasselCount & "Salmon" & vbTab & vbTab & D_salmon & vbLf
If D_silver > 0 Then D_tasselCount = D_tasselCount & "Silver" & vbTab & vbTab & D_silver & vbLf
If D_white > 0 Then D_tasselCount = D_tasselCount & "White" & vbTab & vbTab & D_white & vbLf
If D_yale > 0 Then D_tasselCount = D_tasselCount & "Yale" & vbTab & vbTab & D_yale & vbLf
If D_tasselCount = "" Then D_tasselCount = "N/A"

If A_alice > 0 Then A_tasselCount = A_tasselCount & "Alice:" & vbTab & vbTab & A_alice & vbLf
If A_apricot > 0 Then A_tasselCount = A_tasselCount & "Apricot" & vbTab & vbTab & A_apricot & vbLf
If A_aqua > 0 Then A_tasselCount = A_tasselCount & "Aqua" & vbTab & vbTab & A_aqua & vbLf
If A_black > 0 Then A_tasselCount = A_tasselCount & "Black" & vbTab & vbTab & A_black & vbLf
If A_brown > 0 Then A_tasselCount = A_tasselCount & "Brown" & vbTab & vbTab & A_brown & vbLf
If A_burntorange > 0 Then A_tasselCount = A_tasselCount & "Burnt Orange" & vbTab & A_burntorange & vbLf
If A_cardinal > 0 Then A_tasselCount = A_tasselCount & "Cardinal/Wine" & vbTab & A_cardinal & vbLf
If A_copper > 0 Then A_tasselCount = A_tasselCount & "Copper" & vbTab & vbTab & A_copper & vbLf
If A_citron > 0 Then A_tasselCount = A_tasselCount & "Citron" & vbTab & vbTab & A_citron & vbLf
If A_cream > 0 Then A_tasselCount = A_tasselCount & "Cream" & vbTab & vbTab & A_cream & vbLf
If A_drab > 0 Then A_tasselCount = A_tasselCount & "Drab" & vbTab & vbTab & A_drab & vbLf
If A_forest > 0 Then A_tasselCount = A_tasselCount & "Forest Green" & vbTab & A_forest & vbLf
If A_gold > 0 Then A_tasselCount = A_tasselCount & "Gold" & vbTab & vbTab & A_gold & vbLf
If A_gray > 0 Then A_tasselCount = A_tasselCount & "Gray" & vbTab & vbTab & A_gray & vbLf
If A_kelly > 0 Then A_tasselCount = A_tasselCount & "Kelly Green" & vbTab & A_kelly & vbLf
If A_lemon > 0 Then A_tasselCount = A_tasselCount & "Lemon" & vbTab & vbTab & A_lemon & vbLf
If A_lilac > 0 Then A_tasselCount = A_tasselCount & "Lilac" & vbTab & vbTab & A_lilac & vbLf
If A_ltblue > 0 Then A_tasselCount = A_tasselCount & "Light Blue" & vbTab & A_ltblue & vbLf
If A_maize > 0 Then A_tasselCount = A_tasselCount & "Maize" & vbTab & vbTab & A_maize & vbLf
If A_maroon > 0 Then A_tasselCount = A_tasselCount & "Maroon" & vbTab & vbTab & A_maroon & vbLf
If A_navy > 0 Then A_tasselCount = A_tasselCount & "Navy" & vbTab & vbTab & A_navy & vbLf
If A_nile > 0 Then A_tasselCount = A_tasselCount & "Nile Green" & vbTab & A_nile & vbLf
If A_olive > 0 Then A_tasselCount = A_tasselCount & "Olive Green" & vbTab & A_olive & vbLf
If A_orange > 0 Then A_tasselCount = A_tasselCount & "Orange" & vbTab & vbTab & A_orange & vbLf
If A_peacock > 0 Then A_tasselCount = A_tasselCount & "Peacock Blue" & vbTab & A_peacock & vbLf
If A_pink > 0 Then A_tasselCount = A_tasselCount & "Pink" & vbTab & vbTab & A_pink & vbLf
If A_purple > 0 Then A_tasselCount = A_tasselCount & "Purple" & vbTab & vbTab & A_purple & vbLf
If A_red > 0 Then A_tasselCount = A_tasselCount & "Red" & vbTab & vbTab & A_red & vbLf
If A_sage > 0 Then A_tasselCount = A_tasselCount & "Sage Green" & vbTab & A_sage & vbLf
If A_salmon > 0 Then A_tasselCount = A_tasselCount & "Salmon" & vbTab & vbTab & A_salmon & vbLf
If A_silver > 0 Then A_tasselCount = A_tasselCount & "Silver" & vbTab & vbTab & A_silver & vbLf
If A_white > 0 Then A_tasselCount = A_tasselCount & "White" & vbTab & vbTab & A_white & vbLf
If A_yale > 0 Then A_tasselCount = A_tasselCount & "Yale" & vbTab & vbTab & A_yale & vbLf
If A_tasselCount = "" Then B_tasselCount = "N/A"

If Mem_alice > 0 Then Mem_tasselCount = Mem_tasselCount & "Alice:" & vbTab & vbTab & Mem_alice & vbLf
If Mem_apricot > 0 Then Mem_tasselCount = Mem_tasselCount & "Apricot" & vbTab & vbTab & Mem_apricot & vbLf
If Mem_aqua > 0 Then Mem_tasselCount = Mem_tasselCount & "Aqua" & vbTab & vbTab & Mem_aqua & vbLf
If Mem_black > 0 Then Mem_tasselCount = Mem_tasselCount & "Black" & vbTab & vbTab & Mem_black & vbLf
If Mem_brown > 0 Then Mem_tasselCount = Mem_tasselCount & "Brown" & vbTab & vbTab & Mem_brown & vbLf
If Mem_burntorange > 0 Then Mem_tasselCount = Mem_tasselCount & "Burnt Orange" & vbTab & Mem_burntorange & vbLf
If Mem_cardinal > 0 Then Mem_tasselCount = Mem_tasselCount & "Cardinal/Wine" & vbTab & Mem_cardinal & vbLf
If Mem_copper > 0 Then Mem_tasselCount = Mem_tasselCount & "Copper" & vbTab & vbTab & Mem_copper & vbLf
If Mem_citron > 0 Then Mem_tasselCount = Mem_tasselCount & "Citron" & vbTab & vbTab & Mem_citron & vbLf
If Mem_cream > 0 Then Mem_tasselCount = Mem_tasselCount & "Cream" & vbTab & vbTab & Mem_cream & vbLf
If Mem_drab > 0 Then Mem_tasselCount = Mem_tasselCount & "Drab" & vbTab & vbTab & Mem_drab & vbLf
If Mem_forest > 0 Then Mem_tasselCount = Mem_tasselCount & "Forest Green" & vbTab & Mem_forest & vbLf
If Mem_gold > 0 Then Mem_tasselCount = Mem_tasselCount & "Gold" & vbTab & vbTab & Mem_gold & vbLf
If Mem_gray > 0 Then Mem_tasselCount = Mem_tasselCount & "Gray" & vbTab & vbTab & Mem_gray & vbLf
If Mem_kelly > 0 Then Mem_tasselCount = Mem_tasselCount & "Kelly Green" & vbTab & Mem_kelly & vbLf
If Mem_lemon > 0 Then Mem_tasselCount = Mem_tasselCount & "Lemon" & vbTab & vbTab & Mem_lemon & vbLf
If Mem_lilac > 0 Then Mem_tasselCount = Mem_tasselCount & "Lilac" & vbTab & vbTab & Mem_lilac & vbLf
If Mem_ltblue > 0 Then Mem_tasselCount = Mem_tasselCount & "Light Blue" & vbTab & Mem_ltblue & vbLf
If Mem_maize > 0 Then Mem_tasselCount = Mem_tasselCount & "Maize" & vbTab & vbTab & Mem_maize & vbLf
If Mem_maroon > 0 Then Mem_tasselCount = Mem_tasselCount & "Maroon" & vbTab & vbTab & Mem_maroon & vbLf
If Mem_navy > 0 Then Mem_tasselCount = Mem_tasselCount & "Navy" & vbTab & vbTab & Mem_navy & vbLf
If Mem_nile > 0 Then Mem_tasselCount = Mem_tasselCount & "Nile Green" & vbTab & Mem_nile & vbLf
If Mem_olive > 0 Then Mem_tasselCount = Mem_tasselCount & "Olive Green" & vbTab & Mem_olive & vbLf
If Mem_orange > 0 Then Mem_tasselCount = Mem_tasselCount & "Orange" & vbTab & vbTab & Mem_orange & vbLf
If Mem_peacock > 0 Then Mem_tasselCount = Mem_tasselCount & "Peacock Blue" & vbTab & Mem_peacock & vbLf
If Mem_pink > 0 Then Mem_tasselCount = Mem_tasselCount & "Pink" & vbTab & vbTab & Mem_pink & vbLf
If Mem_purple > 0 Then Mem_tasselCount = Mem_tasselCount & "Purple" & vbTab & vbTab & Mem_purple & vbLf
If Mem_red > 0 Then Mem_tasselCount = Mem_tasselCount & "Red" & vbTab & vbTab & Mem_red & vbLf
If Mem_sage > 0 Then Mem_tasselCount = Mem_tasselCount & "Sage Green" & vbTab & Mem_sage & vbLf
If Mem_salmon > 0 Then Mem_tasselCount = Mem_tasselCount & "Salmon" & vbTab & vbTab & Mem_salmon & vbLf
If Mem_silver > 0 Then Mem_tasselCount = Mem_tasselCount & "Silver" & vbTab & vbTab & Mem_silver & vbLf
If Mem_white > 0 Then Mem_tasselCount = Mem_tasselCount & "White" & vbTab & vbTab & Mem_white & vbLf
If Mem_yale > 0 Then Mem_tasselCount = Mem_tasselCount & "Yale" & vbTab & vbTab & Mem_yale & vbLf
If Mem_tasselCount = "" Then Mem_tasselCount = "N/A"

    Label1.Caption = B_tasselCount
    Label2.Caption = M_tasselCount
    Label3.Caption = D_tasselCount
    Label4.Caption = A_tasselCount
    Label5.Caption = Mem_tasselCount
End Sub
\$\endgroup\$
5
  • 2
    \$\begingroup\$ Unfortunately you didn't give more context, but this code seems to cry for a loop. \$\endgroup\$ – πάντα ῥεῖ Jun 25 '18 at 18:15
  • \$\begingroup\$ @πάνταῥεῖ What additional information can I provide? \$\endgroup\$ – Robby Jun 25 '18 at 18:34
  • \$\begingroup\$ I don't understand what you did with the lilac series. Why didn't you combine the two arrays of two into one array of four and why is the first line different? In fact, B_lilac is the only line that looks correct; the others look like you are tring to add a boolean to the total. \$\endgroup\$ – user66882 Jun 25 '18 at 22:42
  • \$\begingroup\$ Do you know what worksheet you are on or is this intended to be used on whichever worksheet is the activesheet (doubtful due the sub's private declaration). \$\endgroup\$ – user66882 Jun 25 '18 at 22:50
  • \$\begingroup\$ Is it Mem_reb or Mem_red ? \$\endgroup\$ – user66882 Jun 25 '18 at 23:55
1
\$\begingroup\$

The Actual Question

There are probably better ways to do this, but let me sstart with your immediate question:

If you look closely at the first part of the sub, you will see that all the summations only differ in two places, the letter in the second criterion and the number in the last criterion. So you could write function encapsulating this taking the letter and number as parameters and returning the sum.

In the second part, you basically make take the same decision over and over again, only for a different variable and appent to the the corresponding value to another variable. Instead you could define one function that returns the string you concatenate potentially given the header name, e.g. Apricot and the number to use.

Then you can make an array or a collection each for the characters and the numbers to evaluate and the headers to use, best in a separate function, and loop over these concatenating the strings while you do this. This will considerable reduce the amount of code.

The function for the four main categories (not Mem) will look something like this:

Private Function MainCategoryOrderString(ByVal CategoryLetter As String) As String
    Dim itemColorCodes As Scripting.Dictionary
    Set itemColorCodes = AvailableColorCodes()
    Dim result As String
    Dim colorName As Variant
    For Each colorName in itemColorCodes.Keys()
        Dim ordersCount As Long
        ordersCount = NumberOfOrdersForMainCategory(CategoryLetter, itemColorCodes.Item(colorName))
        If ordersCount > 0 Then
            result = result & ColorOrderString(colorName, ordersCount)
        End If
    Next
    MainCategoryOrderString = result
End Function

The one for Mem would only differ in the function used to determine the orders count. Note that This assumes that the colors are provided by the function AvailableColorCodes in the form of a dictionary with the header names for the returned strings as keys and the numbers from the last criterion in the SUMIFS as the values. The function NumberOfOrdersForMainCategory would return the result of the SUMIFS calculation and the ColorOrderString function would return the formatted string used to concatenate.

Some Remarks on the Approach

Given that you are using worksheet functions so often, I wonder whether this problem could not better be solved a (hidden) worksheet. This would contain a column for the color names, one for the corresponding numbers and then fields for each calculation step for the different categories. The concatenation can be achieved using the CONCATENATE.

In any case, your approach will be slowed down by the frequent context switches between the VBA runtime and the Excel function evaluation engine. Another approach would be to load everythin into an array and then do the summation in VBA. However, this does not sound too reasonable for this problem, I think.

General Remarks

Since this is CR, a few general remarks regarding the coding style.

As already addressed in the first section, your code could follow a little more the DRY principle. (Do not repeat yourself.) Whenever you see something very similar or or even identical in your code that appears more than two or three times, you should ask yourself whether you can extract it into a single programming unit like a function, sub or even a class. This will help when you have to add it another time, e.g. here if you have a new color. However, be aware that you have to be a little wary applying the DRY principle because it reduces flexibility. (Here, the benefits are very much in its favour.)

Moreover, your code seems to not separate responsibilities too well. The single responsibility principle (SRP) basically states that each programming unit should have one responsibility only. Here, your sub has the responsibility to manage the categories of orders there are (the four letters and MeM), which colors there are, how to determine the number or orders and what string to return. In the examplatory function I provided in this answer, each of these responsibilities is handled by another function or sub: the sub calling the function knows about the categories, the function AvailableColorCodes knows the colors, the function NumberOfOrdersForMainCategory knows how to determine the number of order and the function ColorOrderString knows how to format the individual order strings for each color. The function itself knows how to put together the whole string for a (main) category. With such a separation of concerns you only ever have to change one place in case something changes. E.g. is there is a new color, you just have to add a further entry to the dictionary in the AvailableColorCodes function.

In your code you are using a lot of explicit cell references. It can be very beneficial to use named ranges instead. You can refer to them by name, which is stable even if you add a new column.

I have three further points regarding readibility and hence maintainability:

  1. Proper indentation helps readibility a lot. Recognizing the extend of blocks like If blocks, For block or subs is much easier if the inner block is indented further.
  2. Using single line if statements is hurting readibility and thus discouraged. Properly indented If blocks are much easier to comprehend.
  3. It is usually preferable to define each variable an its own line. This makes the code somewhat more structured and makes it more apparant if you miss to type a variable.

I guess the last two problems are a consequence of trying to reduce the number of lines in the sub.

There might be more to find, but I will leave it with this.

\$\endgroup\$

Not the answer you're looking for? Browse other questions tagged or ask your own question.