Snasui Excel Forum

Code: Select all

Public Sub NetSale()
    Dim dDay1#, dDay2#, lRow&
    
    dDay1 = CDbl(DateSerial(2016, 2, 3))
    dDay2 = CDbl(DateSerial(2016, 2, 10)) + 0.99991
    
    'collect source data
    With ThisWorkbook
        .Sheets(3).Activate
        .Sheets(3).Cells.Clear
        .Sheets(3).Range("a1:b1") = .Sheets(2).Range("a1")
        .Sheets(3).Range("c1:d1") = .Sheets(2).Range("d1")
        .Sheets(3).Range("e1:f1") = .Sheets(2).Range("e1")
        With .Sheets(3)
            .Range("a2") = ">=" & dDay1
            .Range("b2") = "<=" & dDay2
            .Range("c2") = "SL"
            .Range("d2") = "CN"
            .Range("e2") = "<>0"
            .Range("f2") = "<>3"
        End With
        
        .Sheets(2).UsedRange.AdvancedFilter Action:=xlFilterCopy, _
            CriteriaRange:=.Sheets(3).Range("a1:f2"), _
            CopyToRange:=.Sheets(3).Range("a3"), Unique:=False
        .Sheets(3).Rows("1:2").Delete shift:=xlUp
        lRow = .Sheets(3).Range("a" & Rows.Count).End(xlUp).Row
        
        'exit if not data found
        If lRow = 1 Then
            MsgBox "ไม่มีข้อมูลในช่วงวันที่ " & Format(dDay1, "d mmm yy") & _
                " ถึง " & Format(dDay2, "d mmm yy"), vbExclamation, "Not Found"
            GoTo suddenEnd
        End If
    End With
    
suddenEnd:
End Sub

Code: Select all

Public Sub NetSale()
    Dim dDay1#, dDay2#, lRow&
    
    dDay1 = CDbl(DateSerial(2016, 2, 3))
    dDay2 = CDbl(DateSerial(2016, 2, 10)) + 0.99991
    
    'collect source data
    With ThisWorkbook
        .Sheets(3).Activate
        .Sheets(3).Cells.Clear
        .Sheets(3).Range("a1:b1") = .Sheets(2).Range("a1")
        .Sheets(3).Range("c1") = .Sheets(2).Range("d1")
        .Sheets(3).Range("d1:e1") = .Sheets(2).Range("e1")
        With .Sheets(3)
            .Range("a2:a3") = ">=" & dDay1
            .Range("b2:b3") = "<=" & dDay2
            .Range("c2") = "SL"
            .Range("c3") = "CN"
            .Range("d2:d3") = "<>0"
            .Range("e2:e3") = "<>3"
        End With
        
        .Sheets(2).UsedRange.AdvancedFilter Action:=xlFilterCopy, _
            CriteriaRange:=.Sheets(3).Range("a1:e3"), _
            CopyToRange:=.Sheets(3).Range("a4"), Unique:=False
        .Sheets(3).Rows("1:3").Delete shift:=xlUp
        lRow = .Sheets(3).Range("a" & Rows.Count).End(xlUp).Row
        
        'exit if not data found
        If lRow = 1 Then
            MsgBox "ไม่มีข้อมูลในช่วงวันที่ " & Format(dDay1, "d mmm yy") & _
                " ถึง " & Format(dDay2, "d mmm yy"), vbExclamation, "Not Found"
            GoTo suddenEnd
        End If
    End With
    
suddenEnd:
End Sub