Code thay thế sumifs - File Tổng Hợp Nhập Xuất Tồn (1 người xem)

[hpkhuong]

Chào Anh/Chị GPE!

Tôi có vấn đề về cần thay thế hàm sumifs, mong các anh/chị GPE giúp đỡ.

File đính kèm là Form nhập liệu, theo dõi vật tư tồn kho. Do trong file vừa có VBA (sưu tầm & ghép nối) & vừa có công thức Excel đơn thuần. Mà Form này mục đích thiết kế cho người dùng nhập liệu, cho nên vấn đề vừa xài Vba vừa xài công thức thì thực sự không ổn. Nếu để Công thức mà không Protect Sheet thì sợ người dùng lỡ tay xóa mất công thức thì kết quả không đúng. Còn nếu Protect sheet thì lại không thể chạy đươc VBA mong muốn trong file.

Cho nên hiện tại mình đang lái theo hướng là các công thức trong file thì code hóa, nếu không viết được code thuần túy thì mình Record Macro theo công thức đơn thuần ngoài bảng tính, sau đó đưa vào vba, chạy code & Dán value trở lại, đảm bảo việc sẽ không nhìn thấy công thức ngoài bảng tính.
Đối với bảng tính nhập liệu trực tiếp thì mình căn cứ vào sự kiện Worksheet_Change để record macro theo tham chiếu tương đối thì mình làm được.

Chỉ còn sheet TONKHO trong file là bảng cố định, nên mình không biết cách record như thế nào để thay thế hàm Sumifs, khi cần lấy dữ liệu chỉ việc chạy Sub này.
Vì Sheet TONKHO là sheet data chính, tạo mã, sau đó tự động sumifs số lượng NHẬP | XUẤT | TỒN CUỐI. Cho nên số liệu phải cập nhật tức thời (bấm nút).

Cụ thể trong sheet TONKHO, 3 cột có công thức Sumifs cần các bạn giúp đỡ là Cột H, I, & cột J: (từ dòng 7, tới dòng 2000), riêng cột J là cột Tồn kho bằng Đầu kỳ (cột G) + Nhập(H) - Xuất(I)

Nhờ anh/chị GPE giúp mình code cho 3 cột H, I, J hoặc chỉ cách mình record macro với tham chiếu tương đối trong excel khi bảng tính cố định (nhưng cho fill xuống bên dưới....) (không phải cần tới sự kiện Worksheet_Change)

P/s: công thức excel thì như trong file.

Mong giúp đỡ!
Trân trọng cảm ơn!

 

[giangleloi]

Chào Anh/Chị GPE!

Tôi có vấn đề về cần thay thế hàm sumifs, mong các anh/chị GPE giúp đỡ.

Code cho cột H (Nhập) trong Sheet tồn kho. Anh đẹp trai cũng hiểu về Code chút ít vậy thử làm tương tự nha: Nhìn Code trong file mà hoa cả mắt.

Sub Nhap()
Dim Arr(), I As Long, J As Long, Kq(), Hp, Dic As Object
With Sheets("NHAP")
   Arr = .Range(.[B7], .[D1000000].End(3)).Value
End With
Set Dic = CreateObject("scripting.dictionary")
For I = 1 To UBound(Arr)
   Hp = Arr(I, 1)
   Dic(Hp) = Dic.Item(Hp) + Arr(I, 3)
Next
With Sheets("TONKHO")
   .[H7:H10000].ClearContents
   Kq = .Range(.[B7], .[B1000000].End(3)).Value
   For I = 1 To UBound(Kq)
         Hp = Kq(I, 1)
         Kq(I, 1) = Dic.Item(Hp)
   Next
   .[H7].Resize(I) = Kq
End With
Set Dic=Nothing
End Sub

 

[doveandrose]

file chứa code chi chít nhìn sơ là chóng mặt muốn xỉu rồi . công thức sumifs có vấn đề gì phải thay bằng code vậy ?

 

[giangleloi]

À quên mất nãy hông để ý đoạn này ""<="&IF($K$2<>"",$K$2,TODAY" cũng hông hiểu cho lắm. Test rồi báo lại nha anh chàng đẹp trai.

 

[doveandrose]

sợ để công thức thì chân tay tháy máy họ lại xóa đi thì xong

excel là công cụ cho người "ngoan" làm việc
nếu gặp phải "dân quậy" thì trước sau gì chả banh xác luôn cái file excel cho dù là công thức hay VBA ?

 

[giangleloi]

Cho lên top. Không thì chìm........bác giangleloi ghé thêm hộ đoạn điều kiện này nhé!
P/s: Nhìn code Dic đọc thì thấy rời rạc thật....khó hiểu nữa.......mà lợi hại thật (code ngắn gúm)!

Xíu ăn cơm xong vô tôi làm cho. Ra ngoài mất tiêu rùi. hihi

 

[giangleloi]

Cho lên top. Không thì chìm........bác giangleloi ghé thêm hộ đoạn điều kiện này nhé!
P/s: Nhìn code Dic đọc thì thấy rời rạc thật....khó hiểu nữa.......mà lợi hại thật (code ngắn gúm)!

Thử cái này xem sao (Có rời rạc là do người viết code ấy. hihi)

Sub Nhap()
Dim Arr(), I As Long, J As Long, Kq(), Hp, Dic As Object, DK As Date
  With Sheets("NHAP")
     Arr = .Range(.[B7], .[B1000000].End(3)).Resize(, 4).Value
  End With
     Set Dic = CreateObject("scripting.dictionary")
     DK = Sheet6.[K2].Value2
         For I = 1 To UBound(Arr)
             Hp = Arr(I, 1)
                If Arr(I, 4) < DK + 1 Then
                      Dic(Hp) = Dic.Item(Hp) + Arr(I, 3)
                   ElseIf DK = Empty Then
                      Dic(Hp) = Dic.Item(Hp) + Arr(I, 3)
                End If
         Next
  With Sheets("TONKHO")
       .[H7:H10000].ClearContents
       Kq = .Range(.[B7], .[B1000000].End(3)).Value
            For I = 1 To UBound(Kq)
                 Hp = Kq(I, 1)
                 Kq(I, 1) = Dic.Item(Hp)
            Next
       .[H7].Resize(I) = Kq
End With
Set Dic = Nothing
End Sub

 

[giangleloi]

Add tất cả các điều kiện & kết quả từ điều kiện đó vào biến Hp (cái này này Dic nó nhớ trong Memory của nó hả???) hay thiệt.
Hixx........học hỏi được thêm tí rùi đó!kakaka

Hp là biến nằm trong "hpkhuong" chắc là hpkhuong biết ấy chứ tui hổng có biết.

 

[phuquycnh1]

các có thê làm thêm cho mình sheet thẻ kho được ko các bác ơi.