Nhờ các bác giúp đỡ viết code VBA thay cho hàm VLOOKUP và SUMIFS

hakhuongdhkt · 18/6/19

Dạ em chào các bác,
Em có file excel dữ liệu khá lớn dùng 2 hàm VLOOKUP và SUMIFS để quản lý tồn kho, tuy nhiên mỗi lần thêm dữ liệu nó chạy khá là lâu. Vậy nhờ các bác giúp đỡ em viết code VBA với ạ. Em có đính kèm file mẫu để tiện theo dõi.

File của em như sau:
Sheet "Tim kiem": Em sẽ copy code vào cột A (Code) này (mỗi lần khoảng 200-300 code), em đặt sẵn công thức ở các cột So luong, Vendor... tìm kiếm từ sheet "Nhap T1", "Nhap T2" và "Nhap khac" để khi e paste code vào cột A, các thông tin còn lại sẽ tự xuất hiện. Các bác có thể thay giúp em đám công thức VLOOKUP đó bằng vba được không ạ.
Sheet "Lich su": Em sẽ điền tay dữ liệu vào bảng này, lần xuất sau sẽ điền xuống bên dưới.
Sheet "Nhap T1" "Nhap T2" "Nhap khac": Các dữ liệu từ cột A đến F em nhập tay, chỉ có cột G (Ton) em dùng công thức SUMIFS để trừ đi lượng đã xuất ở cột D (So luong) trong sheet "Lich su" ạ. Các bác giúp em thay đám công thức SUMIFS siêu nặng này với ạ.

Yêu cầu là mỗi khi em nhập thêm dữ liệu mới vào Sheet "Lich su", thông tin ở cột G (Ton) 3 sheets kia cũng tự update theo.

Dữ liệu ở 3 sheet "Nhap T1" "Nhap T2" "Nhap khac" có thể lên tới 500000 dòng.

Mong nhận được sự giúp đỡ của các bác.
Em cảm ơn nhiều ah!

ducdoom · 18/6/19

Xin chào!
Mình thấy ở T1 cũng có A1005, ở T2 cũng có A1005, vậy khi vlookup vendor tại TimKiem thì sẽ lấy kết quả như nào nhỉ?

hakhuongdhkt · 18/6/19

ducdoom đã viết:
Xin chào!
Mình thấy ở T1 cũng có A1005, ở T2 cũng có A1005, vậy khi vlookup vendor tại TimKiem thì sẽ lấy kết quả như nào nhỉ?

Nếu Vlook thì chỉ ra kết quả đầu tiên thôi bác ạ (theo thứ tự lấy giá trị ở Nhap T1 đầu tiên, sau Nhap T2 rồi đến Nhap khác), còn nếu VBA em k biết có thêm được điều kiện nếu "Tồn sau" trong sheet Timkiem âm hoặc =0 thì sẽ lấy giá trị tiếp theo đến khi >0 thì thôi có được không ạ.

hakhuongdhkt · 19/6/19

Các bác giúp em với ạ

snow25 · 19/6/19

hakhuongdhkt đã viết:
Dạ em chào các bác,
Em có file excel dữ liệu khá lớn dùng 2 hàm VLOOKUP và SUMIFS để quản lý tồn kho, tuy nhiên mỗi lần thêm dữ liệu nó chạy khá là lâu. Vậy nhờ các bác giúp đỡ em viết code VBA với ạ. Em có đính kèm file mẫu để tiện theo dõi.

File của em như sau:
Sheet "Tim kiem": Em sẽ copy code vào cột A (Code) này (mỗi lần khoảng 200-300 code), em đặt sẵn công thức ở các cột So luong, Vendor... tìm kiếm từ sheet "Nhap T1", "Nhap T2" và "Nhap khac" để khi e paste code vào cột A, các thông tin còn lại sẽ tự xuất hiện. Các bác có thể thay giúp em đám công thức VLOOKUP đó bằng vba được không ạ.
Sheet "Lich su": Em sẽ điền tay dữ liệu vào bảng này, lần xuất sau sẽ điền xuống bên dưới.
Sheet "Nhap T1" "Nhap T2" "Nhap khac": Các dữ liệu từ cột A đến F em nhập tay, chỉ có cột G (Ton) em dùng công thức SUMIFS để trừ đi lượng đã xuất ở cột D (So luong) trong sheet "Lich su" ạ. Các bác giúp em thay đám công thức SUMIFS siêu nặng này với ạ.

Yêu cầu là mỗi khi em nhập thêm dữ liệu mới vào Sheet "Lich su", thông tin ở cột G (Ton) 3 sheets kia cũng tự update theo.

Dữ liệu ở 3 sheet "Nhap T1" "Nhap T2" "Nhap khac" có thể lên tới 500000 dòng.

Mong nhận được sự giúp đỡ của các bác.
Em cảm ơn nhiều ah!

Công thức của bạn nó chạy chậm là do bạn chọn 1 triệu dòng để cho nó tính.Bạn nên chọn vùng cho phù hợp thôi nó sẽ nhanh ngay.

Bài đã được tự động gộp: 19/6/19

hakhuongdhkt đã viết:
Dạ em chào các bác,
Em có file excel dữ liệu khá lớn dùng 2 hàm VLOOKUP và SUMIFS để quản lý tồn kho, tuy nhiên mỗi lần thêm dữ liệu nó chạy khá là lâu. Vậy nhờ các bác giúp đỡ em viết code VBA với ạ. Em có đính kèm file mẫu để tiện theo dõi.

File của em như sau:
Sheet "Tim kiem": Em sẽ copy code vào cột A (Code) này (mỗi lần khoảng 200-300 code), em đặt sẵn công thức ở các cột So luong, Vendor... tìm kiếm từ sheet "Nhap T1", "Nhap T2" và "Nhap khac" để khi e paste code vào cột A, các thông tin còn lại sẽ tự xuất hiện. Các bác có thể thay giúp em đám công thức VLOOKUP đó bằng vba được không ạ.
Sheet "Lich su": Em sẽ điền tay dữ liệu vào bảng này, lần xuất sau sẽ điền xuống bên dưới.
Sheet "Nhap T1" "Nhap T2" "Nhap khac": Các dữ liệu từ cột A đến F em nhập tay, chỉ có cột G (Ton) em dùng công thức SUMIFS để trừ đi lượng đã xuất ở cột D (So luong) trong sheet "Lich su" ạ. Các bác giúp em thay đám công thức SUMIFS siêu nặng này với ạ.

Yêu cầu là mỗi khi em nhập thêm dữ liệu mới vào Sheet "Lich su", thông tin ở cột G (Ton) 3 sheets kia cũng tự update theo.

Dữ liệu ở 3 sheet "Nhap T1" "Nhap T2" "Nhap khac" có thể lên tới 500000 dòng.

Mong nhận được sự giúp đỡ của các bác.
Em cảm ơn nhiều ah!

Mình viết code ở sheets("tim kiem") nhé.Bạn thử.

Mã:

Sub timkiem()
Application.ScreenUpdating = False
    Dim arr, data, dic As Object, i As Long, j As Long, sh As Worksheet, lr As Long
    Set dic = CreateObject("scripting.dictionary")
        For Each sh In ThisWorkbook.Worksheets
            If sh.Name <> "Tim kiem" And sh.Name <> "Lich su" Then
               lr = sh.Range("A" & Rows.Count).End(xlUp).Row
               data = sh.Range("A2:G" & lr).Value
               For i = 1 To UBound(data)
                   If Not dic.exists(data(i, 1)) Then
                      dic.Add data(i, 1), Array(data(i, 2), data(i, 3), data(i, 4), data(i, 5), data(i, 6))
                   End If
                Next i
            End If
       Next
   With Sheets("tim kiem")
        lr = .Range("A" & Rows.Count).End(xlUp).Row
        If lr = 1 Then Exit Sub
        .Range("c2:H" & lr).ClearContents
        arr = .Range("A2:H" & lr).Value
        For i = 1 To UBound(arr)
            If dic.exists(arr(i, 1)) Then
               arr(i, 3) = dic.Item(arr(i, 1))(0)
               arr(i, 4) = dic.Item(arr(i, 1))(1)
               arr(i, 5) = dic.Item(arr(i, 1))(2)
               arr(i, 6) = dic.Item(arr(i, 1))(3)
               arr(i, 7) = dic.Item(arr(i, 1))(4)
               arr(i, 8) = arr(i, 7) - arr(i, 2)
           End If
       Next i
       .Range("A2:H" & lr).Value = arr
  End With
Application.ScreenUpdating = True
End Sub

hakhuongdhkt · 19/6/19

snow25 đã viết:

Công thức của bạn nó chạy chậm là do bạn chọn 1 triệu dòng để cho nó tính.Bạn nên chọn vùng cho phù hợp thôi nó sẽ nhanh ngay.

Bài đã được tự động gộp: 19/6/19

Mình viết code ở sheets("tim kiem") nhé.Bạn thử.

Mã:

Sub timkiem()
Application.ScreenUpdating = False
    Dim arr, data, dic As Object, i As Long, j As Long, sh As Worksheet, lr As Long
    Set dic = CreateObject("scripting.dictionary")
        For Each sh In ThisWorkbook.Worksheets
            If sh.Name <> "Tim kiem" And sh.Name <> "Lich su" Then
               lr = sh.Range("A" & Rows.Count).End(xlUp).Row
               data = sh.Range("A2:G" & lr).Value
               For i = 1 To UBound(data)
                   If Not dic.exists(data(i, 1)) Then
                      dic.Add data(i, 1), Array(data(i, 2), data(i, 3), data(i, 4), data(i, 5), data(i, 6))
                   End If
                Next i
            End If
       Next
   With Sheets("tim kiem")
        lr = .Range("A" & Rows.Count).End(xlUp).Row
        If lr = 1 Then Exit Sub
        .Range("c2:H" & lr).ClearContents
        arr = .Range("A2:H" & lr).Value
        For i = 1 To UBound(arr)
            If dic.exists(arr(i, 1)) Then
               arr(i, 3) = dic.Item(arr(i, 1))(0)
               arr(i, 4) = dic.Item(arr(i, 1))(1)
               arr(i, 5) = dic.Item(arr(i, 1))(2)
               arr(i, 6) = dic.Item(arr(i, 1))(3)
               arr(i, 7) = dic.Item(arr(i, 1))(4)
               arr(i, 8) = arr(i, 7) - arr(i, 2)
           End If
       Next i
       .Range("A2:H" & lr).Value = arr
  End With
Application.ScreenUpdating = True
End Sub

Wow, quá đỉnh bác ạ. File e chạy nhanh hơn nhiều rồi. Cảm ơn bác nhiều.
Còn đám SUMIFS cột G 3 sheet kia bác giúp em có cách nào thay thế nốt với ạ (có thể cho e 1 nút Run vào sheet "Lich su"). Em cảm ơn trước nhé

snow25 · 19/6/19

hakhuongdhkt đã viết:
Wow, quá đỉnh bác ạ. File e chạy nhanh hơn nhiều rồi. Cảm ơn bác nhiều.
Còn đám SUMIFS cột G 3 sheet kia bác giúp em có cách nào thay thế nốt với ạ (có thể cho e 1 nút Run vào sheet "Lich su"). Em cảm ơn trước nhé

Bạn chạy file này thử.Mình để 2 cái để chạy 1 cái là sự kiện khi nào có thay đổi ở cột D thì nó sẽ chạy code.Còn 1 cái nút Bấm cũng tính chạy code.

hakhuongdhkt · 19/6/19

snow25 đã viết:
Bạn chạy file này thử.Mình để 2 cái để chạy 1 cái là sự kiện khi nào có thay đổi ở cột D thì nó sẽ chạy code.Còn 1 cái nút Bấm cũng tính chạy code.

Em thử thấy ok lắm rồi bác. Để em thử chạy thêm nếu có sai sót nhờ bác giúp tiếp ạ.
Thật sự cảm ơn bác rất nhiều :v:

hakhuongdhkt · 19/6/19

Bác @snow25 ơi,
em thử với data thật và phát hiện ra một chỗ bị nhầm tại sub timkiem. Tại cột G ( Tồn trước) đang lấy giá trị đang trả về ở cột F (Tong so luong) của 3 sheet kia, giờ em muốn sửa cho nó lấy giá trị ở cột G (Ton) kia thì sửa ntn ạ?

snow25 · 19/6/19

hakhuongdhkt đã viết:
Bác @snow25 ơi,
em thử với data thật và phát hiện ra một chỗ bị nhầm tại sub timkiem. Tại cột G ( Tồn trước) đang lấy giá trị đang trả về ở cột F (Tong so luong) của 3 sheet kia, giờ em muốn sửa cho nó lấy giá trị ở cột G (Ton) kia thì sửa ntn ạ?

Bạn tìm dòng nào có cái thế này thì thay thế nhé.

Mã:

dic.Add data(i, 1), Array(data(i, 2), data(i, 3), data(i, 4), data(i, 5), data(i, 7))

hakhuongdhkt · 19/6/19

snow25 đã viết:
Bạn tìm dòng nào có cái thế này thì thay thế nhé.

Mã:

dic.Add data(i, 1), Array(data(i, 2), data(i, 3), data(i, 4), data(i, 5), data(i, 7))

Dạ được rồi bác ạ

Rất cảm ơn bác đã giúp đỡ em nhiệt tình.

Nhờ các bác giúp đỡ viết code VBA thay cho hàm VLOOKUP và SUMIFS

hakhuongdhkt

Gnouhk

File đính kèm

ducdoom

Thành viên chính thức

hakhuongdhkt

Gnouhk

hakhuongdhkt

Gnouhk

snow25

Thành viên gạo cội

File đính kèm

hakhuongdhkt

Gnouhk

snow25

Thành viên gạo cội

File đính kèm

hakhuongdhkt

Gnouhk

hakhuongdhkt

Gnouhk

snow25

Thành viên gạo cội

hakhuongdhkt

Gnouhk

Bài viết mới nhất

Thành viên có số lượng bài viết cao nhất tháng

Thành viên có điểm tương tác cao nhất tháng