Option Explicit
Option Base 1
Sub NHAPDL()
Dim i As Long
Dim t
i = Application.WorksheetFunction.CountIf(Sheets("TH").Range("A5:A65536"), Sheets("Nhap xuat").Range("M1"))
If i <> 0 Then
t = MsgBox(" NGÀY NÀY ĐA CÓ DU LIÊU - BAN MUÔN SUA KHÔNG?" & Chr(10) & "NHÂN YES ĐÊ TIÊP TUC NHÂP" & Chr(10) & "NHÂN NO ĐÊ BO QUA", vbYesNo, "THÔNG BÁO")
If t = vbYes Then
Call KTRA ' kiêm tra tinh hop ly c?a du lieu nh?p vào
If Sheets("Nhap xuat").[A1] = "X" Then
MsgBox " Du lieu Nhap xuât chua đung-Hay kiêm tra lai": Exit Sub
Else
Call XoaDLcu
Call Nhap
Call XEP_A_Z
End If
End If
Else
t = MsgBox(" NGÀY NÀY CHUA CÓ DU LIÊU - BAN MUÔN NHAP KHÔNG?" & Chr(10) & "NHÂN YES ĐÊ TIÊP TUC NHÂP" & Chr(10) & "NHÂN NO ĐÊ BO QUA", vbYesNo, "THÔNG BÁO")
If t = vbYes Then
Call KTRA
If Sheet5.[A1] = "X" Then
MsgBox "CANH BAO: DU LIEU CHUA DUOC NHAP DO CHUA DUNG - HAY KIÊM TRA VÀ SUA LAI !?!", vbCritical
Exit Sub
Else
Call Nhap
Call XEP_A_Z
End If
End If
End If
MsgBox "ĐA XONG"
End Sub
Sub XoaDLcu()
Dim D1 As Long
Dim D2 As Long
With Application.WorksheetFunction
D1 = .Match(Sheets("Nhap xuat").Range("M1"), Sheets("TH").Range("A:A"), 0)
D2 = .Match(Sheets("Nhap xuat").Range("M1"), Sheets("TH").Range("A:A"), 1)
End With
Sheets("TH").Activate
Sheets("TH").Range(Cells(D1, 1), Cells(D2, 256)).ClearContents
Sheets("Nhap xuat").Activate
End Sub
Sub Nhap()
Dim Arr(), KQ(), Ngay As Date
Dim i&, k&, m&, cot&, j&, t&, S&, N&, d&
Dim Ws As Worksheet, Sh As Worksheet
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = False
Set Sh = Sheets("Nhap xuat")
Ngay = Sh.Range("M1")
k = Sh.Range("A62").End(xlUp).Row
Arr = Sh.Range("B6:AL" & k).Value
Set Ws = Sheets("TH")
cot = Ws.Range("IV1").End(xlToLeft).Column
ReDim KQ(1 To UBound(Arr), 1 To cot + 20)
For i = 1 To UBound(Arr)
t = t + 1: KQ(t, 1) = CDate(Ngay)
For j = 1 To 20 ' cot
KQ(t, j + 1) = Arr(i, j)
Next j
For S = 24 To 29
KQ(t, S - 1) = Arr(i, S)
Next S
KQ(t, 21) = Arr(i, 34)
KQ(t, 22) = Arr(i, 35)
Next i
If t Then
d = Ws.Range("A" & Rows.Count).End(xlUp).Row + 1
Ws.Range("A" & d).Resize(100, 150).ClearContents
Ws.Range("A" & d).Resize(t, 28) = KQ
End If
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
End Sub
Sub XEP_A_Z()
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
On Error GoTo Thoat
Sheets("TH").Activate
Range("A5:IV65536").Select
ActiveWorkbook.Worksheets("TH").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("TH").Sort.SortFields.Add Key:=Range("A5"), SortOn _
:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortTextAsNumbers
With ActiveWorkbook.Worksheets("TH").Sort
.SetRange Range("A5:IV65536")
.Apply
End With
Thoat:
Sheets("Nhap xuat").Activate
Application.DisplayAlerts = True
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub
