I need help with VBA

Contributor

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

5 Replies

@sf49ers164480 

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
the code is red you give me

@sf49ers164480 

What is red? Please try to be specific.

the whole code on my end i copy your code

@sf49ers164480 

You must have made a mistake while copying and pasting the code...