Sub loc_biendong()
Dim arr(), kq(), dk As Boolean, i As Long, a As Long, lr As Long
Dim sh_nhatkydc As Worksheet, sh_loc_Nky As Worksheet
Dim tungay As Date, denngay As Date
Dim bophansudung As String, ii&
Dim nghiepvu As String
Dim dkloc As Integer, dkngay As Long, dkbophan As Integer, dknghiepvu As Integer
Set sh_nhatkydc = Sheet31
Set sh_loc_Nky = Sheet28
tungay = sh_nhatkydc.Range("C4").Value
denngay = sh_nhatkydc.Range("C5").Value
bophansudung = sh_nhatkydc.Range("D5").Value
nghiepvu = sh_nhatkydc.Range("E5").Value
If tungay > 0 And denngay > 0 Then
dkngay = 1
End If
If bophansudung <> "" Then
dkbophan = 2
End If
If nghiepvu <> "" Then
dknghiepvu = 4
End If
dkloc = dkngay + dkbophan + dknghiepvu
With sh_nhatkydc
lr = .Range("A" & Rows.Count).End(xlUp).Row 'Tim dong cuoi
arr = .Range("A11:S" & lr).Value
ReDim kq(1 To UBound(arr, 1), 1 To 19)
For i = 1 To UBound(arr, 1)
If dkloc = 1 Then
'Loc theo ngay
dk = arr(i, 1) >= tungay And arr(i, 1) <= denngay
ElseIf dkloc = 2 Then
'Loc theo bo phan
dk = arr(i, 7) = bophansudung
ElseIf dkloc = 3 Then
'Loc theo ngay va Bo phan su dung
dk = arr(i, 1) >= tungay And arr(i, 1) <= denngay And arr(i, 7) = bophansudung
ElseIf dkloc = 7 Then
'Loc theo ngay, Bo phan su dung va nghiep vu
dk = arr(i, 1) >= tungay And arr(i, 1) <= denngay And arr(i, 7) = bophansudung And arr(i, 19) = nghiepvu
ElseIf dkloc = 4 Then
'Loc theo nghiep vu
dk = arr(i, 19) = nghiepvu
ElseIf dkloc = 5 Then
'Loc theo ngay va nghiep vu
dk = arr(i, 1) >= tungay And arr(i, 1) <= denngay And arr(i, 19) = nghiepvu
Else
MsgBox "Khong co Record nao het!!!", vbInformation
Exit Sub
End If
If dk = True Then
a = a + 1
kq(a, 1) = arr(i, 1)
kq(a, 2) = a
For ii = 3 To 19
kq(a, ii) = arr(i, ii)
Next
End If
Next i
End With
With sh_loc_Nky
.Range("A12:T50000").ClearContents
If a > 0 Then
.Range("D12").Resize(a).NumberFormat = "@"
.Range("A12").Resize(a, 19).Value = kq
Else
MsgBox "Khong thay record nao de loc", vbInformation
End If
End With
End Sub
