Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, [c7]) Is Nothing Then
Dim Sh As Worksheet, Rng As Range, sRng As Range
Dim jJ As Byte, MyAdd As String, ShName As String
Dim Rws As Long, Ngay As Date
Set Sh = Sheets("NXT")
Rws = [A11].CurrentRegion.Offset(1, 1).Rows.Count
[b11].Resize(Rws, 4).ClearContents
Set Rng = Sh.Range(Sh.[b9], Sh.[b65500].End(xlUp))
Set sRng = Rng.Find(Target.Value, , xlFormulas, xlWhole)
If Not sRng Is Nothing Then
With [b65500].End(xlUp).Offset(1)
.Value = DateSerial(Year(Date), Month(Date), 1) - 1
.Offset(, 1).Value = sRng.Offset(, 3).Value
.Offset(, 5) = 1
End With
End If
[g10].Value = "GPE"
For jJ = 1 To 2
ShName = Choose(jJ, "Nhap", "Xuat")
Set Sh = ThisWorkbook.Worksheets(ShName)
Set Rng = Sh.Range(Sh.[D7], Sh.[D65500].End(xlUp))
Set sRng = Rng.Find(Target.Value)
If Not sRng Is Nothing Then
MyAdd = sRng.Address
Do
With [b65500].End(xlUp)
If sRng.Offset(, -2).Value <> "" Then
Ngay = sRng.Offset(, -2).Value
Else
Ngay = sRng.Offset(, -2).End(xlUp).Value
End If
If Ngay = .Value Then
.Offset(, 1 + jJ).Value = .Offset(, 1 + jJ).Value + sRng.Offset(, 3).Value
Else
.Offset(1).Value = Ngay
.Offset(1, 1 + jJ).Value = sRng.Offset(, 3).Value
.Offset(1, 5).Value = 31 * Month(Ngay) + Day(Ngay)
End If
End With
Set sRng = Rng.FindNext(sRng)
Loop While Not sRng Is Nothing And sRng.Address <> MyAdd
End If
Next jJ
Rws = [b10].CurrentRegion.Rows.Count
[b10].Resize(Rws, 6).Sort Key1:=Range("G11"), Order1:=xlAscending, _
Key2:=Range("D11"), Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1
[g10].Resize(Rws).Clear
End If
End Sub
