Snasui Excel Forum

Code: Select all

Public Sub definePSA()
    Dim LastRow As Long
    With Sheets("Trend_PSA")
        LastRow = .Range("A" & Rows.Count).End(xlUp).Row
        'UCL PSA
            .Range("AP2:AP" & LastRow).Value = "=IF(RC[-1]<>"""",15,"""")"
        'LCL
            .Range("AQ2:AQ" & LastRow).Value = "=IF(RC[-2]<>"""",12,"""")"
        'USL
            .Range("AR2:AR" & LastRow).Value = "=IF(RC[-3]<>"""",0,"""")"
        'LSL
        .Range("AS2:AS" & LastRow).Value = "=IF(RC[-4]<>"""",-2,"""")"
        
        .Range("AP1").Value = "UCL"
        .Range("AQ1").Value = "LCL"
        .Range("AR1").Value = "USL"
        .Range("AS1").Value = "LSL"
    End With

    Range("AP1:AS1").Select
    Selection.Font.Bold = True
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
    End With
    Range("A2").Select

    ActiveWorkbook.Names.Add Name:="mean0", RefersToR1C1:= _
        "=OFFSET(Trend_PSA!R1C3,1,0,COUNTA(Trend_PSA!C3)-1,1)"
    ActiveWorkbook.Names.Add Name:="mean1", RefersToR1C1:= _
        "=OFFSET(Trend_PSA!R1C7,1,0,COUNTA(Trend_PSA!C7)-1,1)"
    ActiveWorkbook.Names.Add Name:="mean2", RefersToR1C1:= _
        "=OFFSET(Trend_PSA!R1C11,1,0,COUNTA(Trend_PSA!C11)-1,1)"
    ActiveWorkbook.Names.Add Name:="mean3", RefersToR1C1:= _
        "=OFFSET(Trend_PSA!R1C15,1,0,COUNTA(Trend_PSA!C15)-1,1)"
    ActiveWorkbook.Names.Add Name:="mean4", RefersToR1C1:= _
        "=OFFSET(Trend_PSA!R1C19,1,0,COUNTA(Trend_PSA!C19)-1,1)"
    ActiveWorkbook.Names.Add Name:="mean5", RefersToR1C1:= _
        "=OFFSET(Trend_PSA!R1C23,1,0,COUNTA(Trend_PSA!C23)-1,1)"
    ActiveWorkbook.Names.Add Name:="mean6", RefersToR1C1:= _
        "=OFFSET(Trend_PSA!R1C27,1,0,COUNTA(Trend_PSA!C27)-1,1)"
    ActiveWorkbook.Names.Add Name:="mean7", RefersToR1C1:= _
        "=OFFSET(Trend_PSA!R1C31,1,0,COUNTA(Trend_PSA!C31)-1,1)"
    ActiveWorkbook.Names.Add Name:="mean8", RefersToR1C1:= _
        "=OFFSET(Trend_PSA!R1C35,1,0,COUNTA(Trend_PSA!C35)-1,1)"
    ActiveWorkbook.Names.Add Name:="mean9", RefersToR1C1:= _
        "=OFFSET(Trend_PSA!R1C39,1,0,COUNTA(Trend_PSA!C39)-1,1)"

    ActiveWorkbook.Names.Add Name:="st0", RefersToR1C1:= _
        "=OFFSET(Trend_PSA!R1C4,1,0,COUNTA(Trend_PSA!C4)-1,1)"
    ActiveWorkbook.Names.Add Name:="st1", RefersToR1C1:= _
        "=OFFSET(Trend_PSA!R1C8,1,0,COUNTA(Trend_PSA!C8)-1,1)"
    ActiveWorkbook.Names.Add Name:="st2", RefersToR1C1:= _
        "=OFFSET(Trend_PSA!R1C12,1,0,COUNTA(Trend_PSA!C12)-1,1)"
    ActiveWorkbook.Names.Add Name:="st3", RefersToR1C1:= _
        "=OFFSET(Trend_PSA!R1C16,1,0,COUNTA(Trend_PSA!C16)-1,1)"
    ActiveWorkbook.Names.Add Name:="st4", RefersToR1C1:= _
        "=OFFSET(Trend_PSA!R1C20,1,0,COUNTA(Trend_PSA!C20)-1,1)"
    ActiveWorkbook.Names.Add Name:="st5", RefersToR1C1:= _
        "=OFFSET(Trend_PSA!R1C24,1,0,COUNTA(Trend_PSA!C24)-1,1)"
    ActiveWorkbook.Names.Add Name:="st6", RefersToR1C1:= _
        "=OFFSET(Trend_PSA!R1C28,1,0,COUNTA(Trend_PSA!C28)-1,1)"
    ActiveWorkbook.Names.Add Name:="st7", RefersToR1C1:= _
        "=OFFSET(Trend_PSA!R1C32,1,0,COUNTA(Trend_PSA!C32)-1,1)"
    ActiveWorkbook.Names.Add Name:="st8", RefersToR1C1:= _
        "=OFFSET(Trend_PSA!R1C36,1,0,COUNTA(Trend_PSA!C36)-1,1)"
    ActiveWorkbook.Names.Add Name:="st9", RefersToR1C1:= _
        "=OFFSET(Trend_PSA!R1C40,1,0,COUNTA(Trend_PSA!C40)-1,1)"
        
    ActiveWorkbook.Names.Add Name:="cpk0", RefersToR1C1:= _
        "=OFFSET(Trend_PSA!R1C5,1,0,COUNTA(Trend_PSA!C5)-1,1)"
    ActiveWorkbook.Names.Add Name:="cpk1", RefersToR1C1:= _
        "=OFFSET(Trend_PSA!R1C9,1,0,COUNTA(Trend_PSA!C9)-1,1)"
    ActiveWorkbook.Names.Add Name:="cpk2", RefersToR1C1:= _
        "=OFFSET(Trend_PSA!R1C13,1,0,COUNTA(Trend_PSA!C13)-1,1)"
    ActiveWorkbook.Names.Add Name:="cpk3", RefersToR1C1:= _
        "=OFFSET(Trend_PSA!R1C17,1,0,COUNTA(Trend_PSA!C17)-1,1)"
    ActiveWorkbook.Names.Add Name:="cpk4", RefersToR1C1:= _
        "=OFFSET(Trend_PSA!R1C21,1,0,COUNTA(Trend_PSA!C21)-1,1)"
    ActiveWorkbook.Names.Add Name:="cpk5", RefersToR1C1:= _
        "=OFFSET(Trend_PSA!R1C25,1,0,COUNTA(Trend_PSA!C25)-1,1)"
    ActiveWorkbook.Names.Add Name:="cpk6", RefersToR1C1:= _
        "=OFFSET(Trend_PSA!R1C29,1,0,COUNTA(Trend_PSA!C29)-1,1)"
    ActiveWorkbook.Names.Add Name:="cpk7", RefersToR1C1:= _
        "=OFFSET(Trend_PSA!R1C33,1,0,COUNTA(Trend_PSA!C33)-1,1)"
    ActiveWorkbook.Names.Add Name:="cpk8", RefersToR1C1:= _
        "=OFFSET(Trend_PSA!R1C37,1,0,COUNTA(Trend_PSA!C37)-1,1)"
    ActiveWorkbook.Names.Add Name:="cpk9", RefersToR1C1:= _
        "=OFFSET(Trend_PSA!R1C41,1,0,COUNTA(Trend_PSA!C41)-1,1)"
        
    ActiveWorkbook.Names.Add Name:="week", RefersToR1C1:= _
        "=OFFSET(Trend_PSA!R1C1,1,0,COUNTA(Trend_PSA!C1),1)"
        
    ActiveWorkbook.Names.Add Name:="UCL", RefersToR1C1:= _
        "=OFFSET(Trend_PSA!R1C42,1,0,COUNTA(Trend_PSA!C1),1)"
    ActiveWorkbook.Names.Add Name:="LCL", RefersToR1C1:= _
        "=OFFSET(Trend_PSA!R1C43,1,0,COUNTA(Trend_PSA!C1),1)"
    ActiveWorkbook.Names.Add Name:="USL", RefersToR1C1:= _
        "=OFFSET(Trend_PSA!R1C44,1,0,COUNTA(Trend_PSA!C1),1)"
    ActiveWorkbook.Names.Add Name:="LSL", RefersToR1C1:= _
        "=OFFSET(Trend_PSA!R1C45,1,0,COUNTA(Trend_PSA!C1),1)"
End Sub