Giúp tối ưu Marco trong Excel

[zakkblack]

Nhờ các bác giúp sớm. Em hỏi mãi một nơi mà ko có ai rảnh vào giúp cả.

Em đang cố gắng tự học VBA trong Excel dần dần vì thấy nó quá hữu ích, nhất là đối với trường hợp file xuất hàng bên dưới của em.

Lúc đầu e chỉ sử dụng chủ yếu Hàm Sumproduct trong Excel để tổng hợp số liệu. Tuy nhiên, vấn đề là file chạy rất chậm (vô cùng chậm, đối với máy cổ cổ tầm P4 2.0Ghz thì mỗi lần nhập được mã code khách hàng xong cũng phải đợi tầm 15 - 20s mới nhẩy ra được tên và địa chỉ khách, nhập tiếp số lượng thì lại đợi tầm 30s nữa => Kinh hoàng)

Sau đó, để giải quyết vấn đề khi nhập liệu em đã sử dụng cách ghi Macro (vì ko biết viết Macro) để thực hiện các công việc Tổng hợp số liệu.
Kết quả rất tốt, vẫn chiếc máy đó nhưng chạy nhanh như gió khi nhập liệu và khi chạy Macro thì cũng thừa sức.

Tuy nhiên, được Voi đòi Hai Bà. Vấn đề hiện tại là giờ em đã trót bước vào con đường nghiện ngập Marco rồi nên đứng ngồi ko yên với cách ghi Macro thủ công như vậy.

Em gửi file của em lên, để các cao thủ chỉ giáo.
Có rất nhiều vấn đề cần tối ưu (nói riêng về phần lập trình) tuy nhiên do trình độ vỡ lòng nên hiện tại em đang cần được chỉ giáo mấy chỗ sau:

1. Để làm công thức cho các ô cần tính em làm 1 ô đầu tiên
sau đó copy formula cho các ô còn lại,
tiếp theo chọn toàn bộ các ô đã có kết quả, copy rồi paste value.
Rất đơn sơ thế thôi, nên em cần chỉ giáo cách chuyển đổi sang ngôn ngữ lập trình tối ưu. Chứ làm thế kia thì có vẻ thủ công quá.

ActiveCell.FormulaR1C1 = _
"=SUMPRODUCT((DuLieuXuatHang!R7C1:R5000C1=Chitiet_Thang!RC17)*(DuLieuXuatHang!R7C9:R5000C9=Chitiet_Thang!R24C)*DuLieuXuatHang!R7C11:R5000C11)/R23C2"
Range("B25").Select
Selection.Copy
Range("B25:F36").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone,

2. Em rất cần cách làm thế nào để mà chỉ chọn vùng (Range) cần chọn thôi ví dụ đến dòng cuối cùng có dữ liệu. Trong công thức tính Sumproduct của em thì em cứ chọn sẵn đến dòng thứ 5000. Rất mất thời gian xử lý của máy và ko tối ưu.

3. Em cũng đã cố gắng cóp nhặt và tự làm được cách là chỉ chọn vùng từ ô E6 đến P và dòng cuối cùng có dữ liệu như sau, nhưng cũng có vẻ chưa ổn lắm. Em chắc phải có cách hiệu quả và chặt chẽ hơn.

' Lua chon vung du lieu den dong cuoi cung co du lieu de chuan bi Paste cong thuc
Dim row_i As Long
row_i = ActiveSheet.UsedRange.Rows.Count + ActiveSheet.UsedRange.Row
Range("E6", Cells(row_i - 1, "P")).Select

Tóm lại, em cần các bác chỉ giáo thêm để hoàn thiện Macro của em một cách chặt chẽ nhất nhưng đủ để trình vỡ lòng như em hiểu và có thể làm được, phát triển tiếp về sau.

Cảm ơn rất nhiều !

Sub tonghop()
'
' tonghop Macro
'
' Keyboard Shortcut: Ctrl+q
'
Range("B25").Select
ActiveCell.FormulaR1C1 = _
"=SUMPRODUCT((DuLieuXuatHang!R7C1:R5000C1=Chitiet_Thang!RC17)*(DuLieuXuatHang!R7C9:R5000C9=Chitiet_Thang!R24C)*DuLieuXuatHang!R7C11:R5000C11)/R23C2"
Range("B25").Select
Selection.Copy
Range("B25:F36").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Range("J25").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = _
"=SUMPRODUCT((DuLieuXuatHang!R7C1:R5000C1=Chitiet_Thang!RC17)*(DuLieuXuatHang!R7C9:R5000C9=Chitiet_Thang!R24C)*DuLieuXuatHang!R7C13:R5000C13)/1000000"
Range("J25").Select
Selection.Copy
Range("J25:N36").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Range("R25").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = _
"=SUMPRODUCT((DuLieuXuatHang!R7C1:R5000C1=Chitiet_Thang!RC17)*(DuLieuXuatHang!R7C9:R5000C9=Chitiet_Thang!R24C)*DuLieuXuatHang!R7C11:R5000C11)"
Range("R25").Select
Selection.Copy
Range("R2536").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Range("R2536").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Application.CutCopyMode = False

Range("A2").Select

End Sub

Sub chitietkhoiluongkhach()
'
' chitietkhoiluongkhach Macro
'
' Tinh gia tri chi tiet theo khoi luong cua 1 khach hang tai o E6\
Range("E6").Select
ActiveCell.FormulaR1C1 = _
"=SUMPRODUCT((DuLieuXuatHang!R7C1:R5000C1=Chitiet_Khach!R5C)*(DuLieuXuatHang!R7C4:R5000C4=Chitiet_Khach!RC2)*DuLieuXuatHang!R7C14:R5000C14)"
Range("E6").Select
Selection.Copy

' Lua chon vung du lieu den dong cuoi cung co du lieu de chuan bi Paste cong thuc
Dim row_i As Long
row_i = ActiveSheet.UsedRange.Rows.Count + ActiveSheet.UsedRange.Row
Range("E6", Cells(row_i - 1, "P")).Select

Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Application.CutCopyMode = False

' Chuyen den dong cuoi cung csdl
Cells(row_i, "Q").Select

End Sub

Sub chitietloaihangkhach()
'
' chitietloaihang/hangkhach Macro
'
Range("E6").Select
ActiveCell.FormulaR1C1 = _
"=SUMPRODUCT((DuLieuXuatHang!R7C1:R5000C1=Chitiet_Nhanhang!R5C)*(DuLieuXuatHang!R7C4:R5000C4=Chitiet_Nhanhang!RC2)*(DuLieuXuatHang!R7C8:R5000C8=Chitiet_Nhanhang!R2C3)*(DuLieuXuatHang!R7C9:R5000C9=Chitiet_Nhanhang!R3C3)*DuLieuXuatHang!R7C14:R5000C14)"

Range("E6").Select
Selection.Copy

' Lua chon vung du lieu den dong cuoi cung co du lieu de chuan bi Paste cong thuc
Dim row_i As Long
row_i = ActiveSheet.UsedRange.Rows.Count + ActiveSheet.UsedRange.Row
Range("E6", Cells(row_i - 1, "P")).Select

Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Application.CutCopyMode = False

' Chuyen den dong cuoi cung csdl
Cells(row_i, "Q").Select

End Sub

Sub chitietthanhtienkhach()
'
' chitietthanhtienkhach Macro
'
Dim row_j As Long
row_j = ActiveSheet.UsedRange.Rows.Count + ActiveSheet.UsedRange.Row

Range("E6").Select
ActiveCell.FormulaR1C1 = _
"=SUMPRODUCT((DuLieuXuatHang!R7C1:R5000C1=Thanhtien!R5C)*(DuLieuXuatHang!R7C4:R5000C4=Thanhtien!RC2)*DuLieuXuatHang!R7C13:R5000C13)/R2C17"

Range("E6").Select
Selection.Copy

' Lua chon vung du lieu den dong cuoi cung co du lieu de chuan bi Paste cong thuc
Dim row_i As Long
row_i = ActiveSheet.UsedRange.Rows.Count + ActiveSheet.UsedRange.Row
Range("E6", Cells(row_i - 1, "P")).Select

Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False

' Chuyen den dong cuoi cung csdl
Cells(row_i, "Q").Select

End Sub

 

[anhphuong81]

Ðề: Giúp tối ưu Marco trong Excel

Gửi file lên, đọc không hiểu nổi
P/S : Nói thêm
1/ Tránh dùng Select quá nhiều sẽ làm chậm tốc độ
2/ Dùng name động để làm hoặc dùng cách xác đinh dòng cuối có dữ liệu của bảng tính rồi thay giá trị 5000 bằng giá trị vừa xác định