Ðề: Tạo macro trong file excel theo dõi hàng tồn kho!!!
Nếu 1 khi bạn đã chạy & kiểm tra độ tin cậy của macro, ta bước sang bước sau; Đó là viết đầy đủ macro để nó tính toán thêm những mặt hàng nhập & xuất trong kỳ;
Macro đó có nội dung sau:
Mã nguồn PHP:
[COLOR=#000000]
Option Explicit
Sub NXTTrongKy[/COLOR][COLOR=#007700]()
[/COLOR][COLOR=#0000BB]Dim WF [/COLOR][COLOR=#007700]As [/COLOR][COLOR=#0000BB]Object[/COLOR][COLOR=#007700], [/COLOR][COLOR=#0000BB]Sh [/COLOR][COLOR=#007700]As [/COLOR][COLOR=#0000BB]Worksheet[/COLOR][COLOR=#007700], [/COLOR][COLOR=#0000BB]Cls [/COLOR][COLOR=#007700]As [/COLOR][COLOR=#0000BB]Range[/COLOR][COLOR=#007700], [/COLOR][COLOR=#0000BB]Rng [/COLOR][COLOR=#007700]As [/COLOR][COLOR=#0000BB]Range
Dim Rws [/COLOR][COLOR=#007700]As [/COLOR][COLOR=#0000BB]Long
3 Sheets[/COLOR][COLOR=#007700]([/COLOR][COLOR=#DD0000]"NXT"[/COLOR][COLOR=#007700]).[/COLOR][COLOR=#0000BB]Select
Set Sh [/COLOR][COLOR=#007700]= [/COLOR][COLOR=#0000BB]ThisWorkbook[/COLOR][COLOR=#007700].[/COLOR][COLOR=#0000BB]Worksheets[/COLOR][COLOR=#007700]([/COLOR][COLOR=#DD0000]"Nhap"[/COLOR][COLOR=#007700])
[/COLOR][COLOR=#0000BB]5 Rws [/COLOR][COLOR=#007700]= [/COLOR][COLOR=#0000BB]Sh[/COLOR][COLOR=#007700].[[/COLOR][COLOR=#0000BB]B10[/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]a10[/COLOR][COLOR=#007700]].[/COLOR][COLOR=#0000BB]Resize[/COLOR][COLOR=#007700]([/COLOR][COLOR=#0000BB]Rws[/COLOR][COLOR=#007700], [/COLOR][COLOR=#0000BB]7[/COLOR][COLOR=#007700])
[/COLOR][COLOR=#0000BB]7 Set WF [/COLOR][COLOR=#007700]= [/COLOR][COLOR=#0000BB]Application[/COLOR][COLOR=#007700].[/COLOR][COLOR=#0000BB]WorksheetFunction
[/COLOR][COLOR=#007700]For [/COLOR][COLOR=#0000BB]Each Cls In Range[/COLOR][COLOR=#007700]([[/COLOR][COLOR=#0000BB]A11[/COLOR][COLOR=#007700]], [[/COLOR][COLOR=#0000BB]A11[/COLOR][COLOR=#007700]].[/COLOR][COLOR=#0000BB]End[/COLOR][COLOR=#007700]([/COLOR][COLOR=#0000BB]xlDown[/COLOR][COLOR=#007700]))
[/COLOR][COLOR=#0000BB]9 Sh[/COLOR][COLOR=#007700].[[/COLOR][COLOR=#0000BB]ab4[/COLOR][COLOR=#007700]].[/COLOR][COLOR=#0000BB]Value [/COLOR][COLOR=#007700]= [/COLOR][COLOR=#0000BB]Cls[/COLOR][COLOR=#007700].[/COLOR][COLOR=#0000BB]Value
Sh[/COLOR][COLOR=#007700].[[/COLOR][COLOR=#0000BB]ac4[/COLOR][COLOR=#007700]].[/COLOR][COLOR=#0000BB]Value [/COLOR][COLOR=#007700]= [[/COLOR][COLOR=#0000BB]g1[/COLOR][COLOR=#007700]].[/COLOR][COLOR=#0000BB]Value
11 Sh[/COLOR][COLOR=#007700].[[/COLOR][COLOR=#0000BB]ad4[/COLOR][COLOR=#007700]].[/COLOR][COLOR=#0000BB]Value [/COLOR][COLOR=#007700]= [[/COLOR][COLOR=#0000BB]A6[/COLOR][COLOR=#007700]].[/COLOR][COLOR=#0000BB]Value
Cls[/COLOR][COLOR=#007700].[/COLOR][COLOR=#0000BB]Offset[/COLOR][COLOR=#007700](, [/COLOR][COLOR=#0000BB]3[/COLOR][COLOR=#007700]).[/COLOR][COLOR=#0000BB]Value [/COLOR][COLOR=#007700]= [/COLOR][COLOR=#0000BB]WF[/COLOR][COLOR=#007700].[/COLOR][COLOR=#0000BB]VLookup[/COLOR][COLOR=#007700]([/COLOR][COLOR=#0000BB]Cls[/COLOR][COLOR=#007700].[/COLOR][COLOR=#0000BB]Value[/COLOR][COLOR=#007700], [/COLOR][COLOR=#0000BB]Range[/COLOR][COLOR=#007700]([/COLOR][COLOR=#DD0000]"DMVT"[/COLOR][COLOR=#007700]), [/COLOR][COLOR=#0000BB]4[/COLOR][COLOR=#007700], [/COLOR][COLOR=#0000BB]False[/COLOR][COLOR=#007700]) + [/COLOR][COLOR=#0000BB]_
WF[/COLOR][COLOR=#007700].[/COLOR][COLOR=#0000BB]DSum[/COLOR][COLOR=#007700]([/COLOR][COLOR=#0000BB]Rng[/COLOR][COLOR=#007700], [/COLOR][COLOR=#0000BB]Sh[/COLOR][COLOR=#007700].[[/COLOR][COLOR=#0000BB]e10[/COLOR][COLOR=#007700]], [/COLOR][COLOR=#0000BB]Sh[/COLOR][COLOR=#007700].[[/COLOR][COLOR=#0000BB]ab1[/COLOR][COLOR=#007700]:[/COLOR][COLOR=#0000BB]ad2[/COLOR][COLOR=#007700]]) - [/COLOR][COLOR=#0000BB]WF[/COLOR][COLOR=#007700].[/COLOR][COLOR=#0000BB]DSum[/COLOR][COLOR=#007700]([/COLOR][COLOR=#0000BB]Rng[/COLOR][COLOR=#007700], [/COLOR][COLOR=#0000BB]Sh[/COLOR][COLOR=#007700].[[/COLOR][COLOR=#0000BB]f10[/COLOR][COLOR=#007700]], [/COLOR][COLOR=#0000BB]Sh[/COLOR][COLOR=#007700].[[/COLOR][COLOR=#0000BB]ab1[/COLOR][COLOR=#007700]:[/COLOR][COLOR=#0000BB]ad2[/COLOR][COLOR=#007700]])
[/COLOR][COLOR=#0000BB]13 Sh[/COLOR][COLOR=#007700].[[/COLOR][COLOR=#0000BB]ac4[/COLOR][COLOR=#007700]].[/COLOR][COLOR=#0000BB]Value [/COLOR][COLOR=#007700]= [[/COLOR][COLOR=#0000BB]A6[/COLOR][COLOR=#007700]].[/COLOR][COLOR=#0000BB]Value
Sh[/COLOR][COLOR=#007700].[[/COLOR][COLOR=#0000BB]ad4[/COLOR][COLOR=#007700]].[/COLOR][COLOR=#0000BB]Value [/COLOR][COLOR=#007700]= [[/COLOR][COLOR=#0000BB]A7[/COLOR][COLOR=#007700]].[/COLOR][COLOR=#0000BB]Value [/COLOR][COLOR=#007700]+ [/COLOR][COLOR=#0000BB]1
15 Cls[/COLOR][COLOR=#007700].[/COLOR][COLOR=#0000BB]Offset[/COLOR][COLOR=#007700](, [/COLOR][COLOR=#0000BB]4[/COLOR][COLOR=#007700]).[/COLOR][COLOR=#0000BB]Value [/COLOR][COLOR=#007700]= [/COLOR][COLOR=#0000BB]WF[/COLOR][COLOR=#007700].[/COLOR][COLOR=#0000BB]DSum[/COLOR][COLOR=#007700]([/COLOR][COLOR=#0000BB]Rng[/COLOR][COLOR=#007700], [/COLOR][COLOR=#0000BB]Sh[/COLOR][COLOR=#007700].[[/COLOR][COLOR=#0000BB]e10[/COLOR][COLOR=#007700]], [/COLOR][COLOR=#0000BB]Sh[/COLOR][COLOR=#007700].[[/COLOR][COLOR=#0000BB]ab1[/COLOR][COLOR=#007700]:[/COLOR][COLOR=#0000BB]ad2[/COLOR][COLOR=#007700]])
[/COLOR][COLOR=#0000BB]Cls[/COLOR][COLOR=#007700].[/COLOR][COLOR=#0000BB]Offset[/COLOR][COLOR=#007700](, [/COLOR][COLOR=#0000BB]5[/COLOR][COLOR=#007700]).[/COLOR][COLOR=#0000BB]Value [/COLOR][COLOR=#007700]= [/COLOR][COLOR=#0000BB]WF[/COLOR][COLOR=#007700].[/COLOR][COLOR=#0000BB]DSum[/COLOR][COLOR=#007700]([/COLOR][COLOR=#0000BB]Rng[/COLOR][COLOR=#007700], [/COLOR][COLOR=#0000BB]Sh[/COLOR][COLOR=#007700].[[/COLOR][COLOR=#0000BB]f10[/COLOR][COLOR=#007700]], [/COLOR][COLOR=#0000BB]Sh[/COLOR][COLOR=#007700].[[/COLOR][COLOR=#0000BB]ab1[/COLOR][COLOR=#007700]:[/COLOR][COLOR=#0000BB]ad2[/COLOR][COLOR=#007700]])
[/COLOR][COLOR=#0000BB]17 Next Cls
End Sub
[/COLOR]
/(/hư vậy, so với macro trong file đính kèm bên trên của mình, macro này có then các dòng lệnh từ số 13 đến dòng 16
Chúc các bạn ngày cuối tuần vui vẻ!
:boilan:
Pallet nhựa Lâm Đồng giải pháp hoàn hảo cho nhà kho Pallet nhựa Lâm Đồng là một giải pháp đa năng và hiệu quả trong việc vận chuyển và lưu trữ hàng hóa. Trong đó, pallet nhựa Lâm Đồng không chỉ đáp...
Pallet nhựa Lâm Đồng giải pháp hoàn hảo cho nhà kho