Kính gửi các anh chị
Sau một thời gian nghiên cứu, nhờ Copilot em mới nghiên cứu được code như file đính kèm. Các anh chị giúp em thêm phần code để lặp lại quá trình chèn thêm dòng nếu các dòng mới chèn vấn là Bán thành phẩm ạ.
- Đầu tiên em sẽ phân biệt dựa theo cột A sheet BOM1 để biết đâu là BOM sản phẩm (cấp 1) đâu là BOM BTP.
- Tách ra 2 sheet TP và BTP tương ứng
- Ở sheet TP em tìm ở cột D xem mã vật tư nào là Bán thành phẩm thì lấy dữ liệu ở sheet BTP để thêm vào
- Nếu dòng mới thêm vào vẫn có mã vật tư là Bán thành phẩm thì tiếp tục chèn tiếp đến khi nào không xuất hiện nữa thì thôi (em đang gặp khó ở bước này đối với VBA cho Excel còn Java Script cho google sheet đã chạy tốt, tuy nhiên dữ liệu của em rất lớn nên thời gian chạy trên google sheet không đủ - chỉ cho chạy tối đa 6p).
Em gửi cả 2 loại code kính mong các bác giúp đỡ em với ạ.
VBA:
Sub InsertRowsForBTPWithDataAndCalculate()
Dim wsTP As Worksheet
Dim wsBTP As Worksheet
Dim tpData As Variant
Dim btpData As Variant
Dim btpCount As Object
Dim lastRowTP As Long
Dim i As Long, j As Long, k As Long
Dim maVatTu As String
Dim numRowsToInsert As Long
Dim startRow As Long
Dim quantity As Double
Dim newBTPFound As Boolean
' Ð?t worksheet c?n làm vi?c
On Error Resume Next
Set wsTP = ThisWorkbook.Sheets("TP")
Set wsBTP = ThisWorkbook.Sheets("BTP")
On Error GoTo 0
' Ki?m tra n?u sheets không t?n t?i
If wsTP Is Nothing Or wsBTP Is Nothing Then
MsgBox "Ki?m tra tên sheet, có th? không t?n t?i: TP ho?c BTP.", vbExclamation
Exit Sub
End If
' L?y d? li?u t? sheet TP
tpData = wsTP.UsedRange.Value
' L?y d? li?u t? sheet BTP
btpData = wsBTP.UsedRange.Value
' T?o d?i tu?ng Dictionary d? luu s? l?n xu?t hi?n c?a m?i mã s?n ph?m trong BTP
Set btpCount = CreateObject("Scripting.Dictionary")
For i = 2 To UBound(btpData, 1)
maVatTu = btpData(i, 1)
If Not btpCount.exists(maVatTu) Then
btpCount.Add maVatTu, 1
Else
btpCount(maVatTu) = btpCount(maVatTu) + 1
End If
Next i
' Xác d?nh dòng cu?i cùng có d? li?u trong sheet TP
lastRowTP = wsTP.Cells(wsTP.Rows.Count, "A").End(xlUp).Row
' Duy?t qua các dòng t? cu?i lên d?u d? chèn các dòng m?i du?i m?i ô ? c?t I có giá tr? là BTP
For i = lastRowTP To 2 Step -1
maVatTu = wsTP.Cells(i, 4).Value ' Mã v?t tu c?t D
If btpCount.exists(maVatTu) Then ' Ki?m tra n?u mã v?t tu xu?t hi?n trong BTP
wsTP.Cells(i, 9).Value = "BTP" ' Ð?t giá tr? "BTP" vào c?t I
numRowsToInsert = btpCount(maVatTu)
wsTP.Cells(i, 10).Value = numRowsToInsert ' Ð?t s? dòng c?n chèn vào c?t J
If numRowsToInsert > 0 Then
startRow = i + 1
wsTP.Rows(startRow & ":" & startRow + numRowsToInsert - 1).Insert Shift:=xlDown
' Tìm t?t c? các hàng tuong ?ng trong BTP và chèn vào TP
For k = 2 To UBound(btpData, 1)
If btpData(k, 1) = maVatTu Then
' Ði?n d? li?u vào dòng m?i
wsTP.Cells(startRow, 1).Value = wsTP.Cells(i, 1).Value ' Mã s?n ph?m
wsTP.Cells(startRow, 2).Value = wsTP.Cells(i, 2).Value ' S?n ph?m
wsTP.Cells(startRow, 3).Value = wsTP.Cells(i, 3).Value ' ÐVT
wsTP.Cells(startRow, 4).Value = btpData(k, 4) ' Mã v?t tu
wsTP.Cells(startRow, 5).Value = btpData(k, 5) ' V?t tu
wsTP.Cells(startRow, 6).Value = btpData(k, 6) ' ÐVT v?t tu
wsTP.Cells(startRow, 7).Value = btpData(k, 7) ' Lo?i d?nh m?c
' Tính s? lu?ng m?i ? c?t H
quantity = wsTP.Cells(i, 8).Value * btpData(k, 8)
wsTP.Cells(startRow, 8).Value = quantity
' Ki?m tra và d?t giá tr? cho c?t I (Lo?i) và c?t J (S? dòng) c?a dòng m?i
If btpCount.exists(btpData(k, 4)) Then
wsTP.Cells(startRow, 9).Value = "BTP"
wsTP.Cells(startRow, 10).Value = btpCount(btpData(k, 4))
End If
wsTP.Rows(startRow).Interior.Color = RGB(255, 255, 0) ' Ð?t màu n?n cho dòng m?i (vàng)
startRow = startRow + 1
End If
Next k
End If
End If
Next i
MsgBox "Hoàn t?t chèn các dòng du?i các ô có giá tr? là BTP v?i n?i dung tuong ?ng và tính toán s? lu?ng."
End Sub
Java Script
function insertBTPRowsIntoTP() {
var tpSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("TP"); // Thay "TP" bằng tên sheet của bạn
var btpSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("BTP");
// Lấy dữ liệu từ sheet TP
var tpData = tpSheet.getDataRange().getValues();
// Lấy dữ liệu từ sheet BTP
var btpData = btpSheet.getDataRange().getValues();
// Tạo một đối tượng để lưu số lần xuất hiện của mỗi mã linh kiện trong BTP
var btpMap = {};
for (var i = 1; i < btpData.length; i++) { // Bắt đầu từ 1 để bỏ qua tiêu đề
var maLinhKien = btpData
[0];
if (!btpMap[maLinhKien]) {
btpMap[maLinhKien] = [];
}
btpMap[maLinhKien].push({
rowData: btpData.slice(3, 7), // Lưu dữ liệu từ cột D đến G
quantity: btpData[7] // Lưu số lượng
});
}
// Hàm đệ quy để chèn các dòng BTP và bổ sung ô trống
function insertRows(sheet, startRow, maLinhKienTP, quantityMultiplier, maSanPhamTP, tenSanPhamTP, donViTinhTP) {
if (!btpMap[maLinhKienTP]) return; // Nếu không có mã linh kiện trong BTP thì dừng lại
var numRowsToInsert = btpMap[maLinhKienTP].length;
for (var j = 0; j < numRowsToInsert; j++) {
sheet.insertRowAfter(startRow);
var newRow = [maSanPhamTP, tenSanPhamTP, donViTinhTP].concat(btpMap[maLinhKienTP][j].rowData).concat(quantityMultiplier * btpMap[maLinhKienTP][j].quantity);
var newRange = sheet.getRange(startRow + 1, 1, 1, 8); // Cột A đến H
newRange.setValues([newRow]); // Chèn dữ liệu vào từ cột A đến H
newRange.setBackground('#FFFF00'); // Đặt màu nền cho dòng mới (vàng)
startRow++;
// Kiểm tra mã linh kiện trong các dòng vừa chèn xem có là BTP không
var nestedMaLinhKienTP = newRow[3]; // Mã linh kiện mới (cột D)
var nestedQuantity = quantityMultiplier * btpMap[maLinhKienTP][j].quantity;
insertRows(sheet, startRow, nestedMaLinhKienTP, nestedQuantity, newRow[0], newRow[1], newRow[2]);
}
}
// Duyệt qua sheet TP để chèn các dòng từ BTP
for (var i = tpData.length - 1; i >= 1; i--) { // Bắt đầu từ cuối để tránh xung đột khi chèn
var maLinhKienTP = tpData[3]; // Mã linh kiện cột D
var tpQuantity = tpData[7]; // Số lượng cột H
var maSanPhamTP = tpData[0]; // Mã sản phẩm cột A
var tenSanPhamTP = tpData[1]; // Tên sản phẩm cột B
var donViTinhTP = tpData[2]; // Đơn vị tính cột C
insertRows(tpSheet, i, maLinhKienTP, tpQuantity, maSanPhamTP, tenSanPhamTP, donViTinhTP);
}
}
Do file google sheet hôm trước em gửi ở dạng excel nên em gửi lại ạ
docs.google.com
