Snasui Excel Forum

Code: Select all

''    Sheet3.Select
'    If Sheet3.Cells(6, 1).Value = 2 Or Sheet3.Cells(6, 1).Value = "" Then
'        insertrow = 5
'        If numrowcal > 0 And Sheet3.Cells(6, 1).Value <> "" Then
'            For j = 1 To numrowcal
'                Sheet3.Cells(5 + j, 1).EntireRow.Insert
'            Next j
'        End If
'    Else
'    For i = 1 To numrowin
'        If (Sheet3.Cells(5 + i, 1).Value = Sheet8.Cells(3, 1).Value - 1 And Sheet3.Cells(6 + i, 1).Value = Sheet8.Cells(3, 1).Value + 1) _
'        Or (Sheet3.Cells(5 + i, 1).Value = Sheet8.Cells(3, 1).Value - 1 And Sheet3.Cells(6 + i, 1).Value = "") Then
''            Sheet3.Rows(6 + i, 5 + i + numrowcal).Insert
''                If Sheet3.Cells(6, 1).Value = "" Then
''                    insertrow = 5
''                    For j = 1 To numrowcal
''                        Sheet3.Cells(5 + j, 1).EntireRow.Insert
''                    Next j
''                Else
'                insertrow = 5 + i
'                For j = 1 To numrowcal
'                    Sheet3.Cells(5 + i + j, 1).EntireRow.Insert
'                Next j
''                End If
'            Exit For
'        End If
'    Next i
'    End If

Code: Select all

Sub macro_insert(psaverow)
    Dim numrowin, numrowcal, insertrow, numman, numop, i, j As Integer
    Dim shin, shcal, shlist As Worksheet
    Set shin = ThisWorkbook.Worksheets("information")
    Set shcal = ThisWorkbook.Worksheets("cal")
    Set shlist = ThisWorkbook.Worksheets("list_other")
    numrowin = 0
    numrowcal = 0
    numman = 0
    insertrow = 5
    
'    Sheets("information").Select
    
    numop = UserForm5.TextBox35.Value
    numrowin = shin.Range("A6:A" & shin.Cells(Rows.count, "A").End(xlUp).Row).Rows.count
    numrowcal = shcal.Range("A3:A" & shcal.Cells(Rows.count, "A").End(xlUp).Row).Rows.count
    numman = shlist.Range("A2:A" & shlist.Cells(Rows.count, "A").End(xlUp).Row).Rows.count
    If Sheet8.Cells(3, 1) = "" Then
        numrowcal = 0
    End If
    '********************************************** ลบ row ครั้งนี้ (ของเดิม) ทั้งหมดออก ***********************************
    For i = numrowin To 1 Step -1
        If Sheet3.Cells(5 + i, 1) = UserForm5.TextBox35.Text Then
            Sheet3.Cells(5 + i, 1).EntireRow.Delete
        End If
    Next i
    '**********************************************************************************************************
    '**************************************** insert row ของครั้งนี้ (ของใหม่) เท่าจำนวนใหม่ **********************************
''    Sheet3.Select
'    If Sheet3.Cells(6, 1).Value = 2 Or Sheet3.Cells(6, 1).Value = "" Then
'        insertrow = 5
'        If numrowcal > 0 And Sheet3.Cells(6, 1).Value <> "" Then
'            For j = 1 To numrowcal
'                Sheet3.Cells(5 + j, 1).EntireRow.Insert
'            Next j
'        End If
'    Else
'    For i = 1 To numrowin
'        If (Sheet3.Cells(5 + i, 1).Value = Sheet8.Cells(3, 1).Value - 1 And Sheet3.Cells(6 + i, 1).Value = Sheet8.Cells(3, 1).Value + 1) _
'        Or (Sheet3.Cells(5 + i, 1).Value = Sheet8.Cells(3, 1).Value - 1 And Sheet3.Cells(6 + i, 1).Value = "") Then
''            Sheet3.Rows(6 + i, 5 + i + numrowcal).Insert
''                If Sheet3.Cells(6, 1).Value = "" Then
''                    insertrow = 5
''                    For j = 1 To numrowcal
''                        Sheet3.Cells(5 + j, 1).EntireRow.Insert
''                    Next j
''                Else
'                insertrow = 5 + i
'                For j = 1 To numrowcal
'                    Sheet3.Cells(5 + i + j, 1).EntireRow.Insert
'                Next j
''                End If
'            Exit For
'        End If
'    Next i
'    End If
    
'' ใช้แทน  insert row  ไปพรางๆก่อน ยังทำไม่ได้
     Sheets("cal").Select
     Rows("3:3").Select
     If Range("A4").Value <> "" Then
         Range(Selection, Selection.End(xlDown)).Select
     End If
     Application.CutCopyMode = False
     Selection.Copy
     Sheets("information").Select
     Rows("6:6").Select
     Selection.Insert Shift:=xlDown
    
    
    '**********************************************************************************************************
    '************************************* กรอกค่า (แทนใช้สูตร) หลังคอลัมน์ 25 เป็นต้นไป ************************************
    For i = 1 To numrowcal
        shcal.Range(shcal.Cells(2 + i, 1), shcal.Cells(2 + i, 24)).Copy shin.Range(shin.Cells(insertrow + i, 1), shin.Cells(insertrow + i, 24))
        If shin.Cells(insertrow + i, 21).Value > 0 Then
            shin.Cells(insertrow + i, 25).Value = shin.Cells(insertrow + i, 21).Value * shin.Cells(insertrow + i, 22).Value                              '25 = ค่าน้ำมันไม่มีคำสั่งเดินทาง
        Else
            shin.Cells(insertrow + i, 25).Value = ""
        End If
        For j = 1 To numman
            If shin.Cells(insertrow + i, 2).Value = shlist.Cells(1 + j, 2).Value Then
                shin.Cells(insertrow + i, 26).Value = shlist.Cells(1 + j, 6).Value                                                                                             '26 = ระดับ
            End If
        Next j
        For j = 1 To 17
            If shin.Cells(insertrow + i, 26).Value = shlist.Cells(1 + j, 8).Value And shlist.Cells(1 + j, 8).Value <> "" Then
                shin.Cells(insertrow + i, 27).Value = shlist.Cells(1 + j, 18).Value                                                                                           '27 = จัดระดับกลุ่ม
                If shin.Cells(insertrow + i, 13).Value <> "" Then
                    shin.Cells(insertrow + i, 29).Value = shin.Cells(insertrow + i, 13).Value / shlist.Cells(1 + j, 9).Value                                    '29 = จำนวนวันค่าเบี้ยเลี้ยง
                Else
                    shin.Cells(insertrow + i, 29).Value = ""
                End If
                shin.Cells(insertrow + i, 30).Value = Int(shin.Cells(insertrow + i, 14) / shlist.Cells(1 + j, 12))                                                 '30 = จำนวนวันค่าเบี้ยเลี้ยง
                shin.Cells(insertrow + i, 31).Value = Int(shin.Cells(insertrow + i, 19) / 225)                                                                          '31 = จำนวนวันค่าเสี่ยงภัย
                shin.Cells(insertrow + i, 32).Value = shlist.Cells(1 + j, 13).Value * shin.Cells(insertrow + i, 6).Value                                       '32 = ค่าแรง
                shin.Cells(insertrow + i, 33).Value = shlist.Cells(1 + j, 15).Value * shin.Cells(insertrow + i, 23).Value                                     '33 = ค่า OT 1 เท่า
                shin.Cells(insertrow + i, 34).Value = shlist.Cells(1 + j, 14).Value * shin.Cells(insertrow + i, 24).Value                                      '34 = ค่า OT 1.5 เท่า
                If shin.Cells(insertrow + i, 26).Value = shlist.Cells(2, 8).Value Then shin.Cells(insertrow + i, 35).Value = shin.Cells(insertrow + i, 6).Value * shlist.Cells(2, 16).Value _
                    : shin.Cells(insertrow + i, 36).Value = shin.Cells(insertrow + i, 6).Value * shlist.Cells(2, 17).Value: shin.Cells(insertrow + i, 37).Value = shin.Cells(insertrow + i, 6).Value '35,36,37 (เกี่ยวกับรถ)
            End If
        Next j
        If shin.Cells(insertrow + i, 9).Value = shlist.Cells(2, 24).Value Then
            shin.Cells(insertrow + i, 28).Value = shlist.Cells(2, 24).Value                                                                                                    '28 = สถานที่ทำงาน
        Else
            shin.Cells(insertrow + i, 28).Value = shlist.Cells(1, 25).Value
        End If
        shin.Cells(insertrow + i, 38).Value = Sheet1.Cells(psaverow, 7).Value                        '38 = ระดับขั้นตอนการทำงาน
        shin.Cells(insertrow + i, 39).Value = shin.Cells(insertrow + i, 32).Value * Sheet5.Cells(2, 77).Value     '0.4152                                                                                '39 = สวัสดิการ
    Next i
End Sub

    '**********************************************************************************************************
    '*************************************เรียงลำดับ ************************************
    
    ' รียงลำดับใช้แทน  insert row  ไปพรางๆก่อน ยังทำไม่ได้
    Sheets("information").Select
     Range("Table1[ครั้งที่]").Select
    ActiveWorkbook.Worksheets("information").ListObjects("Table1").Sort.SortFields. _
        Clear
    ActiveWorkbook.Worksheets("information").ListObjects("Table1").Sort.SortFields. _
        add Key:=Range("A6"), SortOn:=xlSortOnValues, Order:=xlAscending, _
        DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("information").ListObjects("Table1").Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

End Sub

Code: Select all

Sub macro_insert(psaverow)
    Dim numrowin, numrowcal, insertrow, numman, numop, i, j As Integer
    Dim shin, shcal, shlist As Worksheet
    Set shin = ThisWorkbook.Worksheets("information")
    Set shcal = ThisWorkbook.Worksheets("cal")
    Set shlist = ThisWorkbook.Worksheets("list_other")
    numrowin = 0
    numrowcal = 0
    numman = 0
    insertrow = 5
    
'    Sheets("information").Select
    
    numop = UserForm5.TextBox35.Value
    numrowin = shin.Range("A6:A" & shin.Cells(Rows.count, "A").End(xlUp).Row).Rows.count
    numrowcal = shcal.Range("A3:A" & shcal.Cells(Rows.count, "A").End(xlUp).Row).Rows.count
    numman = shlist.Range("A2:A" & shlist.Cells(Rows.count, "A").End(xlUp).Row).Rows.count
    If Sheet8.Cells(3, 1) = "" Then
        numrowcal = 0
    End If
    '********************************************** ลบ row ครั้งนี้ (ของเดิม) ทั้งหมดออก ***********************************
    For i = numrowin To 1 Step -1
        If Sheet3.Cells(5 + i, 1) = UserForm5.TextBox35.Text Then
            Sheet3.Cells(5 + i, 1).EntireRow.Delete
        End If
    Next i
    '**********************************************************************************************************
    '**************************************** insert row ของครั้งนี้ (ของใหม่) เท่าจำนวนใหม่ **********************************
'    Sheet3.Select
    If Sheet3.Cells(6, 1).Value = 2 Or Sheet3.Cells(6, 1).Value = "" Then
        insertrow = 5
        If numrowcal > 0 And Sheet3.Cells(6, 1).Value <> "" Then
            For j = 1 To numrowcal
                Sheet3.Cells(5 + j, 1).EntireRow.Insert
            Next j
        End If
    Else
    For i = 1 To numrowin
        If (Sheet3.Cells(5 + i, 1).Value = Sheet8.Cells(3, 1).Value - 1 And Sheet3.Cells(6 + i, 1).Value = Sheet8.Cells(3, 1).Value + 1) _
        Or (Sheet3.Cells(5 + i, 1).Value = Sheet8.Cells(3, 1).Value - 1 And Sheet3.Cells(6 + i, 1).Value = "") Then
'            Sheet3.Rows(6 + i, 5 + i + numrowcal).Insert
'                If Sheet3.Cells(6, 1).Value = "" Then
'                    insertrow = 5
'                    For j = 1 To numrowcal
'                        Sheet3.Cells(5 + j, 1).EntireRow.Insert
'                    Next j
'                Else
                insertrow = 5 + i
                For j = 1 To numrowcal
                    Sheet3.Cells(5 + i + j, 1).EntireRow.Insert
                Next j
'                End If
            Exit For
        End If
    Next i
    End If
    
    '**********************************************************************************************************
    '************************************* กรอกค่า (แทนใช้สูตร) หลังคอลัมน์ 25 เป็นต้นไป ************************************
    For i = 1 To numrowcal
        shcal.Range(shcal.Cells(2 + i, 1), shcal.Cells(2 + i, 24)).Copy shin.Range(shin.Cells(insertrow + i, 1), shin.Cells(insertrow + i, 24))
        If shin.Cells(insertrow + i, 21).Value > 0 Then
            shin.Cells(insertrow + i, 25).Value = shin.Cells(insertrow + i, 21).Value * shin.Cells(insertrow + i, 22).Value                              '25 = ค่าน้ำมันไม่มีคำสั่งเดินทาง
        Else
            shin.Cells(insertrow + i, 25).Value = ""
        End If
        For j = 1 To numman
            If shin.Cells(insertrow + i, 2).Value = shlist.Cells(1 + j, 2).Value Then
                shin.Cells(insertrow + i, 26).Value = shlist.Cells(1 + j, 6).Value                                                                                             '26 = ระดับ
            End If
        Next j
        For j = 1 To 17
            If shin.Cells(insertrow + i, 26).Value = shlist.Cells(1 + j, 8).Value And shlist.Cells(1 + j, 8).Value <> "" Then
                shin.Cells(insertrow + i, 27).Value = shlist.Cells(1 + j, 18).Value                                                                                           '27 = จัดระดับกลุ่ม
                If shin.Cells(insertrow + i, 13).Value <> "" Then
                    shin.Cells(insertrow + i, 29).Value = shin.Cells(insertrow + i, 13).Value / shlist.Cells(1 + j, 9).Value                                    '29 = จำนวนวันค่าเบี้ยเลี้ยง
                Else
                    shin.Cells(insertrow + i, 29).Value = ""
                End If
                shin.Cells(insertrow + i, 30).Value = Int(shin.Cells(insertrow + i, 14) / shlist.Cells(1 + j, 12))                                                 '30 = จำนวนวันค่าเบี้ยเลี้ยง
                shin.Cells(insertrow + i, 31).Value = Int(shin.Cells(insertrow + i, 19) / 225)                                                                          '31 = จำนวนวันค่าเสี่ยงภัย
                shin.Cells(insertrow + i, 32).Value = shlist.Cells(1 + j, 13).Value * shin.Cells(insertrow + i, 6).Value                                       '32 = ค่าแรง
                shin.Cells(insertrow + i, 33).Value = shlist.Cells(1 + j, 15).Value * shin.Cells(insertrow + i, 23).Value                                     '33 = ค่า OT 1 เท่า
                shin.Cells(insertrow + i, 34).Value = shlist.Cells(1 + j, 14).Value * shin.Cells(insertrow + i, 24).Value                                      '34 = ค่า OT 1.5 เท่า
                If shin.Cells(insertrow + i, 26).Value = shlist.Cells(2, 8).Value Then shin.Cells(insertrow + i, 35).Value = shin.Cells(insertrow + i, 6).Value * shlist.Cells(2, 16).Value _
                    : shin.Cells(insertrow + i, 36).Value = shin.Cells(insertrow + i, 6).Value * shlist.Cells(2, 17).Value: shin.Cells(insertrow + i, 37).Value = shin.Cells(insertrow + i, 6).Value '35,36,37 (เกี่ยวกับรถ)
            End If
        Next j
        If shin.Cells(insertrow + i, 9).Value = shlist.Cells(2, 24).Value Then
            shin.Cells(insertrow + i, 28).Value = shlist.Cells(2, 24).Value                                                                                                    '28 = สถานที่ทำงาน
        Else
            shin.Cells(insertrow + i, 28).Value = shlist.Cells(1, 25).Value
        End If
        shin.Cells(insertrow + i, 38).Value = Sheet1.Cells(psaverow, 7).Value                        '38 = ระดับขั้นตอนการทำงาน
        shin.Cells(insertrow + i, 39).Value = shin.Cells(insertrow + i, 32).Value * Sheet5.Cells(2, 77).Value     '0.4152                                                                                '39 = สวัสดิการ
    Next i
End Sub