Giúp code: Lọc theo tháng & Mã khách hàng, rồi tự cấn trừ số tiền công nợ!

Hong.Van · 27/3/13

Em chào Thầy cô & anh chị!
Đây là bài toán về Lọc theo tháng & Mã khách hàng, rồi tự cấn trừ số tiền công nợ. Em giải thích như sau:
Trong 1 File có nhiều Sheet CNT01, CNT02, ... (CNT01 nghĩa là công nợ tháng 01)
Em muốn code cho kết qủa tại Sheet ChiTiet_CN

1/Tại Sheet ChiTiet_CN:
a/ Khi em chọn tháng tại cell Q4 là CNT01 thì nó nhảy qua Sheet CNT01, nếu chọn CNT02 thì nó nhảy qua Sheet CNT02 để dò tìm
b/ Khi em chọn Mã Khách hàng tại cell Q5: thì nó sẽ lọc ra Mã KH của Sheet cần dò tìm tại cell Q4
c/ Sau khi chọn Tháng & Mã KH xong: thì code sẽ tự cấn trừ "Tiền nợ" (Cột Q) và "Tiền trả" (cột R) dựa vào cùng Mã là "Hóa đơn" (cột L)

Trong File em làm các ví dụ như:
Sheet VD_CNT01_B001: là ví dụ cho kết quả sau khi Chọn CNT01 và B001.....
Em giải thích kết qủa của VD_CNT01_B001 như sau:
Khi lọc B001 của Sheet CNT01 thì ta sẽ có kết quả là các dòng có chữ màu xanh

Dòng thứ 11: có hóa đơn 099 và Tiền nợ là 5.000.000
Dòng thứ 19: có hóa đơn 099 và Tiền Trả là 5.000.000
Như vậy Dòng thứ 11 và 19 sẽ tự cấn trừ và không cần thể hiện nữa (Vì số tiền bằng nhau = 5.000.000)

Dòng thứ 12: có hóa đơn 001 và Tiền nợ là 1.200.000
Dòng thứ 20: có hóa đơn 001 và Tiền Trả là 1.200.000
Như vậy Dòng thứ 12 và 20 sẽ tự cấn trừ và không cần thể hiện nữa (Vì số tiền bằng nhau = 1.200.000)

Dòng thứ 15: có hóa đơn 004 và Tiền nợ là 1.800.000
Dòng thứ 21: có hóa đơn 004 và Tiền Trả là 1.600.000
Ở đây có sự chênh lệch giữa Tiền nợ và Tiền trả là 200.000
Như vậy Dòng thứ 12 và 20 sẽ tự cấn trừ và thể hiện nội dung của dòng 15 và Tiền nợ là 200.000

Dòng thứ 18 :có hóa đơn 005 và Tiền nợ là 2.800.000 (Ở đây khách hàng chưa trả nên vẫn thể hiện nguyên cả dòng này)

Các ví dụ : VD_CNT01_B002, VD_CNT01_B003, VD_CNT01_B004, VD_CNT01_BKVL thì cách thức giống như trên
-------------
Riệng ví dụ: VD_CNT01_131: số 131 ở đây là đại diện cho tất cả các Mã khách hàng, nên nó sẽ tổng hợp cấn trừ theo từng mã KH và hóa đơn

Em xin cảm ơn !
p/s: Đây là bài toán Công nợ, nếu Thầy cô & anh chị chưa hiểu chỗ nào thì xin cứ phản hồi

SA_DQ · 27/3/13

Kiểm theo file xem sao.

Còn trang tính cuối chưa thể làm, vì chưa thấu đáo

Hong.Van · 27/3/13

1/ Tại Sheet ChiTiet_CN em chọn Mã KH (cell G5 ) thấy nó không chạy, em chọn lại cell G4 thì thấy nó báo lỗi
"Subscript out of Range" ở dòng

Mã:

For Each Cls In Range([Ba11], [Ba11].End(xlDown).Offset(1))

2/

SA_DQ đã viết:
Còn trang tính cuối chưa thể làm, vì chưa thấu đáo

Em giải thích thêm khi chọn mã KH là "131"
Mã 131 là mã chung cho toàn bộ của các Mã KH, cách em làm là làm từng mã B001, B002, B003, B004, BKVL ... rồi copy và dán vào
Mong sự giúp đỡ của Các Thầy & các bạn
Em cảm ơn!

ChanhTQ@ · 27/3/13

[thongbao]1/ Tại Sheet ChiTiet_CN em chọn Mã KH (cell G5 ) thấy nó không chạy, em chọn lại cell G4 thì thấy nó báo lỗi
"Subscript out of Range" ở dòng

PHP:

For Each Cls In Range([Ba11], [Ba11].End(xlDown).Offset(1))

[/thongbao]
[G5] bạn đã chọn mã hàng nào?

Sau đó [G4] chọn gì cho nó?!

E rằng mã hàng đó không có trong giao dịch chăng?

Hong.Van · 27/3/13

ChanhTQ@ đã viết:
[thongbao]1/ Tại Sheet ChiTiet_CN em chọn Mã KH (cell G5 ) thấy nó không chạy, em chọn lại cell G4 thì thấy nó báo lỗi
"Subscript out of Range" ở dòng

PHP:

For Each Cls In Range([Ba11], [Ba11].End(xlDown).Offset(1))

[G5] bạn đã chọn mã hàng nào?

Sau đó [G4] chọn gì cho nó?!

E rằng mã hàng đó không có trong giao dịch chăng?

Dĩ nhiên phải chọn mã đang tồn tại
VD: cell G4 chọn CNT01 và G5 chọn B001
-----------
Không biết ai tải File về có bị như mình không?

ChanhTQ@ · 27/3/13

Ở trang 'ChiTiet_...', tại ô trống nào đó, bạn lập công thức này xem sao:

=AD2

SA_DQ · 28/3/13

Nhờ thấm nhuần tư tưởng bẩy lỗi trong "VBA trong excel, cải thiện & tăng tốc"

Bạn xem file này bớt lỗi nè,

}}}}}

Hong.Van · 28/3/13

SA_DQ đã viết:
Bạn xem file này bớt lỗi nè,

Em cảm ơn Thầy
Em nhờ Thầy giúp em sửa code thêm 2 điểm sau:
1/ Khi chọn Mã KH là 131 thì toàn bộ các mã từ Cell E1 trở xuống đểu thể hiện là 131, Em muốn nó thể hiện chi tiết các mã khách hàng (Xin xem Sheet VD_CNT01_131, em có ghi chú)

2/ Sau khi cấn trừ tiền, Nếu còn tồn tại số tiền nào thì lấy Ngày Nợ hoặc Ngày Trả và nội dung của ngày đó
Em có làm thêm ví dụ CNT03 của Mã B007. Ở đây khách hàng trả tiền dư so với nợ phải trả là 500.000, như vậy Kết quả sau khi chạy code thì fải thể hiện ngày trả là 31/03/13 và nội dung là "Cty B007 chuyển khỏan thanh toán" (Xin xem Sheet VD_CNT03_B007)
Em cảm ơn!

SA_DQ · 28/3/13

Bạn lấy macro này chép đè lên cái cũ xem đúng í chưa nha:

PHP:

Sub GPE()
 Dim NgNo, NgTra As Variant, TNo As Double, TTra As Double
 Dim Cls As Range, HDon, DGDu As String
 On Error GoTo LCT
    [AB11].CurrentRegion.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _
        "AD1:AD2"), CopyToRange:=Range("BA10:BH10")
    [Ba10].CurrentRegion.Select
    Selection.Sort Key1:=Range("bA11"), Order1:=xlAscending, Key2:=Range( _
        "bB11"), Order2:=xlAscending, Key3:=Range("bC11"), Order3:=xlAscending, _
        Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:= _
        xlTopToBottom, DataOption1:=xlSortTextAsNumbers
    [b10].Select
9    For Each Cls In Range([Ba11], [Ba999].End(xlUp).Offset(1))
        If Cls.Value <> HDon Then
            If Cls.Row > 11 And TNo <> TTra Then        'Ghi Len Báo Cáo:'
                With [B999].End(xlUp).Offset(1)
                    .Value = HDon:                      .Offset(, 3).Value = [ad2].Value
8                    If TNo > TTra Then
                        .Offset(, 4).Value = DGiai:     .Offset(, 1).Value = NgNo
                        .Offset(, 5).Value = TNo - TTra
                    Else
                        .Offset(, 4).Value = DGDu:      .Offset(, 2) = NgTra
                        .Offset(, 6) = TTra - TNo
7                   End If
                End With
            End If
'Ghi Vo Bién:'
            HDon = Cls.Value:                           NgNo = Cls.Offset(, 1).Value
            TNo = Cls.Offset(, 5).Value:                TTra = Cls.Offset(, 6).Value
            DGiai = Cls.Offset(, 4).Value
6           If TTra > 0 Then
                NgTra = Cls.Offset(, 2).Value:          DGDu = Cls.Offset(, 4).Value
            End If
        Else
5           NgTra = Cls.Offset(, 2).Value:              DGDu = Cls.Offset(, 4).Value
            TNo = TNo + Cls.Offset(, 5).Value:          TTra = TTra + Cls.Offset(, 6).Value
        End If
    Next Cls
KhongLoi:
    Exit Sub
LCT:
    Application.ScreenUpdating = True
    MsgBox Error, , Erl
    Resume KhongLoi
End Sub

(Lí ra câu 1 bạn có thể tự làm & hi vọng khỏi gởi file)

Hong.Van · 29/3/13

Kết quả rất tốt, em cảm ơn
Bây giờ em nâng cao yêu cầu như sau:
Trong tháng 1 (CNT01) trong cùng 1 ngày (05/01/2013) Khách hàng Mã B001 trả nợ cho các hóa đơn 099,001,004. Bình thường em fải tách thành 03 dòng cho 03 hóa đơn này, nhưng bây giờ gom 03 dòng thành 01 dòng. Như vậy code có thực hiện được không?
Em nghỉ trường hợp này khó! Nếu code không thể thực hiện được thì vui lòng thông báo giúp em
Em cảm ơn!

Hong.Van · 1/4/13

Hong.Van đã viết:
Kết quả rất tốt, em cảm ơn
Bây giờ em nâng cao yêu cầu như sau:
Trong tháng 1 (CNT01) trong cùng 1 ngày (05/01/2013) Khách hàng Mã B001 trả nợ cho các hóa đơn 099,001,004. Bình thường em fải tách thành 03 dòng cho 03 hóa đơn này, nhưng bây giờ gom 03 dòng thành 01 dòng. Như vậy code có thực hiện được không?
Em nghỉ trường hợp này khó! Nếu code không thể thực hiện được thì vui lòng thông báo giúp em
Em cảm ơn!

Em không biết bài trên có giải được không?
Nếu được, xin vui lòng giúp em!

SA_DQ · 1/4/13

Bây giờ em nâng cao yêu cầu như sau:
Trong tháng 1 (CNT01) trong cùng 1 ngày (05/01/2013) Khách hàng Mã B001 trả nợ cho các hóa đơn 099,001,004. Bình thường em fải tách thành 03 dòng cho 03 hóa đơn này, nhưng bây giờ gom 03 dòng thành 01 dòng. Như vậy code có thực hiện được không?
Em nghỉ trường hợp này khó! Nếu code không thể thực hiện được thì vui lòng thông báo giúp em
Em cảm ơn!

Đây là việc khó;
& càng khó thêm khi mã hóa đơn của bạn lại là những con số không đứng đầu (Trước) trong mã.
(Điều này có thể fải lựa 1 trong 2 hướng sau:

Mã hóa đơn là số thì mã đầu tiên fải là 100,101,. . . .(Hay 1000,1001,. . . )
Mã gồm 1 chử cái đầu, như G000, G001; GPE0, GPE1,. . . .GPEZ; . . .

Hãy chọn 1 fương án từ đó & bình tĩnh chờ 1 thời gian

(Xin lỗi vì hôm nay mới thấy bài của bạn!)

Hong.Van · 1/4/13

HYen17 đã viết:
Đây là việc khó;
& càng khó thêm khi mã hóa đơn của bạn lại là những con số không đứng đầu (Trước) trong mã.
(Điều này có thể fải lựa 1 trong 2 hướng sau:

Mã hóa đơn là số thì mã đầu tiên fải là 100,101,. . . .(Hay 1000,1001,. . . )

Mã gồm 1 chử cái đầu, như G000, G001; GPE0, GPE1,. . . .GPEZ; . . .

Hãy chọn 1 fương án từ đó & bình tĩnh chờ 1 thời gian

Hóa đơn bao gồm bảy chữ số từ 0000001 đến 9999999, như vậy hóa đơn là những con số do đó mình không thể mã hóa nó được!
Nếu có fương án khác thì các Thầy cô & anh chị giúp em, trường hợp khó quá thì thôi vậy
Em cảm ơn!

SA_DQ · 1/4/13

Dù sao chăng nữa, mình vẫn khuyên bạn rằng trong excel, nếu nhất thiết fải lấy mã hóa đơn gồm 7 kí số, thì nên bắt đầu hóa đơn số 1.000.000

ChanhTQ@ · 2/4/13

Sau nữa ngày suy tư, mình nghĩ ra 1 con đường vòng vo, như sau

Đối chiếu với nội dung macro

Mã:

Sub GPE()
' . . . . . '
End Sub

Ta thấy, sau khi thực hiện câu lệnh dài nhất của macro này, chúng ta thu được kết quả trên trang tính như sau:

| BA | BB | BC | BD | BE | BF | BG | BH 10 |Hóa đơn|Ngày nợ|Ngày trả|Mã KH|Diễn giải|Tiền nợ,10^6|Tiền trả, 10^6|Ghi chú
11 |1000001|1/1/2013||B001|Bán hàng cho CTi B001|1.2||
12 |1000004|1/3/2013||B001|Bán hàng cho CTy B001|1.8||
13 |1000005|1/5/2013||B001|Bán hàng cho CTi B001|2.8||
14 |1000099|12/31/2012||B001|Bán hàng cho CTi B001||
15 |1000099,1000001,1000004||1/5/2013|B001|CTy B001 chuyển khoản||8.0|

Ta bổ sung macro để tách dòng 15 của bảng trên làm 3 dòng;
Hay nói cách khác: dò theo bảng, dòng nào trùng với mã chứa trong trường [Hóa đơn] của dòng 15 thì xóa đi.

Chuyện này chúng ta có thể làm được; Bạn hãy thử sức xem sao.

Chúc thành công.

Hong.Van · 2/4/13

HYen17 đã viết:
Dù sao chăng nữa, mình vẫn khuyên bạn rằng trong excel, nếu nhất thiết fải lấy mã hóa đơn gồm 7 kí số, thì nên bắt đầu hóa đơn số 1.000.000

Hóa đơn là những con số sê ri nên chúng ta không thể thay đổi được!
Ví dụ: Mình cung cấp cho khách hàng hóa đơn số 789, Khi đối chiếu công nợ mà chuyển 789 thành 1000789 thì họ nói tôi nợ hóa đơn 789 chứ không phải 1000789 !? hoặc sếp hỏi hoặc cơ quan thuế hỏi?
------------------------------------------

ChanhTQ@ đã viết:
Đối chiếu với nội dung macro

Mã:

Sub GPE() ' . . . . . ' End Sub

Ta thấy, sau khi thực hiện câu lệnh dài nhất của macro này, chúng ta thu được kết quả trên trang tính như sau:

| BA | BB | BC | BD | BE | BF | BG | BH 10 |Hóa đơn|Ngày nợ|Ngày trả|Mã KH|Diễn giải|Tiền nợ,10^6|Tiền trả, 10^6|Ghi chú
11 |1000001|1/1/2013||B001|Bán hàng cho CTi B001|1.2||
12 |1000004|1/3/2013||B001|Bán hàng cho CTy B001|1.8||
13 |1000005|1/5/2013||B001|Bán hàng cho CTi B001|2.8||
14 |1000099|12/31/2012||B001|Bán hàng cho CTi B001||
15 |1000099,1000001,1000004||1/5/2013|B001|CTy B001 chuyển khoản||8.0|

Ta bổ sung macro để tách dòng 15 của bảng trên làm 3 dòng;
Hay nói cách khác: dò theo bảng, dòng nào trùng với mã chứa trong trường [Hóa đơn] của dòng 15 thì xóa đi.

Chuyện này chúng ta có thể làm được; Bạn hãy thử sức xem sao.

Chúc thành công.

Hì thực tế là không thể làm được nên mới làm fiền Thầy cô & anh chị!
Cho em có ý kiến: nếu những hóa đơn 1000099,1000001,1000004 thì chúng ta tách được. Còn các hóa đơn 789,1023,123456 thì sao không tách được nhỉ? Vì chúng cách nhau bằng dấu phẩy mà !
Em cảm ơn!

SA_DQ · 2/4/13

[thongbao](1) Hóa đơn là những con số sê ri nên chúng ta không thể thay đổi được!
(3) Hì thực tế là không thể làm được nên mới làm fiền Thầy cô & anh chị!
Cho em có ý kiến: (2) Nếu những hóa đơn 1000099,1000001,1000004 thì chúng ta tách được. Còn các hóa đơn 789,1023,123456 thì sao không tách được nhỉ? Vì chúng cách nhau bằng dấu phẩy mà !
[/thongbao]

(1) Để khắc fục thì bạn làm thêm 1 trường bằng cách cộng số hóa đơn với 1 triệu; & luôn làm việc với trường này; nhưng khi ra bảng báo cáo thì dùng trường [Hóa dơn]

(2) Việc này có quan hệ việc ký tự đại diện "*" & "?"; Bạn có thể chưa gặp, giống như "Chưa thấy quan tài, chưa đỗ lệ" vậy mà!

(3) Bạn tạo 1 file như bảng bài trên & nhờ GPE.COM xem thử; Khối người sẽ giúp bạn ấy chứ!
Nhưng trước tiên hãy cùng thử sức đi;

Thân!

vodoi2x · 18/4/13

thử phương án mới xem sao

Hong.Van đã viết:
Kết quả rất tốt, em cảm ơn
Bây giờ em nâng cao yêu cầu như sau:
Trong tháng 1 (CNT01) trong cùng 1 ngày (05/01/2013) Khách hàng Mã B001 trả nợ cho các hóa đơn 099,001,004. Bình thường em fải tách thành 03 dòng cho 03 hóa đơn này, nhưng bây giờ gom 03 dòng thành 01 dòng. Như vậy code có thực hiện được không?
Em nghỉ trường hợp này khó! Nếu code không thể thực hiện được thì vui lòng thông báo giúp em
Em cảm ơn!

Thử thêm lựa chọn khác này xem sao,

Vì diễn giải khá dài và loằng văn ngoằng, nên không chắc đúng với ý đồ mong muốn của bạn chưa, (ngày và tiêu đề diễn giải ở bảng kết quả rất là mông lung - vì khó hợp lý tốt, vì có nhiều ngày đành phải lấy ngày bán/trả mới nhất)

Và không hiểu số liệu để test, vậy bạn tự test các trường hợp, lưu ý:

- Mã số hóa đơn chỉ chứa các con số nhé, không chứa chữ cái (để text như hiện thời cũng được

- khi nhóm mã hóa đơn thì phải phân cách bằng dấu "," đó

- bấm Ctrl+F3 để thấy các name, đặt để định vị dữ liệu của các sheets - chú ý các tên cho cell định vị ở các sheets -CNT01 , CNT02 ... đặt đúng theo tên được chọn ở phần kết quả (ô chọn tháng)

down file kèm về test

Hong.Van · 19/4/13

vodoi2x đã viết:
Thử thêm lựa chọn khác này xem sao,

Vì diễn giải khá dài và loằng văn ngoằng, nên không chắc đúng với ý đồ mong muốn của bạn chưa, (ngày và tiêu đề diễn giải ở bảng kết quả rất là mông lung - vì khó hợp lý tốt, vì có nhiều ngày đành phải lấy ngày bán/trả mới nhất)

Và không hiểu số liệu để test, vậy bạn tự test các trường hợp, lưu ý:

- Mã số hóa đơn chỉ chứa các con số nhé, không chứa chữ cái (để text như hiện thời cũng được

- khi nhóm mã hóa đơn thì phải phân cách bằng dấu "," đó

- bấm Ctrl+F3 để thấy các name, đặt để định vị dữ liệu của các sheets - chú ý các tên cho cell định vị ở các sheets -CNT01 , CNT02 ... đặt đúng theo tên được chọn ở phần kết quả (ô chọn tháng)

down file kèm về test

Em cảm ơn sự hỗ trợ của anh!
Bây giờ có trường hợp như thế này: Trả nợ thừa hay thiếu so với tổng tiền phải thanh toán (Thực tế chuyện này xảy ra thường xuyên)

Em ví dụ: Trong Sheet CNT01, Mã KH là B001. Tổng nợ phải thanh toán của 3 hóa đơn 099,001,004 là 8.000.000 đồng, nhưng khi thực tế thanh tóan thì có thể khách hàng B001 trả 9.000.000 (trả dư 1.000.000) hoặc 7.500.000 (trả thiếu 500.000), trong trường hợp này thì code không chạy. Như vậy trong các trường hợp trên có cách nào xử lý được không?
Nhờ sự giúp đỡ của các Thầy cô & anh chị! Em cảm ơn.

vodoi2x · 19/4/13

Hong.Van đã viết:
Em cảm ơn sự hỗ trợ của anh!
Bây giờ có trường hợp như thế này: Trả nợ thừa hay thiếu so với tổng tiền phải thanh toán (Thực tế chuyện này xảy ra thường xuyên)

Em ví dụ: Trong Sheet CNT01, Mã KH là B001. Tổng nợ phải thanh toán của 3 hóa đơn 099,001,004 là 8.000.000 đồng, nhưng khi thực tế thanh tóan thì có thể khách hàng B001 trả 9.000.000 (trả dư 1.000.000) hoặc 7.500.000 (trả thiếu 500.000), trong trường hợp này thì code không chạy. Như vậy trong các trường hợp trên có cách nào xử lý được không?
Nhờ sự giúp đỡ của các Thầy cô & anh chị! Em cảm ơn.

banj thử nhập chưa, trường hợp thiếu (7500000) code vẫn chạy và đẩy tiền thiếu cho mã Hoá đơn cuối (004) đó

trường hợp thừa, Vấn đề khi đó, tiền thừa đẩy cho mã Hoá đơn nào thui??

Giúp code: Lọc theo tháng & Mã khách hàng, rồi tự cấn trừ số tiền công nợ!

Busy

File đính kèm

/(hông là gì!

File đính kèm

Busy

0901452không62

Busy

0901452không62

/(hông là gì!

File đính kèm

Busy

File đính kèm

/(hông là gì!

Busy

File đính kèm

Busy

/(hông là gì!

Busy

/(hông là gì!

0901452không62

Busy

/(hông là gì!

Nothing & Nothing

File đính kèm

Busy

Nothing & Nothing

Facebook

Group