Private Sub XoaDuLieu()
Dim EndRow As Long
EndRow = PhatSinh.[A:L].Find(What:="*", LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
PhatSinh.Range("A5:L" & EndRow).ClearContents
End Sub
Private Sub TinhGiaXK()
Dim EndRow As Long
EndRow = PhatSinh.[A:K].Find(What:="*", LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
PhatSinh.Range("A5:A65536").ClearContents
PhatSinh.Range("L5:L65536").ClearContents
If EndRow = 4 Then Exit Sub
With PhatSinh.Range("A5:A" & EndRow)
.FormulaR1C1 = "=MONTH(RC[1])"
.Value = .Value
End With
Select Case PhatSinh.[G2].Value
Case "BQGQ tháng"
TinhGiaXK_BQGQThang
Case "BQGQ sau m" & ChrW(7895) & "i l" & ChrW(7847) & "n nh" & ChrW(7853) & "p"
TinhGiaXK_BQGQNhap
Case "FIFO"
TinhGiaXK_FIFO
Case "LIFO"
TinhGiaXK_LIFO
End Select
End Sub
Private Sub TinhGiaXK_BQGQThang()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim ArrPhatSinh, ArrTest, ArrTonKho(), ArrViTri(), Dic, EndRow As Long, i As Long, j As Long, k As Long, l As Long, TaiKhoan As String, SoLuong As Double, SoLuongTon As Double, SoTien As Double, SoTienTon As Double, STT As Long, Gia As String
TaiKhoan = PhatSinh.[E2].Value
Set Dic = CreateObject("Scripting.Dictionary")
ArrPhatSinh = DauKy.Range("A3:E" & DauKy.[A65536].End(xlUp).Row + 1).Value
ReDim ArrTonKho(1 To 10000, 1 To 48)
For i = 1 To UBound(ArrPhatSinh, 1) - 1
If ArrPhatSinh(i, 1) Like TaiKhoan & "*" Then
j = j + 1
Dic.Add ArrPhatSinh(i, 2) & vbBack & ArrPhatSinh(i, 3), j
ArrTonKho(j, 1) = ArrPhatSinh(i, 5)
ArrTonKho(j, 2) = ArrPhatSinh(i, 4)
End If
Next
EndRow = PhatSinh.[A:K].Find(What:="*", LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1
ArrPhatSinh = PhatSinh.Range("A5:K" & EndRow).Value
ArrTest = PhatSinh.Range("L5:L" & EndRow).Value
ReDim ArrViTri(1 To UBound(ArrPhatSinh), 1 To 3)
For i = 1 To UBound(ArrPhatSinh, 1)
If ArrPhatSinh(i, 4) Like TaiKhoan & "*" And ArrPhatSinh(i, 7) <> "" Then
If Not Dic.Exists(ArrPhatSinh(i, 6) & vbBack & ArrPhatSinh(i, 7)) Then
j = j + 1
Dic.Add ArrPhatSinh(i, 6) & vbBack & ArrPhatSinh(i, 7), j
End If
STT = CLng(Dic.Item(ArrPhatSinh(i, 6) & vbBack & ArrPhatSinh(i, 7)))
ArrTonKho(STT, ArrPhatSinh(i, 1) * 4 - 3) = ArrTonKho(STT, ArrPhatSinh(i, 1) * 4 - 3) + ArrPhatSinh(i, 11)
ArrTonKho(STT, ArrPhatSinh(i, 1) * 4 - 2) = ArrTonKho(STT, ArrPhatSinh(i, 1) * 4 - 2) + ArrPhatSinh(i, 10)
End If
If ArrPhatSinh(i, 5) Like TaiKhoan & "*" And ArrPhatSinh(i, 9) <> "" Then
If Not Dic.Exists(ArrPhatSinh(i, 8) & vbBack & ArrPhatSinh(i, 9)) Then
j = j + 1
Dic.Add ArrPhatSinh(i, 8) & vbBack & ArrPhatSinh(i, 9), j
End If
STT = CLng(Dic.Item(ArrPhatSinh(i, 8) & vbBack & ArrPhatSinh(i, 9)))
ArrTonKho(STT, ArrPhatSinh(i, 1) * 4 - 1) = ArrTonKho(STT, ArrPhatSinh(i, 1) * 4 - 1) + ArrPhatSinh(i, 10)
k = k + 1
ArrViTri(k, 1) = ArrPhatSinh(i, 8) & vbBack & ArrPhatSinh(i, 9)
ArrViTri(k, 2) = ArrPhatSinh(i, 1)
ArrViTri(k, 3) = i
End If
Next
For i = 1 To j
For l = 1 To 12
If l > 1 Then
ArrTonKho(i, l * 4 - 3) = ArrTonKho(i, l * 4 - 3) + (ArrTonKho(i, l * 4 - 6) - ArrTonKho(i, l * 4 - 5)) * ArrTonKho(i, l * 4 - 4)
ArrTonKho(i, l * 4 - 2) = ArrTonKho(i, l * 4 - 2) + ArrTonKho(i, l * 4 - 6) - ArrTonKho(i, l * 4 - 5)
End If
If ArrTonKho(i, l * 4 - 2) = 0 Then
ArrTonKho(i, l * 4) = 0
Else
ArrTonKho(i, l * 4) = ArrTonKho(i, l * 4 - 3) / ArrTonKho(i, l * 4 - 2)
End If
Next
Next
For i = 1 To k
STT = CLng(Dic.Item(ArrViTri(i, 1)))
ArrTest(ArrViTri(i, 3), 1) = Round(ArrTonKho(STT, ArrViTri(i, 2) * 4) * ArrPhatSinh(ArrViTri(i, 3), 10), 0)
Next
PhatSinh.Range("L5:L" & EndRow).Value = ArrTest
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub
Private Sub TinhGiaXK_BQGQNhap()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim ArrPhatSinh, ArrTest, ArrTonKho(), Dic, EndRow As Long, i As Long, j As Long, TaiKhoan As String, SoLuong As Double, SoLuongTon As Double, SoTien As Double, SoTienTon As Double, STT As Long, Gia As String
TaiKhoan = PhatSinh.[E2].Value
Set Dic = CreateObject("Scripting.Dictionary")
ArrPhatSinh = DauKy.Range("A3:E" & DauKy.[A65536].End(xlUp).Row + 1).Value
ReDim ArrTonKho(1 To 10000, 1 To 2)
For i = 1 To UBound(ArrPhatSinh, 1) - 1
If ArrPhatSinh(i, 1) Like TaiKhoan & "*" Then
j = j + 1
Dic.Add ArrPhatSinh(i, 2) & vbBack & ArrPhatSinh(i, 3), j
ArrTonKho(j, 1) = ArrPhatSinh(i, 5)
ArrTonKho(j, 2) = ArrPhatSinh(i, 4)
End If
Next
EndRow = PhatSinh.[A:K].Find(What:="*", LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1
ArrPhatSinh = PhatSinh.Range("A5:K" & EndRow).Value
ArrTest = PhatSinh.Range("L5:L" & EndRow).Value
For i = 1 To UBound(ArrPhatSinh, 1)
If ArrPhatSinh(i, 4) Like TaiKhoan & "*" And ArrPhatSinh(i, 7) <> "" Then
If Not Dic.Exists(ArrPhatSinh(i, 6) & vbBack & ArrPhatSinh(i, 7)) Then
j = j + 1
Dic.Add ArrPhatSinh(i, 6) & vbBack & ArrPhatSinh(i, 7), j
End If
STT = CLng(Dic.Item(ArrPhatSinh(i, 6) & vbBack & ArrPhatSinh(i, 7)))
ArrTonKho(STT, 1) = ArrTonKho(STT, 1) + ArrPhatSinh(i, 11)
ArrTonKho(STT, 2) = ArrTonKho(STT, 2) + ArrPhatSinh(i, 10)
End If
If ArrPhatSinh(i, 5) Like TaiKhoan & "*" And ArrPhatSinh(i, 9) <> "" Then
If Not Dic.Exists(ArrPhatSinh(i, 8) & vbBack & ArrPhatSinh(i, 9)) Then
ArrTest(i, 1) = 0
Else
STT = CLng(Dic.Item(ArrPhatSinh(i, 8) & vbBack & ArrPhatSinh(i, 9)))
If ArrTonKho(STT, 2) >= ArrPhatSinh(i, 10) Then
ArrTest(i, 1) = Round(ArrPhatSinh(i, 10) * ArrTonKho(STT, 1) / ArrTonKho(STT, 2), 0)
ArrTonKho(STT, 1) = ArrTonKho(STT, 1) - ArrTest(i, 1)
ArrTonKho(STT, 2) = ArrTonKho(STT, 2) - ArrPhatSinh(i, 10)
ElseIf ArrTonKho(STT, 2) > 0 Then
ArrTest(i, 1) = ArrTonKho(STT, 1)
ArrTonKho(STT, 1) = 0
ArrTonKho(STT, 2) = 0
ElseIf ArrTonKho(STT, 2) = 0 Then
ArrTest(i, 1) = 0
End If
End If
End If
Next
PhatSinh.Range("L5:L" & EndRow).Value = ArrTest
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub
Private Sub TinhGiaXK_FIFO()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim ArrPhatSinh, ArrTest, Dic, EndRow As Long, i As Long, TaiKhoan As String, SoLuong As Double, SoLuongTon As Double, SoTien As Double, SoTienTon As Double, Item As String, Gia As String
TaiKhoan = PhatSinh.[E2].Value
Set Dic = CreateObject("Scripting.Dictionary")
ArrPhatSinh = DauKy.Range("A3:E" & DauKy.[A65536].End(xlUp).Row + 1).Value
For i = 1 To UBound(ArrPhatSinh, 1) - 1
If ArrPhatSinh(i, 1) Like TaiKhoan & "*" Then
Dic.Add ArrPhatSinh(i, 2) & vbBack & ArrPhatSinh(i, 3), ArrPhatSinh(i, 5) & "/" & ArrPhatSinh(i, 4) & vbBack
End If
Next
EndRow = PhatSinh.[A:K].Find(What:="*", LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1
ArrPhatSinh = PhatSinh.Range("A5:K" & EndRow).Value
ArrTest = PhatSinh.Range("L5:L" & EndRow).Value
For i = 1 To UBound(ArrPhatSinh, 1)
If ArrPhatSinh(i, 4) Like TaiKhoan & "*" And ArrPhatSinh(i, 7) <> "" Then
If Not Dic.Exists(ArrPhatSinh(i, 6) & vbBack & ArrPhatSinh(i, 7)) Then
Dic.Add ArrPhatSinh(i, 6) & vbBack & ArrPhatSinh(i, 7), ArrPhatSinh(i, 11) & "/" & ArrPhatSinh(i, 10) & vbBack
Else
Dic.Item(ArrPhatSinh(i, 6) & vbBack & ArrPhatSinh(i, 7)) = Dic.Item(ArrPhatSinh(i, 6) & vbBack & ArrPhatSinh(i, 7)) & ArrPhatSinh(i, 11) & "/" & ArrPhatSinh(i, 10) & vbBack
End If
End If
If ArrPhatSinh(i, 5) Like TaiKhoan & "*" And ArrPhatSinh(i, 9) <> "" Then
If Not Dic.Exists(ArrPhatSinh(i, 8) & vbBack & ArrPhatSinh(i, 9)) Then
ArrTest(i, 1) = 0
Else
Item = Dic.Item(ArrPhatSinh(i, 8) & vbBack & ArrPhatSinh(i, 9))
SoLuong = ArrPhatSinh(i, 10)
SoTien = 0
Do Until SoLuong = 0
If Item = "" Then
Gia = "0/999999999999"
Else
Gia = Left(Item, InStr(Item, vbBack) - 1)
End If
SoLuongTon = CDbl(Right(Gia, InStr(StrReverse(Gia), "/") - 1))
SoTienTon = CDbl(Left(Gia, InStr(Gia, "/") - 1))
If SoLuong >= SoLuongTon Then
SoLuong = SoLuong - SoLuongTon
SoTien = SoTien + SoTienTon
If Gia <> "0/999999999999" Then Item = Right(Item, Len(Item) - Len(Gia) - 1)
Else
SoTien = SoTien + Round(SoLuong * Evaluate(Gia), 0)
If Gia <> "0/999999999999" Then Item = (SoTienTon - Round(SoLuong * Evaluate(Gia), 0)) & "/" & (SoLuongTon - SoLuong) & Right(Item, Len(Item) - Len(Gia))
SoLuong = 0
End If
Loop
ArrTest(i, 1) = SoTien
Dic.Item(ArrPhatSinh(i, 8) & vbBack & ArrPhatSinh(i, 9)) = Item
End If
End If
Next
PhatSinh.Range("L5:L" & EndRow).Value = ArrTest
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub
Private Sub TinhGiaXK_LIFO()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim ArrPhatSinh, Dic, EndRow As Long, i As Long, TaiKhoan As String, SoLuong As Double, SoLuongTon As Double, SoTien As Double, SoTienTon As Double, Item As String, Gia As String
TaiKhoan = PhatSinh.[E2].Value
Set Dic = CreateObject("Scripting.Dictionary")
ArrPhatSinh = DauKy.Range("A3:E" & DauKy.[A65536].End(xlUp).Row + 1).Value
For i = 1 To UBound(ArrPhatSinh, 1) - 1
If ArrPhatSinh(i, 1) Like TaiKhoan & "*" Then
Dic.Add ArrPhatSinh(i, 2) & vbBack & ArrPhatSinh(i, 3), vbBack & "0/999999999999" & vbBack & ArrPhatSinh(i, 5) & "/" & ArrPhatSinh(i, 4)
End If
Next
EndRow = PhatSinh.[A:K].Find(What:="*", LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1
ArrPhatSinh = PhatSinh.Range("A5:K" & EndRow).Value
ArrTest = PhatSinh.Range("L5:L" & EndRow).Value
For i = 1 To UBound(ArrPhatSinh, 1)
If ArrPhatSinh(i, 4) Like TaiKhoan & "*" And ArrPhatSinh(i, 7) <> "" Then
If Not Dic.Exists(ArrPhatSinh(i, 6) & vbBack & ArrPhatSinh(i, 7)) Then
Dic.Add ArrPhatSinh(i, 6) & vbBack & ArrPhatSinh(i, 7), vbBack & "0/999999999999" & vbBack & ArrPhatSinh(i, 11) & "/" & ArrPhatSinh(i, 10)
Else
Dic.Item(ArrPhatSinh(i, 6) & vbBack & ArrPhatSinh(i, 7)) = Dic.Item(ArrPhatSinh(i, 6) & vbBack & ArrPhatSinh(i, 7)) & vbBack & ArrPhatSinh(i, 11) & "/" & ArrPhatSinh(i, 10)
End If
End If
If ArrPhatSinh(i, 5) Like TaiKhoan & "*" And ArrPhatSinh(i, 9) <> "" Then
If Not Dic.Exists(ArrPhatSinh(i, 8) & vbBack & ArrPhatSinh(i, 9)) Then
ArrTest(i, 1) = 0
Else
Item = Dic.Item(ArrPhatSinh(i, 8) & vbBack & ArrPhatSinh(i, 9))
SoLuong = ArrPhatSinh(i, 10)
SoTien = 0
Do Until SoLuong = 0
Gia = Right(Item, InStr(StrReverse(Item), vbBack) - 1)
SoLuongTon = CDbl(Right(Gia, InStr(StrReverse(Gia), "/") - 1))
SoTienTon = CDbl(Left(Gia, InStr(Gia, "/") - 1))
If SoLuong >= SoLuongTon Then
SoLuong = SoLuong - SoLuongTon
SoTien = SoTien + SoTienTon
Item = Left(Item, Len(Item) - Len(Gia) - 1)
Else
SoTien = SoTien + Round(SoLuong * Evaluate(Gia), 0)
Item = Left(Item, Len(Item) - Len(Gia)) & (SoTienTon - Round(SoLuong * Evaluate(Gia), 0)) & "/" & (SoLuongTon - SoLuong)
SoLuong = 0
End If
Loop
ArrTest(i, 1) = SoTien
Dic.Item(ArrPhatSinh(i, 8) & vbBack & ArrPhatSinh(i, 9)) = Item
End If
End If
Next
PhatSinh.Range("L5:L" & EndRow).Value = ArrTest
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub