Ðề: Lập báo cáo bán hàng (báo cáo theo yêu cầu Quản trị) từ data báng hàng tổng hợp
Bác ơi, data mới chỉ 2 tháng thôi mà đã 40.000 hàng ! cái này mà lên 12 tháng là ngồi đợi tới Tết Mông Cổ luôn
Đây là macro cải tiến xài cho Bai1, so với cách truyền thống, macro này xài mảng dữ liệu nên tốc độ giảm đi 5 lần (Còn cỡ 1 gy hơn gì đó xíu cho 2 tháng dữ liệu của bạn hiện có. )
Mã nguồn PHP:
[COLOR=#000000]
Option Explicit
Sub Bai1_2[/COLOR][COLOR=#007700]()
[/COLOR][COLOR=#0000BB]Dim Sh [/COLOR][COLOR=#007700]As [/COLOR][COLOR=#0000BB]Worksheet[/COLOR][COLOR=#007700], [/COLOR][COLOR=#0000BB]Rng [/COLOR][COLOR=#007700]As [/COLOR][COLOR=#0000BB]Range[/COLOR][COLOR=#007700], [/COLOR][COLOR=#0000BB]Cls [/COLOR][COLOR=#007700]As [/COLOR][COLOR=#0000BB]Range
Dim TT [/COLOR][COLOR=#007700]As [/COLOR][COLOR=#0000BB]Byte[/COLOR][COLOR=#007700], [/COLOR][COLOR=#0000BB]Dat [/COLOR][COLOR=#007700]As [/COLOR][COLOR=#0000BB]Date[/COLOR][COLOR=#007700], [/COLOR][COLOR=#0000BB]Tmr [/COLOR][COLOR=#007700]As [/COLOR][COLOR=#0000BB]Double
Dim Rw [/COLOR][COLOR=#007700]As [/COLOR][COLOR=#0000BB]Long[/COLOR][COLOR=#007700], [/COLOR][COLOR=#0000BB]jJ [/COLOR][COLOR=#007700]As [/COLOR][COLOR=#0000BB]Long[/COLOR][COLOR=#007700], [/COLOR][COLOR=#0000BB]Ff [/COLOR][COLOR=#007700]As [/COLOR][COLOR=#0000BB]Long[/COLOR][COLOR=#007700], [/COLOR][COLOR=#0000BB]Ww [/COLOR][COLOR=#007700]As [/COLOR][COLOR=#0000BB]Long[/COLOR][COLOR=#007700], [/COLOR][COLOR=#0000BB]Zz [/COLOR][COLOR=#007700]As [/COLOR][COLOR=#0000BB]Byte
Dim Arr[/COLOR][COLOR=#007700](), [/COLOR][COLOR=#0000BB]Mng[/COLOR][COLOR=#007700](): [/COLOR][COLOR=#0000BB]Dim sCh [/COLOR][COLOR=#007700]As [/COLOR][COLOR=#0000BB]String
Set Sh [/COLOR][COLOR=#007700]= [/COLOR][COLOR=#0000BB]ThisWorkbook[/COLOR][COLOR=#007700].[/COLOR][COLOR=#0000BB]Worksheets[/COLOR][COLOR=#007700]([/COLOR][COLOR=#DD0000]"Data"[/COLOR][COLOR=#007700])
[/COLOR][COLOR=#0000BB]Rw [/COLOR][COLOR=#007700]= [/COLOR][COLOR=#0000BB]Sh[/COLOR][COLOR=#007700].[[/COLOR][COLOR=#0000BB]b4[/COLOR][COLOR=#007700]].[/COLOR][COLOR=#0000BB]CurrentRegion[/COLOR][COLOR=#007700].[/COLOR][COLOR=#0000BB]Rows[/COLOR][COLOR=#007700].[/COLOR][COLOR=#0000BB]Count
Set Rng [/COLOR][COLOR=#007700]= [/COLOR][COLOR=#0000BB]Sh[/COLOR][COLOR=#007700].[[/COLOR][COLOR=#0000BB]a3[/COLOR][COLOR=#007700]].[/COLOR][COLOR=#0000BB]Resize[/COLOR][COLOR=#007700]([/COLOR][COLOR=#0000BB]Rw[/COLOR][COLOR=#007700], [/COLOR][COLOR=#0000BB]9[/COLOR][COLOR=#007700])
[/COLOR][COLOR=#0000BB]Sheets[/COLOR][COLOR=#007700]([/COLOR][COLOR=#DD0000]"Bai1"[/COLOR][COLOR=#007700]).[/COLOR][COLOR=#0000BB]Select[/COLOR][COLOR=#007700]: [/COLOR][COLOR=#0000BB]Tmr [/COLOR][COLOR=#007700]= [/COLOR][COLOR=#0000BB]Timer
[/COLOR][COLOR=#007700]For [/COLOR][COLOR=#0000BB]Zz [/COLOR][COLOR=#007700]= [/COLOR][COLOR=#0000BB]10 To 11
Sh[/COLOR][COLOR=#007700].[[/COLOR][COLOR=#0000BB]ab8[/COLOR][COLOR=#007700]].[/COLOR][COLOR=#0000BB]Value [/COLOR][COLOR=#007700]= [/COLOR][COLOR=#DD0000]"G"
[/COLOR][COLOR=#0000BB]Dat [/COLOR][COLOR=#007700]= [/COLOR][COLOR=#0000BB]DateSerial[/COLOR][COLOR=#007700]([[/COLOR][COLOR=#0000BB]a4[/COLOR][COLOR=#007700]].[/COLOR][COLOR=#0000BB]Value[/COLOR][COLOR=#007700], [/COLOR][COLOR=#0000BB]Zz[/COLOR][COLOR=#007700], [/COLOR][COLOR=#0000BB]1[/COLOR][COLOR=#007700])
[/COLOR][COLOR=#0000BB]Sh[/COLOR][COLOR=#007700].[[/COLOR][COLOR=#0000BB]aa8[/COLOR][COLOR=#007700]].[/COLOR][COLOR=#0000BB]Value [/COLOR][COLOR=#007700]= [/COLOR][COLOR=#0000BB]Dat
Sh[/COLOR][COLOR=#007700].[[/COLOR][COLOR=#0000BB]Ac8[/COLOR][COLOR=#007700]].[/COLOR][COLOR=#0000BB]Value [/COLOR][COLOR=#007700]= [/COLOR][COLOR=#0000BB]DateSerial[/COLOR][COLOR=#007700]([[/COLOR][COLOR=#0000BB]a4[/COLOR][COLOR=#007700]].[/COLOR][COLOR=#0000BB]Value[/COLOR][COLOR=#007700], [/COLOR][COLOR=#0000BB]1 [/COLOR][COLOR=#007700]+ [/COLOR][COLOR=#0000BB]Zz[/COLOR][COLOR=#007700], [/COLOR][COLOR=#0000BB]1[/COLOR][COLOR=#007700])
[/COLOR][COLOR=#0000BB]Rng[/COLOR][COLOR=#007700].[/COLOR][COLOR=#0000BB]AdvancedFilter Action[/COLOR][COLOR=#007700]:=[/COLOR][COLOR=#0000BB]xlFilterCopy[/COLOR][COLOR=#007700], [/COLOR][COLOR=#0000BB]CriteriaRange[/COLOR][COLOR=#007700]:= [/COLOR][COLOR=#0000BB]_
Sh[/COLOR][COLOR=#007700].[/COLOR][COLOR=#0000BB]Range[/COLOR][COLOR=#007700]([/COLOR][COLOR=#DD0000]"AA1:Ac4"[/COLOR][COLOR=#007700]), [/COLOR][COLOR=#0000BB]CopyToRange[/COLOR][COLOR=#007700]:=[/COLOR][COLOR=#0000BB]Sh[/COLOR][COLOR=#007700].[/COLOR][COLOR=#0000BB]Range[/COLOR][COLOR=#007700]([/COLOR][COLOR=#DD0000]"AA10:AD10"[/COLOR][COLOR=#007700]), [/COLOR][COLOR=#0000BB]Unique[/COLOR][COLOR=#007700]:=[/COLOR][COLOR=#0000BB]False
Ff [/COLOR][COLOR=#007700]= [[/COLOR][COLOR=#0000BB]F9999[/COLOR][COLOR=#007700]].[/COLOR][COLOR=#0000BB]End[/COLOR][COLOR=#007700]([/COLOR][COLOR=#0000BB]xlUp[/COLOR][COLOR=#007700]).[/COLOR][COLOR=#0000BB]Row
Mng [/COLOR][COLOR=#007700]= [[/COLOR][COLOR=#0000BB]f7[/COLOR][COLOR=#007700]].[/COLOR][COLOR=#0000BB]Resize[/COLOR][COLOR=#007700]([/COLOR][COLOR=#0000BB]Ff [/COLOR][COLOR=#007700]- [/COLOR][COLOR=#0000BB]7[/COLOR][COLOR=#007700]).[/COLOR][COLOR=#0000BB]Value
ReDim mKQ[/COLOR][COLOR=#007700]([/COLOR][COLOR=#0000BB]1 To Ff [/COLOR][COLOR=#007700]- [/COLOR][COLOR=#0000BB]7[/COLOR][COLOR=#007700], [/COLOR][COLOR=#0000BB]1 To 3[/COLOR][COLOR=#007700])
[/COLOR][COLOR=#0000BB]Cells[/COLOR][COLOR=#007700]([/COLOR][COLOR=#0000BB]7[/COLOR][COLOR=#007700], [/COLOR][COLOR=#0000BB]3 [/COLOR][COLOR=#007700]* [/COLOR][COLOR=#0000BB]Zz [/COLOR][COLOR=#007700]- [/COLOR][COLOR=#0000BB]22[/COLOR][COLOR=#007700]).[/COLOR][COLOR=#0000BB]Resize[/COLOR][COLOR=#007700]([/COLOR][COLOR=#0000BB]Ff [/COLOR][COLOR=#007700]- [/COLOR][COLOR=#0000BB]7[/COLOR][COLOR=#007700], [/COLOR][COLOR=#0000BB]3[/COLOR][COLOR=#007700]).[/COLOR][COLOR=#0000BB]Value [/COLOR][COLOR=#007700]= [/COLOR][COLOR=#0000BB]mKQ[/COLOR][COLOR=#007700]()
[/COLOR][COLOR=#0000BB]Arr [/COLOR][COLOR=#007700]= [/COLOR][COLOR=#0000BB]Sh[/COLOR][COLOR=#007700].[[/COLOR][COLOR=#0000BB]AB10[/COLOR][COLOR=#007700]].[/COLOR][COLOR=#0000BB]CurrentRegion[/COLOR][COLOR=#007700].[/COLOR][COLOR=#0000BB]Value
[/COLOR][COLOR=#007700]For [/COLOR][COLOR=#0000BB]jJ [/COLOR][COLOR=#007700]= [/COLOR][COLOR=#0000BB]1 To UBound[/COLOR][COLOR=#007700]([/COLOR][COLOR=#0000BB]Arr[/COLOR][COLOR=#007700](), [/COLOR][COLOR=#0000BB]1[/COLOR][COLOR=#007700])
For [/COLOR][COLOR=#0000BB]Ww [/COLOR][COLOR=#007700]= [/COLOR][COLOR=#0000BB]1 To UBound[/COLOR][COLOR=#007700]([/COLOR][COLOR=#0000BB]Mng[/COLOR][COLOR=#007700](), [/COLOR][COLOR=#0000BB]1[/COLOR][COLOR=#007700])
If [/COLOR][COLOR=#0000BB]Arr[/COLOR][COLOR=#007700]([/COLOR][COLOR=#0000BB]jJ[/COLOR][COLOR=#007700], [/COLOR][COLOR=#0000BB]2[/COLOR][COLOR=#007700]) = [/COLOR][COLOR=#0000BB]Mng[/COLOR][COLOR=#007700]([/COLOR][COLOR=#0000BB]Ww[/COLOR][COLOR=#007700], [/COLOR][COLOR=#0000BB]1[/COLOR][COLOR=#007700]) [/COLOR][COLOR=#0000BB]Then
sCh [/COLOR][COLOR=#007700]= [/COLOR][COLOR=#0000BB]Left[/COLOR][COLOR=#007700]([/COLOR][COLOR=#0000BB]Arr[/COLOR][COLOR=#007700]([/COLOR][COLOR=#0000BB]jJ[/COLOR][COLOR=#007700], [/COLOR][COLOR=#0000BB]3[/COLOR][COLOR=#007700]), [/COLOR][COLOR=#0000BB]1[/COLOR][COLOR=#007700])
[/COLOR][COLOR=#0000BB]TT [/COLOR][COLOR=#007700]= Switch([/COLOR][COLOR=#0000BB]sCh [/COLOR][COLOR=#007700]= [/COLOR][COLOR=#DD0000]"G"[/COLOR][COLOR=#007700], [/COLOR][COLOR=#0000BB]1[/COLOR][COLOR=#007700], [/COLOR][COLOR=#0000BB]sCh [/COLOR][COLOR=#007700]= [/COLOR][COLOR=#DD0000]"H"[/COLOR][COLOR=#007700], [/COLOR][COLOR=#0000BB]2[/COLOR][COLOR=#007700], [/COLOR][COLOR=#0000BB]sCh [/COLOR][COLOR=#007700]= [/COLOR][COLOR=#DD0000]"P"[/COLOR][COLOR=#007700], [/COLOR][COLOR=#0000BB]3[/COLOR][COLOR=#007700])
[/COLOR][COLOR=#0000BB]mKQ[/COLOR][COLOR=#007700]([/COLOR][COLOR=#0000BB]Ww[/COLOR][COLOR=#007700], [/COLOR][COLOR=#0000BB]TT[/COLOR][COLOR=#007700]) = [/COLOR][COLOR=#0000BB]mKQ[/COLOR][COLOR=#007700]([/COLOR][COLOR=#0000BB]Ww[/COLOR][COLOR=#007700], [/COLOR][COLOR=#0000BB]TT[/COLOR][COLOR=#007700]) + [/COLOR][COLOR=#0000BB]Arr[/COLOR][COLOR=#007700]([/COLOR][COLOR=#0000BB]jJ[/COLOR][COLOR=#007700], [/COLOR][COLOR=#0000BB]4[/COLOR][COLOR=#007700])
[/COLOR][COLOR=#0000BB]End [/COLOR][COLOR=#007700]If
[/COLOR][COLOR=#0000BB]Next Ww
Next jJ
Cells[/COLOR][COLOR=#007700]([/COLOR][COLOR=#0000BB]7[/COLOR][COLOR=#007700], [/COLOR][COLOR=#0000BB]3 [/COLOR][COLOR=#007700]* [/COLOR][COLOR=#0000BB]Zz [/COLOR][COLOR=#007700]- [/COLOR][COLOR=#0000BB]22[/COLOR][COLOR=#007700]).[/COLOR][COLOR=#0000BB]Resize[/COLOR][COLOR=#007700]([/COLOR][COLOR=#0000BB]Ff [/COLOR][COLOR=#007700]- [/COLOR][COLOR=#0000BB]7[/COLOR][COLOR=#007700], [/COLOR][COLOR=#0000BB]3[/COLOR][COLOR=#007700]).[/COLOR][COLOR=#0000BB]Value [/COLOR][COLOR=#007700]= [/COLOR][COLOR=#0000BB]mKQ[/COLOR][COLOR=#007700]()
[/COLOR][COLOR=#0000BB]Next Zz
[/COLOR][COLOR=#007700][[/COLOR][COLOR=#0000BB]s999[/COLOR][COLOR=#007700]].[/COLOR][COLOR=#0000BB]End[/COLOR][COLOR=#007700]([/COLOR][COLOR=#0000BB]xlUp[/COLOR][COLOR=#007700]).[/COLOR][COLOR=#0000BB]Offset[/COLOR][COLOR=#007700]([/COLOR][COLOR=#0000BB]1[/COLOR][COLOR=#007700]).[/COLOR][COLOR=#0000BB]Value [/COLOR][COLOR=#007700]= [/COLOR][COLOR=#0000BB]Timer [/COLOR][COLOR=#007700]- [/COLOR][COLOR=#0000BB]Tmr
End Sub
[/COLOR]
Bồn nước là một trong những thiết bị không thể thiếu trong mỗi gia đình và doanh nghiệp, đặc biệt là khi nhu cầu sử dụng nước ngày càng tăng cao. Trong số các loại bồn nước hiện nay, bồn nước inox...
Bồn Nước Inox Sơn Hà: Lựa Chọn Thông Minh Cho Gia Đình và Doanh Nghiệp