Snasui Excel Forum

Code: Select all

Private Sub CommandButton1_Click()
Dim Rng As Range
Dim rCheck As Range
Dim Lrow7 As Integer
   

    Lrow7 = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row

    With Sheets("NetTrade")
        Set rCheck = .Range("I1", .Range("I" & Rows.Count).End(xlUp))
    End With
        
    With Sheets("Holiday")
        Set Rng = .Range("A1", .Range("B" & Rows.Count).End(xlUp)) 'Range B  คือ Column สุดท้ายที่ดึง
    End With
        
    ColName = 2
    
    Lrow7 = Lrow7 + 1
    Range("A" & Lrow7) = Date

    If Format(Range("A" & Lrow7), "dddd") = "Monday" Then
        Range("H" & Lrow7) = Range("A" & Lrow7) - 3
        Range("I" & Lrow7) = Range("H" & Lrow7) + 5

    ElseIf Format(Range("A" & Lrow7), "dddd") = "Friday" Then
        Range("H" & Lrow7) = Range("A" & Lrow7) - 1
        Range("I" & Lrow7) = Range("H" & Lrow7) + 5

    ElseIf Format(Range("A" & Lrow7), "dddd") = "Thursday" Then
        Range("H" & Lrow7) = Range("A" & Lrow7) - 1
        Range("I" & Lrow7) = Range("H" & Lrow7) + 5

    Else
        Range("H" & Lrow7) = Range("A" & Lrow7) - 1
        Range("I" & Lrow7) = Range("H" & Lrow7) + 3

    End If
    MsgBox (Range("I" & Lrow7))
    If Application.VLookup(Range("I" & Lrow7), Rng, 2, 0) = True Then 'ตรวจสอบว่ามีในฐานข้อมูลวันหยุดพิเศษหรือเปล่า
        If Format(Range("A" & Lrow7), "dddd") = "Monday" Then
            Range("H" & Lrow7) = Range("A" & Lrow7) - 3
            Range("I" & Lrow7) = Range("H" & Lrow7) + 6
        ElseIf Format(Range("A" & Lrow7), "dddd") = "Friday" Then
            Range("H" & Lrow7) = Range("A" & Lrow7) - 1
            Range("I" & Lrow7) = Range("H" & Lrow7) + 6
        ElseIf Format(Range("A" & Lrow7), "dddd") = "Thursday" Then
            Range("H" & Lrow7) = Range("A" & Lrow7) - 1
            Range("I" & Lrow7) = Range("H" & Lrow7) + 6
        Else
            Range("H" & Lrow7) = Range("A" & Lrow7) - 1
            Range("I" & Lrow7) = Range("H" & Lrow7) + 4
        End If
    Else
        MsgBox ("ไม่พบ")
    End If
End Sub