Sep 22 2022 11:00 AM - edited Sep 22 2022 11:02 AM
how shorted VBA down on a button on file
'WEEK 1
Worksheets("1").Range("K2").Formula = "=IF(INDIRECT(""'BYE WEEKS'!B3"")<>"""",INDIRECT(""'BYE WEEKS'!B3""),"""")"
Worksheets("1").Range("K3").Formula = "=IF(INDIRECT(""'BYE WEEKS'!B4"")<>"""",INDIRECT(""'BYE WEEKS'!B4""),"""")"
Worksheets("1").Range("K4").Formula = "=IF(INDIRECT(""'BYE WEEKS'!B5"")<>"""",INDIRECT(""'BYE WEEKS'!B5""),"""")"
Worksheets("1").Range("K5").Formula = "=IF(INDIRECT(""'BYE WEEKS'!B6"")<>"""",INDIRECT(""'BYE WEEKS'!B6""),"""")"
Worksheets("1").Range("K6").Formula = "=IF(INDIRECT(""'BYE WEEKS'!B7"")<>"""",INDIRECT(""'BYE WEEKS'!B7""),"""")"
Worksheets("1").Range("K7").Formula = "=IF(INDIRECT(""'BYE WEEKS'!B8"")<>"""",INDIRECT(""'BYE WEEKS'!B8""),"""")"
'WEEK 2
Worksheets("2").Range("K2").Formula = "=IF(INDIRECT(""'BYE WEEKS'!E3"")<>"""",INDIRECT(""'BYE WEEKS'!E3""),"""")"
Worksheets("2").Range("K3").Formula = "=IF(INDIRECT(""'BYE WEEKS'!E4"")<>"""",INDIRECT(""'BYE WEEKS'!E4""),"""")"
Worksheets("2").Range("K4").Formula = "=IF(INDIRECT(""'BYE WEEKS'!E5"")<>"""",INDIRECT(""'BYE WEEKS'!E5""),"""")"
Worksheets("2").Range("K5").Formula = "=IF(INDIRECT(""'BYE WEEKS'!E6"")<>"""",INDIRECT(""'BYE WEEKS'!E6""),"""")"
Worksheets("2").Range("K6").Formula = "=IF(INDIRECT(""'BYE WEEKS'!E7"")<>"""",INDIRECT(""'BYE WEEKS'!E7""),"""")"
Worksheets("2").Range("K7").Formula = "=IF(INDIRECT(""'BYE WEEKS'!E8"")<>"""",INDIRECT(""'BYE WEEKS'!E8""),"""")"
'WEEK 3
Worksheets("3").Range("K2").Formula = "=IF(INDIRECT(""'BYE WEEKS'!H3"")<>"""",INDIRECT(""'BYE WEEKS'!H3""),"""")"
Worksheets("3").Range("K3").Formula = "=IF(INDIRECT(""'BYE WEEKS'!H4"")<>"""",INDIRECT(""'BYE WEEKS'!H4""),"""")"
Worksheets("3").Range("K4").Formula = "=IF(INDIRECT(""'BYE WEEKS'!H5"")<>"""",INDIRECT(""'BYE WEEKS'!H5""),"""")"
Worksheets("3").Range("K5").Formula = "=IF(INDIRECT(""'BYE WEEKS'!H6"")<>"""",INDIRECT(""'BYE WEEKS'!H6""),"""")"
Worksheets("3").Range("K6").Formula = "=IF(INDIRECT(""'BYE WEEKS'!H7"")<>"""",INDIRECT(""'BYE WEEKS'!H7""),"""")"
Worksheets("3").Range("K7").Formula = "=IF(INDIRECT(""'BYE WEEKS'!H8"")<>"""",INDIRECT(""'BYE WEEKS'!H8""),"""")"
'WEEK 4
Worksheets("4").Range("K2").Formula = "=IF(INDIRECT(""'BYE WEEKS'!K3"")<>"""",INDIRECT(""'BYE WEEKS'!K3""),"""")"
Worksheets("4").Range("K3").Formula = "=IF(INDIRECT(""'BYE WEEKS'!K4"")<>"""",INDIRECT(""'BYE WEEKS'!K4""),"""")"
Worksheets("4").Range("K4").Formula = "=IF(INDIRECT(""'BYE WEEKS'!K5"")<>"""",INDIRECT(""'BYE WEEKS'!K5""),"""")"
Worksheets("4").Range("K5").Formula = "=IF(INDIRECT(""'BYE WEEKS'!K6"")<>"""",INDIRECT(""'BYE WEEKS'!K6""),"""")"
Worksheets("4").Range("K6").Formula = "=IF(INDIRECT(""'BYE WEEKS'!K7"")<>"""",INDIRECT(""'BYE WEEKS'!K7""),"""")"
Worksheets("4").Range("K7").Formula = "=IF(INDIRECT(""'BYE WEEKS'!K8"")<>"""",INDIRECT(""'BYE WEEKS'!K8""),"""")"
'WEEK 5
Worksheets("5").Range("K2").Formula = "=IF(INDIRECT(""'BYE WEEKS'!N3"")<>"""",INDIRECT(""'BYE WEEKS'!N3""),"""")"
Worksheets("5").Range("K3").Formula = "=IF(INDIRECT(""'BYE WEEKS'!N4"")<>"""",INDIRECT(""'BYE WEEKS'!N4""),"""")"
Worksheets("5").Range("K4").Formula = "=IF(INDIRECT(""'BYE WEEKS'!N5"")<>"""",INDIRECT(""'BYE WEEKS'!N5""),"""")"
Worksheets("5").Range("K5").Formula = "=IF(INDIRECT(""'BYE WEEKS'!N6"")<>"""",INDIRECT(""'BYE WEEKS'!N6""),"""")"
Worksheets("5").Range("K6").Formula = "=IF(INDIRECT(""'BYE WEEKS'!N7"")<>"""",INDIRECT(""'BYE WEEKS'!N7""),"""")"
Worksheets("5").Range("K7").Formula = "=IF(INDIRECT(""'BYE WEEKS'!N8"")<>"""",INDIRECT(""'BYE WEEKS'!N8""),"""")"
'WEEK 6
Worksheets("6").Range("K2").Formula = "=IF(INDIRECT(""'BYE WEEKS'!Q3"")<>"""",INDIRECT(""'BYE WEEKS'!Q3""),"""")"
Worksheets("6").Range("K3").Formula = "=IF(INDIRECT(""'BYE WEEKS'!Q4"")<>"""",INDIRECT(""'BYE WEEKS'!Q4""),"""")"
Worksheets("6").Range("K4").Formula = "=IF(INDIRECT(""'BYE WEEKS'!Q5"")<>"""",INDIRECT(""'BYE WEEKS'!Q5""),"""")"
Worksheets("6").Range("K5").Formula = "=IF(INDIRECT(""'BYE WEEKS'!Q6"")<>"""",INDIRECT(""'BYE WEEKS'!Q6""),"""")"
Worksheets("6").Range("K6").Formula = "=IF(INDIRECT(""'BYE WEEKS'!Q7"")<>"""",INDIRECT(""'BYE WEEKS'!Q7""),"""")"
Worksheets("6").Range("K7").Formula = "=IF(INDIRECT(""'BYE WEEKS'!Q8"")<>"""",INDIRECT(""'BYE WEEKS'!Q8""),"""")"
'WEEK 7
Worksheets("7").Range("K2").Formula = "=IF(INDIRECT(""'BYE WEEKS'!B11"")<>"""",INDIRECT(""'BYE WEEKS'!B11""),"""")"
Worksheets("7").Range("K3").Formula = "=IF(INDIRECT(""'BYE WEEKS'!B12"")<>"""",INDIRECT(""'BYE WEEKS'!B12""),"""")"
Worksheets("7").Range("K4").Formula = "=IF(INDIRECT(""'BYE WEEKS'!B13"")<>"""",INDIRECT(""'BYE WEEKS'!B13""),"""")"
Worksheets("7").Range("K5").Formula = "=IF(INDIRECT(""'BYE WEEKS'!B14"")<>"""",INDIRECT(""'BYE WEEKS'!B14""),"""")"
Worksheets("7").Range("K6").Formula = "=IF(INDIRECT(""'BYE WEEKS'!B15"")<>"""",INDIRECT(""'BYE WEEKS'!B15""),"""")"
Worksheets("7").Range("K7").Formula = "=IF(INDIRECT(""'BYE WEEKS'!B16"")<>"""",INDIRECT(""'BYE WEEKS'!B16""),"""")"
'WEEK 8
Worksheets("8").Range("K2").Formula = "=IF(INDIRECT(""'BYE WEEKS'!E11"")<>"""",INDIRECT(""'BYE WEEKS'!E11""),"""")"
Worksheets("8").Range("K3").Formula = "=IF(INDIRECT(""'BYE WEEKS'!E12"")<>"""",INDIRECT(""'BYE WEEKS'!E12""),"""")"
Worksheets("8").Range("K4").Formula = "=IF(INDIRECT(""'BYE WEEKS'!E13"")<>"""",INDIRECT(""'BYE WEEKS'!E13""),"""")"
Worksheets("8").Range("K5").Formula = "=IF(INDIRECT(""'BYE WEEKS'!E14"")<>"""",INDIRECT(""'BYE WEEKS'!E14""),"""")"
Worksheets("8").Range("K6").Formula = "=IF(INDIRECT(""'BYE WEEKS'!E15"")<>"""",INDIRECT(""'BYE WEEKS'!E15""),"""")"
Worksheets("8").Range("K7").Formula = "=IF(INDIRECT(""'BYE WEEKS'!E16"")<>"""",INDIRECT(""'BYE WEEKS'!E16""),"""")"
'WEEK 9
Worksheets("9").Range("K2").Formula = "=IF(INDIRECT(""'BYE WEEKS'!H11"")<>"""",INDIRECT(""'BYE WEEKS'!H11""),"""")"
Worksheets("9").Range("K3").Formula = "=IF(INDIRECT(""'BYE WEEKS'!H12"")<>"""",INDIRECT(""'BYE WEEKS'!H12""),"""")"
Worksheets("9").Range("K4").Formula = "=IF(INDIRECT(""'BYE WEEKS'!H13"")<>"""",INDIRECT(""'BYE WEEKS'!H13""),"""")"
Worksheets("9").Range("K5").Formula = "=IF(INDIRECT(""'BYE WEEKS'!H14"")<>"""",INDIRECT(""'BYE WEEKS'!H14""),"""")"
Worksheets("9").Range("K6").Formula = "=IF(INDIRECT(""'BYE WEEKS'!H15"")<>"""",INDIRECT(""'BYE WEEKS'!H15""),"""")"
Worksheets("9").Range("K7").Formula = "=IF(INDIRECT(""'BYE WEEKS'!H16"")<>"""",INDIRECT(""'BYE WEEKS'!H16""),"""")"
'WEEK 10
Worksheets("10").Range("K2").Formula = "=IF(INDIRECT(""'BYE WEEKS'!K11"")<>"""",INDIRECT(""'BYE WEEKS'!K11""),"""")"
Worksheets("10").Range("K3").Formula = "=IF(INDIRECT(""'BYE WEEKS'!K12"")<>"""",INDIRECT(""'BYE WEEKS'!K12""),"""")"
Worksheets("10").Range("K4").Formula = "=IF(INDIRECT(""'BYE WEEKS'!K13"")<>"""",INDIRECT(""'BYE WEEKS'!K13""),"""")"
Worksheets("10").Range("K5").Formula = "=IF(INDIRECT(""'BYE WEEKS'!K14"")<>"""",INDIRECT(""'BYE WEEKS'!K14""),"""")"
Worksheets("10").Range("K6").Formula = "=IF(INDIRECT(""'BYE WEEKS'!K15"")<>"""",INDIRECT(""'BYE WEEKS'!K15""),"""")"
Worksheets("10").Range("K7").Formula = "=IF(INDIRECT(""'BYE WEEKS'!K16"")<>"""",INDIRECT(""'BYE WEEKS'!K16""),"""")"
'WEEK 11
Worksheets("11").Range("K2").Formula = "=IF(INDIRECT(""'BYE WEEKS'!N11"")<>"""",INDIRECT(""'BYE WEEKS'!N11""),"""")"
Worksheets("11").Range("K3").Formula = "=IF(INDIRECT(""'BYE WEEKS'!N12"")<>"""",INDIRECT(""'BYE WEEKS'!N12""),"""")"
Worksheets("11").Range("K4").Formula = "=IF(INDIRECT(""'BYE WEEKS'!N13"")<>"""",INDIRECT(""'BYE WEEKS'!N13""),"""")"
Worksheets("11").Range("K5").Formula = "=IF(INDIRECT(""'BYE WEEKS'!N14"")<>"""",INDIRECT(""'BYE WEEKS'!N14""),"""")"
Worksheets("11").Range("K6").Formula = "=IF(INDIRECT(""'BYE WEEKS'!N15"")<>"""",INDIRECT(""'BYE WEEKS'!N15""),"""")"
Worksheets("11").Range("K7").Formula = "=IF(INDIRECT(""'BYE WEEKS'!N16"")<>"""",INDIRECT(""'BYE WEEKS'!N16""),"""")"
'WEEK 12
Worksheets("12").Range("K2").Formula = "=IF(INDIRECT(""'BYE WEEKS'!Q11"")<>"""",INDIRECT(""'BYE WEEKS'!Q11""),"""")"
Worksheets("12").Range("K3").Formula = "=IF(INDIRECT(""'BYE WEEKS'!Q12"")<>"""",INDIRECT(""'BYE WEEKS'!Q12""),"""")"
Worksheets("12").Range("K4").Formula = "=IF(INDIRECT(""'BYE WEEKS'!Q13"")<>"""",INDIRECT(""'BYE WEEKS'!Q13""),"""")"
Worksheets("12").Range("K5").Formula = "=IF(INDIRECT(""'BYE WEEKS'!Q14"")<>"""",INDIRECT(""'BYE WEEKS'!Q14""),"""")"
Worksheets("12").Range("K6").Formula = "=IF(INDIRECT(""'BYE WEEKS'!Q15"")<>"""",INDIRECT(""'BYE WEEKS'!Q15""),"""")"
Worksheets("12").Range("K7").Formula = "=IF(INDIRECT(""'BYE WEEKS'!Q16"")<>"""",INDIRECT(""'BYE WEEKS'!Q16""),"""")"
'WEEK 13
Worksheets("13").Range("K2").Formula = "=IF(INDIRECT(""'BYE WEEKS'!B19"")<>"""",INDIRECT(""'BYE WEEKS'!B19""),"""")"
Worksheets("13").Range("K3").Formula = "=IF(INDIRECT(""'BYE WEEKS'!B20"")<>"""",INDIRECT(""'BYE WEEKS'!B20""),"""")"
Worksheets("13").Range("K4").Formula = "=IF(INDIRECT(""'BYE WEEKS'!B21"")<>"""",INDIRECT(""'BYE WEEKS'!B21""),"""")"
Worksheets("13").Range("K5").Formula = "=IF(INDIRECT(""'BYE WEEKS'!B22"")<>"""",INDIRECT(""'BYE WEEKS'!B22""),"""")"
Worksheets("13").Range("K6").Formula = "=IF(INDIRECT(""'BYE WEEKS'!B23"")<>"""",INDIRECT(""'BYE WEEKS'!B23""),"""")"
Worksheets("13").Range("K7").Formula = "=IF(INDIRECT(""'BYE WEEKS'!B24"")<>"""",INDIRECT(""'BYE WEEKS'!B24""),"""")"
'WEEK 14
Worksheets("14").Range("K2").Formula = "=IF(INDIRECT(""'BYE WEEKS'!E19"")<>"""",INDIRECT(""'BYE WEEKS'!E19""),"""")"
Worksheets("14").Range("K3").Formula = "=IF(INDIRECT(""'BYE WEEKS'!E20"")<>"""",INDIRECT(""'BYE WEEKS'!E20""),"""")"
Worksheets("14").Range("K4").Formula = "=IF(INDIRECT(""'BYE WEEKS'!E21"")<>"""",INDIRECT(""'BYE WEEKS'!E21""),"""")"
Worksheets("14").Range("K5").Formula = "=IF(INDIRECT(""'BYE WEEKS'!E22"")<>"""",INDIRECT(""'BYE WEEKS'!E22""),"""")"
Worksheets("14").Range("K6").Formula = "=IF(INDIRECT(""'BYE WEEKS'!E23"")<>"""",INDIRECT(""'BYE WEEKS'!E23""),"""")"
Worksheets("14").Range("K7").Formula = "=IF(INDIRECT(""'BYE WEEKS'!E24"")<>"""",INDIRECT(""'BYE WEEKS'!E24""),"""")"
'WEEK 15
Worksheets("15").Range("K2").Formula = "=IF(INDIRECT(""'BYE WEEKS'!H19"")<>"""",INDIRECT(""'BYE WEEKS'!H19""),"""")"
Worksheets("15").Range("K3").Formula = "=IF(INDIRECT(""'BYE WEEKS'!H20"")<>"""",INDIRECT(""'BYE WEEKS'!H20""),"""")"
Worksheets("15").Range("K4").Formula = "=IF(INDIRECT(""'BYE WEEKS'!H21"")<>"""",INDIRECT(""'BYE WEEKS'!H21""),"""")"
Worksheets("15").Range("K5").Formula = "=IF(INDIRECT(""'BYE WEEKS'!H22"")<>"""",INDIRECT(""'BYE WEEKS'!H22""),"""")"
Worksheets("15").Range("K6").Formula = "=IF(INDIRECT(""'BYE WEEKS'!H23"")<>"""",INDIRECT(""'BYE WEEKS'!H23""),"""")"
Worksheets("15").Range("K7").Formula = "=IF(INDIRECT(""'BYE WEEKS'!H24"")<>"""",INDIRECT(""'BYE WEEKS'!H24""),"""")"
'WEEK 16
Worksheets("16").Range("K2").Formula = "=IF(INDIRECT(""'BYE WEEKS'!K19"")<>"""",INDIRECT(""'BYE WEEKS'!K19""),"""")"
Worksheets("16").Range("K3").Formula = "=IF(INDIRECT(""'BYE WEEKS'!K20"")<>"""",INDIRECT(""'BYE WEEKS'!K20""),"""")"
Worksheets("16").Range("K4").Formula = "=IF(INDIRECT(""'BYE WEEKS'!K21"")<>"""",INDIRECT(""'BYE WEEKS'!K21""),"""")"
Worksheets("16").Range("K5").Formula = "=IF(INDIRECT(""'BYE WEEKS'!K22"")<>"""",INDIRECT(""'BYE WEEKS'!K22""),"""")"
Worksheets("16").Range("K6").Formula = "=IF(INDIRECT(""'BYE WEEKS'!K23"")<>"""",INDIRECT(""'BYE WEEKS'!K23""),"""")"
Worksheets("16").Range("K7").Formula = "=IF(INDIRECT(""'BYE WEEKS'!K24"")<>"""",INDIRECT(""'BYE WEEKS'!K24""),"""")"
'WEEK 17
Worksheets("17").Range("K2").Formula = "=IF(INDIRECT(""'BYE WEEKS'!N19"")<>"""",INDIRECT(""'BYE WEEKS'!N19""),"""")"
Worksheets("17").Range("K3").Formula = "=IF(INDIRECT(""'BYE WEEKS'!N20"")<>"""",INDIRECT(""'BYE WEEKS'!N20""),"""")"
Worksheets("17").Range("K4").Formula = "=IF(INDIRECT(""'BYE WEEKS'!N21"")<>"""",INDIRECT(""'BYE WEEKS'!N21""),"""")"
Worksheets("17").Range("K5").Formula = "=IF(INDIRECT(""'BYE WEEKS'!N22"")<>"""",INDIRECT(""'BYE WEEKS'!N22""),"""")"
Worksheets("17").Range("K6").Formula = "=IF(INDIRECT(""'BYE WEEKS'!N23"")<>"""",INDIRECT(""'BYE WEEKS'!N23""),"""")"
Worksheets("17").Range("K7").Formula = "=IF(INDIRECT(""'BYE WEEKS'!N24"")<>"""",INDIRECT(""'BYE WEEKS'!N24""),"""")"
'WEEK 18
Worksheets("18").Range("K2").Formula = "=IF(INDIRECT(""'BYE WEEKS'!Q19"")<>"""",INDIRECT(""'BYE WEEKS'!Q19""),"""")"
Worksheets("18").Range("K3").Formula = "=IF(INDIRECT(""'BYE WEEKS'!Q20"")<>"""",INDIRECT(""'BYE WEEKS'!Q20""),"""")"
Worksheets("18").Range("K4").Formula = "=IF(INDIRECT(""'BYE WEEKS'!Q21"")<>"""",INDIRECT(""'BYE WEEKS'!Q21""),"""")"
Worksheets("18").Range("K5").Formula = "=IF(INDIRECT(""'BYE WEEKS'!Q22"")<>"""",INDIRECT(""'BYE WEEKS'!Q22""),"""")"
Worksheets("18").Range("K6").Formula = "=IF(INDIRECT(""'BYE WEEKS'!Q23"")<>"""",INDIRECT(""'BYE WEEKS'!Q23""),"""")"
Worksheets("18").Range("K7").Formula = "=IF(INDIRECT(""'BYE WEEKS'!Q24"")<>"""",INDIRECT(""'BYE WEEKS'!Q24""),"""")"
Thanks You
SF49ers164480
Sep 22 2022 12:23 PM
Private Sub CommandButton1_Click()
Dim w As Long
Dim sh As Worksheet
Dim r As Long
Dim c As String
For w = 1 To 18
Set sh = Worksheets(CStr(w))
For r = 2 To 7
c = Chr(66 + 3 * ((w - 1) Mod 6))
sh.Range("K" & r).Formula = "=IF(INDIRECT(""'BYE WEEKS'!B" & r & _
""")<>"""",INDIRECT(""'BYE WEEKS'!B" & r & """),"""")"
Next r
Next w
End Sub
Sep 23 2022 12:02 PM
What is red? Please try to be specific.
Sep 27 2022 02:53 PM
You must have made a mistake while copying and pasting the code...