Sub Bom_Filter1()
Dim wsFilter As Worksheet, wsListCode As Worksheet
Dim wsReport As Worksheet, wsListNoBom As Worksheet
Dim rngBom As Range, rngBom1 As Range, rngReportDel As Range, rngNoBomDel As Range
Dim sMaterialNo As String, i As Long, j As Long, k As Long
Dim lRowNoBom As Long
Dim sSemiProNo As String, FirstRowOffset As Long, lLastRow As Long
Dim vSoLuong
With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
.StatusBar = VNI("Ñang tính toùan...Xin chôø trong giaây laùt.")
End With
Set wsFilter = Worksheets("BOM")
Set wsListCode = Worksheets("LIST_TO_EXPORT")
Set wsReport = Worksheets("REPORT")
Set wsListNoBom = Worksheets("NO_BOM")
'Xoa vung truoc khi xuat
Set rngReportDel = Worksheets("REPORT").Range("tb_Report_Del")
Set rngNoBomDel = Worksheets("NO_BOM").Range("tb_No_BOM")
rngReportDel.ClearContents
rngNoBomDel.ClearContents
Set rngReportDel = Nothing
j = 0: FirstRowOffset = 0: lRowNoBom = 4
lLastRow = LastRow(Sheet3)
sMaterialNo = Trim(wsListCode.Range("A4").Offset(FirstRowOffset, 0))
vSoLuong = wsListCode.Range("A4").Offset(FirstRowOffset, 2)
If Len(sMaterialNo) = 0 Then
MsgBoxUni VNI("Baïn khoâng coù maõ naøo ñeå xuaát."), vbInformation + vbOKOnly, VNI("Thoâng baùo")
GoTo Bom_Filter1_Exit
End If
'Vong lap de lap lai danh sach
Do While Len(sMaterialNo) = 10
wsFilter.Range("A3").AutoFilter Field:=1, Criteria1:=sMaterialNo
On Error Resume Next
Set rngBom = Range("tb_Bom").SpecialCells(xlCellTypeVisible)
If Err.Number = 1004 Then
wsListNoBom.Cells(lRowNoBom, 1) = sMaterialNo
lRowNoBom = lRowNoBom + 1
Err.Clear
MsgBoxUni VNI("Saûn phaåm chöa coù BOM " & sMaterialNo), vbInformation + vbOKOnly, VNI("Thoâng baùo")
GoTo Bom_Filter1_End_Loop
End If
If rngBom.Rows.Count > 0 Then
For i = 1 To rngBom.Rows.Count
sSemiProNo = Trim(rngBom.Cells(i, 9))
If Len(sSemiProNo) = 10 Then
With wsReport.Range("A4")
.Offset(j, 0) = rngBom.Cells(i, 1) 'Ma san pham
.Offset(j, 1) = rngBom.Cells(i, 2) 'Mo ta san pham
.Offset(j, 2) = rngBom.Cells(i, 9) 'Ma Component
.Offset(j, 3) = rngBom.Cells(i, 10) 'Mo ta Component
.Offset(j, 4) = rngBom.Cells(i, 19) * vSoLuong 'So luong
.Offset(j, 5) = rngBom.Cells(i, 20) 'Don vi tinh
End With
j = j + 1
End If
Next i
End If
Bom_Filter1_End_Loop:
FirstRowOffset = FirstRowOffset + 1
sMaterialNo = Trim(wsListCode.Range("A4").Offset(FirstRowOffset, 0))
If lLastRow > 3 Then
Application.StatusBar = VNI("Ñang tính toùan...Xin chôø trong giaây laùt. Ñang thöïc hieän tôùi ") & FirstRowOffset & "/" & (lLastRow - 3)
End If
Loop
If Err.Number <> 0 Then
MsgBoxUni VNI("Xin baïn xem laïi code! Coù loãi xaõy ra."), vbInformation + vbOKOnly, VNI("Thoâng baùo")
Else
MsgBoxUni VNI("Baïn ñaõ hoøan taát xuaát BOM."), vbInformation + vbOKOnly, VNI("Thoâng baùo")
End If
'Giai phong bo nho
Bom_Filter1_Exit:
Set rngBom = Nothing: Set rngBom1 = Nothing
Set wsFilter = Nothing: Set wsListCode = Nothing: Set wsReport = Nothing
With Application
.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
.StatusBar = False
End With
End Sub