Snasui Excel Forum

Code: Select all

Sub Createtable()

    'ปรับขนาดตาราง
    Cells.Select
    Selection.RowHeight = 20
    With Selection.Font
        .Name = "Tahoma"
        .Size = 11
    End With
    Rows("1:1").RowHeight = 57.5
    Rows("2:2").RowHeight = 19.5
    Rows("3:4").RowHeight = 20.25
    Rows("5:5").RowHeight = 40.5
    Columns("A:A").ColumnWidth = 3.5
    Columns("B:B").ColumnWidth = 36
    Range("C:C,F:F,K:K").ColumnWidth = 5.5
    Range("D:D,G:G,I:I,L:L,N:N").ColumnWidth = 9
    Range("E:E,H:H,J:J,M:M,O:O").ColumnWidth = 14
    
    'หัวตาราง
    Range("A3:A5,B3:B5,C3:J3,C4:E4,F4:H4,I4:J4,K3:O3,K4:M4,N4:O4").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
    End With
    Selection.Merge
    Range("C5:O5").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
    End With
    Range("A3:O5").Select
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
    End With
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
    End With

    'เส้นตาราง
    Range("A6:O6").Select
    Range(Selection, Selection.End(xlDown)).Select
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
    End With
    Selection.Borders(xlEdgeBottom).LineStyle = xlNone
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
    End With
    



    'ตัวอักษร
    Range("A3") = "NO."
    Range("B3") = "มาตรการ"
    Range("C3") = "ขั้นตอนก่อนการปรับปรุง"
    Range("C4") = "ตรวจวัดก่อนปรับปรุง"
    Range("C5") = "จำนวน"
    Range("D5") = "กำลังไฟฟ้า" & Chr(13) & "" & Chr(10) & "(kW)"
    Range("E5") = "พลังงานไฟฟ้า" & Chr(13) & "" & Chr(10) & "(kWh)"
    Range("F4") = "ค่าจากการประเมิน"
    Range("F5") = "จำนวน"
    Range("G5") = "กำลังไฟฟ้า" & Chr(13) & "" & Chr(10) & "(kW)"
    Range("H5") = "พลังงานไฟฟ้า" & Chr(13) & "" & Chr(10) & "(kWh)"
    Range("I4") = "ผลประหยัดคาดการณ์"
    Range("I5") = "กำลังไฟฟ้า" & Chr(13) & "" & Chr(10) & "(kW)"
    Range("J5") = "พลังงานไฟฟ้า" & Chr(13) & "" & Chr(10) & "(kWh)"
    Range("K3") = "ขั้นตอนหลังการปรับปรุง (M&V)"
    Range("K4") = "ตรวจวัดหลังปรับปรุง"
    Range("K5") = "จำนวน"
    Range("L5") = "กำลังไฟฟ้า" & Chr(13) & "" & Chr(10) & "(kW)"
    Range("M5") = "พลังงานไฟฟ้า" & Chr(13) & "" & Chr(10) & "(kWh)"
    Range("N4") = "ผลประหยัดจากการตรวจวัด"
    Range("N5") = "กำลังไฟฟ้า" & Chr(13) & "" & Chr(10) & "(kW)"
    Range("O5") = "พลังงานไฟฟ้า" & Chr(13) & "" & Chr(10) & "(kWh)"
    Rows("3:3").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("O3") = "=""อัตราค่าไฟฟ้าเฉลี่ย(บาท)  ""&TEXT(Log!O16,""#,##0.00"")"

    Range("O3").Select
    With Selection
        .HorizontalAlignment = xlRight
        .VerticalAlignment = xlCenter
    End With
    With Selection.Font
        .Name = "Tahoma"
        .Size = 10
    End With
    
    Rows("1:1").RowHeight = 10
    Rows("2:2").RowHeight = 40
    Range("B3") = "*Document = ดูเอกสารเพิ่มเติม"
    Range("B3").HorizontalAlignment = xlLeft
End Sub