Hỏi về code VBA thay thế cho sử dụng VLOOKUP

[boy_cn_hb]

Em có 1 file excel quản lý đơn hàng gồm 2 sheet.
Sheet "DON HANG" nhập dữ liệu về đơn
Sheet "LENH EP" để làm lệnh sản xuất
Sau khi nhập đủ dữ liệu đơn hàng, em muốn làm lệnh sản xuất bằng cách gõ số đơn vào ô C7 ở sheet "LENH EP"
Khi gõ số đơn vào thì sẽ lấy thông tin toàn bộ các mã hàng thuộc đơn đó đẩy sang từ sheet "DON HANG" sang cột tương ứng ở "LENH EP"
Tức là nếu đơn đó có 2 mã sản phẩm thì sang lệnh sản xuất cũng lấy đủ 2 dòng
Nếu dùng VLOOKUP thì em làm được nhưng muốn cải thiện tốc độ bằng VBA
Mong các cao nhân giúp đỡ

 

[Ba mười ba]

Em có 1 file excel quản lý đơn hàng gồm 2 sheet.
Sheet "DON HANG" nhập dữ liệu về đơn
Sheet "LENH EP" để làm lệnh sản xuất
Sau khi nhập đủ dữ liệu đơn hàng, em muốn làm lệnh sản xuất bằng cách gõ số đơn vào ô C7 ở sheet "LENH EP"
Khi gõ số đơn vào thì sẽ lấy thông tin toàn bộ các mã hàng thuộc đơn đó đẩy sang từ sheet "DON HANG" sang cột tương ứng ở "LENH EP"
Tức là nếu đơn đó có 2 mã sản phẩm thì sang lệnh sản xuất cũng lấy đủ 2 dòng
Nếu dùng VLOOKUP thì em làm được nhưng muốn cải thiện tốc độ bằng VBA
Mong các cao nhân giúp đỡ

Xem công thức "VLOOKUP" của bạn tôi không hiểu,tôi không thấy trả về kết quả mẫu,kết quả mẫu sẽ như thế nào vậy?

 

[Nhattanktnn]

Em có 1 file excel quản lý đơn hàng gồm 2 sheet.
Sheet "DON HANG" nhập dữ liệu về đơn
Sheet "LENH EP" để làm lệnh sản xuất
Sau khi nhập đủ dữ liệu đơn hàng, em muốn làm lệnh sản xuất bằng cách gõ số đơn vào ô C7 ở sheet "LENH EP"
Khi gõ số đơn vào thì sẽ lấy thông tin toàn bộ các mã hàng thuộc đơn đó đẩy sang từ sheet "DON HANG" sang cột tương ứng ở "LENH EP"
Tức là nếu đơn đó có 2 mã sản phẩm thì sang lệnh sản xuất cũng lấy đủ 2 dòng
Nếu dùng VLOOKUP thì em làm được nhưng muốn cải thiện tốc độ bằng VBA
Mong các cao nhân giúp đỡ

Thử:

Sub LocDuLieu()
Dim sArr(), dArr(), LotNo$, Tmp$, Dims, sLr&, dLr&, I&, J&, K&
Const StartRow = 11 'Dong dau tien chua du lieu trong sheet "LENH EP"
Application.ScreenUpdating = False
With Sheets("LENH EP")
    dLr = .Range("A" & Rows.Count).End(xlUp).Row
    If dLr > StartRow Then .Rows(StartRow & ":" & dLr - 1).Delete
    LotNo = .[c7].Value
End With
With Sheets("DON HANG")
    sLr = .Range("A" & Rows.Count).End(xlUp).Row
    sArr = .Range("A5:O" & sLr).Value
End With
ReDim dArr(1 To UBound(sArr, 1), 1 To 15)
For I = 1 To UBound(sArr)
    If sArr(I, 1) = LotNo Then
        K = K + 1
        dArr(K, 1) = K              'STT
        dArr(K, 2) = sArr(I, 2)     'Ngay sx
        dArr(K, 3) = sArr(I, 5)     'mau giay
        dArr(K, 4) = "ÐH S" & ChrW(7889) & ": " & LotNo 'So don hang
        dArr(K, 5) = sArr(I, 15)     'sl ep
        Tmp = sArr(I, 7)
        Dims = Split(Tmp, "*")
            For J = 0 To UBound(Dims)
                If UBound(Dims) <> 2 Then
                    MsgBox "Sai thong so kich thuoc"
                    Exit For
                Else
                    dArr(K, 6 + J) = Dims(J)   'kich thuoc
                End If
            Next
        dArr(K, 9) = sArr(I, 8)     'be mat
        dArr(K, 10) = sArr(I, 11)     'cot hdf
        dArr(K, 11) = sArr(I, 12)     'can bang
        dArr(K, 12) = sArr(I, 13)     'chong xuoc
        dArr(K, 13) = sArr(I, 9)      'De
        dArr(K, 14) = sArr(I, 10)      'hem sx
    End If
Next
With Sheets("LENH EP")
    If K Then
        .Rows(StartRow).Resize(K).Insert
        .Range("A" & StartRow).Resize(K, 15) = dArr
        .Range("E" & dLr) = WorksheetFunction.Sum(Range("E" & StartRow & ":E" & dLr - 1))
    End If
End With
Application.ScreenUpdating = True
End Sub

 

[boy_cn_hb]

Cám ơn các bác. Em đã làm được rồi.
Em có 1 thắc mắc muốn hỏi các bác.
Em có dùng "Application.WorksheetFunction.VLookup" để thay thế hàm Vlookup trực tiếp trên file.
Như ở trong code của em ở sheet CAC DH thì nếu không có đoạn bắt lỗi, nó sẽ báo lỗi 1004.
Em chèn thêm đoạn bắt lỗi vào thì code chạy được.
Nhưng lại có 1 lỗi là nếu như em nhập liệu vào cột G thì dữ liệu không tự nhảy ngay mà phải click chuột lại 1 lần nữa vào ô đó dữ liệu mới nhảy
Các bác xem file đính kèm chỉ giáo giúp em với

 

[Cu Tồ]

Cám ơn các bác. Em đã làm được rồi.
Em có 1 thắc mắc muốn hỏi các bác.
Em có dùng "Application.WorksheetFunction.VLookup" để thay thế hàm Vlookup trực tiếp trên file.
Như ở trong code của em ở sheet CAC DH thì nếu không có đoạn bắt lỗi, nó sẽ báo lỗi 1004.
Em chèn thêm đoạn bắt lỗi vào thì code chạy được.
Nhưng lại có 1 lỗi là nếu như em nhập liệu vào cột G thì dữ liệu không tự nhảy ngay mà phải click chuột lại 1 lần nữa vào ô đó dữ liệu mới nhảy
Các bác xem file đính kèm chỉ giáo giúp em với

Bạn thay sự kiện [Worksheet_SelectionChange] thành [Worksheet_Change] thử xem
Với lại còn phải xem bạn copy vào thì target lúc này là một cell hay nhiều cell nữa

 

[Nhattanktnn]

Cám ơn các bác. Em đã làm được rồi.
Em có 1 thắc mắc muốn hỏi các bác.
Em có dùng "Application.WorksheetFunction.VLookup" để thay thế hàm Vlookup trực tiếp trên file.
Như ở trong code của em ở sheet CAC DH thì nếu không có đoạn bắt lỗi, nó sẽ báo lỗi 1004.
Em chèn thêm đoạn bắt lỗi vào thì code chạy được.
Nhưng lại có 1 lỗi là nếu như em nhập liệu vào cột G thì dữ liệu không tự nhảy ngay mà phải click chuột lại 1 lần nữa vào ô đó dữ liệu mới nhảy
Các bác xem file đính kèm chỉ giáo giúp em với

Mình có 3 điều muốn nói:
1/ Nếu dùng VBA thì không nên dùng vlookup method. Còn đã dùng vlookup thì dùng hàm sẵn trong excel cho tiện chứ VBA gì cho cồng kềnh
2/ Vấn đề bạn hỏi, lỗi là do khi thay đổi target (vì bạn đang chọn selectionchange), nếu target = empty thì vlookup vẫn tính, dẫn đến lỗi. Nếu muốn phải thêm điều kiện if i=empty then exit sub
3/ Bạn nên sửa thành worksheet change chứ ko phải seclection change thì có lẽ hay hơn

Hiểu biết hạn hẹp nên mình góp ý nhiêu đó

 

[boy_cn_hb]

Mình có 3 điều muốn nói:
1/ Nếu dùng VBA thì không nên dùng vlookup method. Còn đã dùng vlookup thì dùng hàm sẵn trong excel cho tiện chứ VBA gì cho cồng kềnh
2/ Vấn đề bạn hỏi, lỗi là do khi thay đổi target (vì bạn đang chọn selectionchange), nếu target = empty thì vlookup vẫn tính, dẫn đến lỗi. Nếu muốn phải thêm điều kiện if i=empty then exit sub
3/ Bạn nên sửa thành worksheet change chứ ko phải seclection change thì có lẽ hay hơn

Hiểu biết hạn hẹp nên mình góp ý nhiêu đó

Cám ơn bạn nhiều. Vì mình hiện tại chưa làm được code thay thế vlookup bằng vba nên mới thử cách này để cải thiện tốc độ file
Hiện tại file của mình lên tới hơn 300MB. Mình có đọc thì thấy bảo thay thế bằng function vlookup cũng giảm được tốc độ 1 chút nên mới làm.

 

[Ba mười ba]

..
1/ Nếu dùng VBA thì không nên dùng vlookup method.
..

Tôi không không hiểu nhược điểm gì mà không sử dụng " Application.WorksheetFunction.VLookup "?
Nó đơn giản quá? hay nó không tối ưu bằng hàm tự tạo hay một thủ tục... tương đương? hay về vấn đề tốc độ?
Bạn có thể cho tôi biết rõ hơn vì sao không nên được không? Bởi những người không hiểu nhiều về code như tôi lại hay sử dụng "WorksheetFunction".
Cảm ơn

 

[vanaccex]

Tôi không không hiểu nhược điểm gì mà không sử dụng " Application.WorksheetFunction.VLookup "?

Bạn thử với dữ liệu lớn hơn xem sẽ thấy

 

[boy_cn_hb]

Em vừa copy code vào file chính thì thấy nếu giữ nguyên công thức tại ô lại nhanh hơn là chạy hàm vlookup từ Worksheet.Function.
Vì em có theo dõi thấy hình như do file của em nặng nên 3 dòng code của em nó tính toán 3 lần, và mỗi lần lại mất thêm 1 ít thời gian nữa.
Các bác có thể giải thích giúp em được k ạ?

 

[Cu Tồ]

Em vừa copy code vào file chính thì thấy nếu giữ nguyên công thức tại ô lại nhanh hơn là chạy hàm vlookup từ Worksheet.Function.
Vì em có theo dõi thấy hình như do file của em nặng nên 3 dòng code của em nó tính toán 3 lần, và mỗi lần lại mất thêm 1 ít thời gian nữa.
Các bác có thể giải thích giúp em được k ạ?

mình thấy nếu file nặng thì chỗ nào chỉ cần chứa giá trị mà ko cần công thức để cập nhật nữa thì bạn thay công thức bằng chính giá trị kết quả như thế sẽ tối ưu được một phần dung lượng file. Còn vì sao nó chạy 3 lần thì nó cũng là tìm và gán kết quả,code cũng chạy theo thứ tự,gán tới ô nào nó tính giá trị vào ô đó file bạn nặng sẵn rồi nó sẽ có độ trễ (vì cũng không biết file chính ô mà bạn gán công thức có tác động tính toán đên các công thức khác không)

 

[boy_cn_hb]

mình thấy nếu file nặng thì chỗ nào chỉ cần chứa giá trị mà ko cần công thức để cập nhật nữa thì bạn thay công thức bằng chính giá trị kết quả như thế sẽ tối ưu được một phần dung lượng file. Còn vì sao nó chạy 3 lần thì nó cũng là gán công thức,code cũng chạy theo thứ tự,gán tới ô nào nó tính giá trị vào ô đó file bạn nặng sẵn rồi nó sẽ có độ trễ

Vấn đề là dường như nếu dùng công thức ngay tại ô tính thì nó sẽ tính 1 lần cho cả trang, nên nếu file nặng thì mình cũng chỉ phải chờ 1 lần.
Còn nếu dùng trong vba thì mình sẽ phải chờ nhiều lần tuỳ theo số lần mình sử dụng công thức.

 

[Cu Tồ]

Vấn đề là dường như nếu dùng công thức ngay tại ô tính thì nó sẽ tính 1 lần cho cả trang, nên nếu file nặng thì mình cũng chỉ phải chờ 1 lần.
Còn nếu dùng trong vba thì mình sẽ phải chờ nhiều lần tuỳ theo số lần mình sử dụng công thức.

Cũng không rõ,mình kiến thức cũng hạn hẹp,nếu theo lý thiết degbug thì thấy nó chạy vòng lặp tìm rồi gán từng giá trị,nếu chạy 3 lần thì gán 3 lần giá trị,nhưng nếu lần gán giá trị thứ nhất xong mà lại gặp ngay tính toán của các công thức excel trên bảng tính khi có giá trị mới vừa được cập nhật vào (trong trường họp các ô gán giá trị đó có liên quan đến công thức khác) thì excel lại tính toán.tính toán xong vba lại chạy lần gán kết quả lần 2. và vòng lặp như thế. kiến thức hạn hẹp không biết có đúng thế không nữa.theo ý kiến ca nhân thôi không đúng bỏ qua nha bạn hihi

 

[boy_cn_hb]

Mình cũng gà mờ đang tìm hiểu nên có thể câu hỏi cũng k rõ nghĩa lắm. Cảm ơn bạn nha.