Sub TheoDoiTangGiam()
Dim ws As Worksheet
'Khai bao Sheet muon chay code
Set ws = ThisWorkbook.Sheets("nguon")
Dim clNgay, clNoiDung, clTang, clGiam, clRemain, clDetail As String
Dim beginRow, lastRow As Long
'Khai bao cot theo y muon
clNgay = "A" ' Cot A Ngay hach toan
clNoiDung = "B" ' Cot B Dien Giai
clTang = "F" ' Cot F Phat sinh tang
clGiam = "G" ' Cot G Phat sinh giam
clRemain = "O" ' Cot I So tien con lai
clDetail = "P" ' Cot J Chi tiet so tien thanh toan
'Khai bao dong bat dau
beginRow = 7
'Xac dinh dong cuoi theo cot A
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
Dim dict As Object
Set dict = CreateObject("Scripting.Dictionary")
Dim i, j As Long
Dim remaining As Double
Dim note As String
' Initialize the dictionary with the transactions
For i = beginRow To lastRow '7 o day la ngay bat dau phat sinh giao dich tai cot A
'F la cot phat sinh tang
If ws.Cells(i, clTang).Value > 0 Then
dict.Add i, ws.Cells(i, clTang).Value
End If
Next i
' Process the transactions
For i = beginRow To lastRow
'G la cot phat sinh giam
If ws.Cells(i, clGiam).Value > 0 Then
remaining = ws.Cells(i, clGiam).Value
note = ""
ws.Cells(beginRow - 1, clDetail).Value = "Ngay hoa don, Dien Giai hoa don, So Tien Thanh Toan"
For Each Key In dict.Keys
If remaining <= 0 Then Exit For
If dict(Key) > 0 Then
If dict(Key) >= remaining Then
'note = note & ws.Cells(Key, clNgay).Value & " - " & ws.Cells(Key, clNoiDung).Value & ", "
note = note & ws.Cells(Key, clNgay).Value & " , " & ws.Cells(Key, clNoiDung).Value & " , " & remaining & ", "
dict(Key) = dict(Key) - remaining
'note = note & ws.Cells(Key, clNgay).Value & " , " & ws.Cells(Key, clNoiDung).Value & " , " & dict(Key) & ", " 'So tien con lai
remaining = 0
Else
'note = note & ws.Cells(Key, clNgay).Value & " - " & ws.Cells(Key, clNoiDung).Value & ", "
note = note & ws.Cells(Key, clNgay).Value & " , " & ws.Cells(Key, clNoiDung).Value & " ," & dict(Key) & ", "
remaining = remaining - dict(Key)
'note = note & ws.Cells(Key, clNgay).Value & " , " & ws.Cells(Key, clNoiDung).Value & " , " & "0" & ", " 'So tien con lai
dict(Key) = 0
End If
End If
Next Key
ws.Cells(i, clDetail).Value = Left(note, Len(note) - 2) 'bo di dau "," cuoi cung'
End If
Next i
' Update the remaining amounts
'Cot I xac dinh so tien chua thanh toan tuong ung voi so du no cuoi ky cua bang du lieu
ws.Cells(beginRow - 1, clRemain).Value = "So tien chua thanh toan"
For Each Key In dict.Keys
If dict(Key) > 0 Then
'ws.Cells(Key, clDetail).Value = "chua duoc thanh toan"
ws.Cells(Key, clRemain).Value = dict(Key)
Else
'ws.Cells(Key, clGiam).Value = "done"
ws.Cells(Key, clRemain).Value = 0
End If
Next Key
' Split the values in column F into separate columns
'Cot J tach ra so tien thanh toan cua nhung hoa don nao
Dim clNumber As Integer
clNumber = Range(clDetail & "1").Column
For i = beginRow - 1 To lastRow
If ws.Cells(i, clDetail).Value <> "" Then
Dim values() As String
values = Split(ws.Cells(i, clDetail).Value, ",")
For j = LBound(values) To UBound(values)
ws.Cells(i, clNumber + j).Value = values(j) 'clNumber o day la so thu tu cot clDetail'
Next j
End If
Next i
End Sub
