Cải tiến hàm Sumif trong VBA (1 người xem)

  • Thread starter Thread starter quyenpv
  • Ngày gửi Ngày gửi
Liên hệ QC

Người dùng đang xem chủ đề này

Đang trực tuyến: 1 (Thành viên: 0, Khách: 1)
quyenpv

quyenpv

Thu nhặt kiến thức
Tham gia
5/1/13
Bài viết
729
Được thích
97
Giới tính
Nam
Nghề nghiệp
Decode cuộc đời!
Dear anh chị!
Hiện em đang dùng Code hàm Sumif trong VBA kiểu thủ công này, nhờ anh chị hỗ trợ rút gọn giúp đoạn code trên em có cho vào vòng for nhưng bị lỗi phần biến W7,W8, W9, W10 nó chạy không chính xác. Có cách nào cố định biến đó được không ạ
Em cám ơn

Mã: 
Sub Sumif_Help()
Dim LastRow
Sheets("BQT-VTU").Select
With Sheets("BQT-VTU")
    'Lay Dong cuoi cung cua BQT_VTU
    LastRow = Sheets("BQT-VTU").Cells(Rows.Count, "T").End(xlUp).Row


    'Tinh Tong tien vat tu xuat, thi cong, thu hoi, mat mat
    .Range("H" & LastRow + 1).Formula = "=SUBTOTAL(9,H11:H" & LastRow & ")"
    .Range("K" & LastRow + 1).Formula = "=SUBTOTAL(9,K11:K" & LastRow & ")"
    .Range("N" & LastRow + 1).Formula = "=SUBTOTAL(9,N11:N" & LastRow & ")"
    .Range("P" & LastRow + 1).Formula = "=SUBTOTAL(9,P11:P" & LastRow & ")"
    .Range("R" & LastRow + 1).Formula = "=SUBTOTAL(9,R11:R" & LastRow & ")"

    'Phan tich tong tien cac loai kho VTU03
    .Range("H" & LastRow + 2).Formula = "=SUMIF(V11:V" & LastRow & ",W7,H11:H" & LastRow & ")"
    .Range("K" & LastRow + 2).Formula = "=SUMIF(V11:V" & LastRow & ",W7,K11:K" & LastRow & ")"
    .Range("N" & LastRow + 2).Formula = "=SUMIF(V11:V" & LastRow & ",W7,N11:N" & LastRow & ")"
    .Range("P" & LastRow + 2).Formula = "=SUMIF(V11:V" & LastRow & ",W7,P11:P" & LastRow & ")"
    .Range("R" & LastRow + 2).Formula = "=SUMIF(V11:V" & LastRow & ",W7,R11:R" & LastRow & ")"
    'Phan tich tong tien cac loai kho VTU03
    .Range("H" & LastRow + 3).Formula = "=SUMIF(V11:V" & LastRow & ",W8,H11:H" & LastRow & ")"
    .Range("K" & LastRow + 3).Formula = "=SUMIF(V11:V" & LastRow & ",W8,K11:K" & LastRow & ")"
    .Range("N" & LastRow + 3).Formula = "=SUMIF(V11:V" & LastRow & ",W8,N11:N" & LastRow & ")"
    .Range("P" & LastRow + 3).Formula = "=SUMIF(V11:V" & LastRow & ",W8,P11:P" & LastRow & ")"
    .Range("R" & LastRow + 3).Formula = "=SUMIF(V11:V" & LastRow & ",W8,R11:R" & LastRow & ")"
    'Phan tich tong tien cac loai kho VTU04
    .Range("H" & LastRow + 4).Formula = "=SUMIF(V11:V" & LastRow & ",W9,H11:H" & LastRow & ")"
    .Range("K" & LastRow + 4).Formula = "=SUMIF(V11:V" & LastRow & ",W9,K11:K" & LastRow & ")"
    .Range("N" & LastRow + 4).Formula = "=SUMIF(V11:V" & LastRow & ",W9,N11:N" & LastRow & ")"
    .Range("P" & LastRow + 4).Formula = "=SUMIF(V11:V" & LastRow & ",W9,P11:P" & LastRow & ")"
    .Range("R" & LastRow + 4).Formula = "=SUMIF(V11:V" & LastRow & ",W9,R11:R" & LastRow & ")"
    'Phan tich tong tien cac loai kho VTU-DA
    .Range("H" & LastRow + 5).Formula = "=SUMIF(V11:V" & LastRow & ",W10,H11:H" & LastRow & ")"
    .Range("K" & LastRow + 5).Formula = "=SUMIF(V11:V" & LastRow & ",W10,K11:K" & LastRow & ")"
    .Range("N" & LastRow + 5).Formula = "=SUMIF(V11:V" & LastRow & ",W10,N11:N" & LastRow & ")"
    .Range("P" & LastRow + 5).Formula = "=SUMIF(V11:V" & LastRow & ",W10,P11:P" & LastRow & ")"
    .Range("R" & LastRow + 5).Formula = "=SUMIF(V11:V" & LastRow & ",W10,R11:R" & LastRow & ")"
    'xxxxxx
    .Range("D" & LastRow + 6).FormulaR1C1 = "=DocSoUni(R[-5]C[7])"
    
    .Rows("11:" & LastRow - 1 & "").RowHeight = 35
    .Rows("" & LastRow + 1 & ":" & LastRow + 10 & "").RowHeight = 23
    
    .PageSetup.PrintArea = "$A$1:$S" & LastRow + 10 & ""
    
End With

End Sub
 

File đính kèm

Các anh ơi cho em hỏi làm sao thay được điều kiện từ W7-W10 với ạ

Mã: 
For i = 2 To 5
    'Phan tich tong tien cac loai kho VTU03
    .Range("H" & LastRow + i).Formula = "=SUMIF(V11:V" & LastRow & ",W7,H11:H" & LastRow & ")"
    .Range("K" & LastRow + i).Formula = "=SUMIF(V11:V" & LastRow & ",W7,K11:K" & LastRow & ")"
    .Range("N" & LastRow + i).Formula = "=SUMIF(V11:V" & LastRow & ",W7,N11:N" & LastRow & ")"
    .Range("P" & LastRow + i).Formula = "=SUMIF(V11:V" & LastRow & ",W7,P11:P" & LastRow & ")"
    .Range("R" & LastRow + i).Formula = "=SUMIF(V11:V" & LastRow & ",W7,R11:R" & LastRow & ")"

Next i
 
Help me
 
quyenpv đã viết:
Dear anh chị!
Hiện em đang dùng Code hàm Sumif trong VBA kiểu thủ công này, nhờ anh chị hỗ trợ rút gọn giúp đoạn code trên em có cho vào vòng for nhưng bị lỗi phần biến W7,W8, W9, W10 nó chạy không chính xác. Có cách nào cố định biến đó được không ạ
Em cám ơn

Mã: 
Sub Sumif_Help()
Dim LastRow
Sheets("BQT-VTU").Select
With Sheets("BQT-VTU")
    'Lay Dong cuoi cung cua BQT_VTU
    LastRow = Sheets("BQT-VTU").Cells(Rows.Count, "T").End(xlUp).Row


    'Tinh Tong tien vat tu xuat, thi cong, thu hoi, mat mat
    .Range("H" & LastRow + 1).Formula = "=SUBTOTAL(9,H11:H" & LastRow & ")"
    .Range("K" & LastRow + 1).Formula = "=SUBTOTAL(9,K11:K" & LastRow & ")"
    .Range("N" & LastRow + 1).Formula = "=SUBTOTAL(9,N11:N" & LastRow & ")"
    .Range("P" & LastRow + 1).Formula = "=SUBTOTAL(9,P11:P" & LastRow & ")"
    .Range("R" & LastRow + 1).Formula = "=SUBTOTAL(9,R11:R" & LastRow & ")"

    'Phan tich tong tien cac loai kho VTU03
    .Range("H" & LastRow + 2).Formula = "=SUMIF(V11:V" & LastRow & ",W7,H11:H" & LastRow & ")"
    .Range("K" & LastRow + 2).Formula = "=SUMIF(V11:V" & LastRow & ",W7,K11:K" & LastRow & ")"
    .Range("N" & LastRow + 2).Formula = "=SUMIF(V11:V" & LastRow & ",W7,N11:N" & LastRow & ")"
    .Range("P" & LastRow + 2).Formula = "=SUMIF(V11:V" & LastRow & ",W7,P11:P" & LastRow & ")"
    .Range("R" & LastRow + 2).Formula = "=SUMIF(V11:V" & LastRow & ",W7,R11:R" & LastRow & ")"
    'Phan tich tong tien cac loai kho VTU03
    .Range("H" & LastRow + 3).Formula = "=SUMIF(V11:V" & LastRow & ",W8,H11:H" & LastRow & ")"
    .Range("K" & LastRow + 3).Formula = "=SUMIF(V11:V" & LastRow & ",W8,K11:K" & LastRow & ")"
    .Range("N" & LastRow + 3).Formula = "=SUMIF(V11:V" & LastRow & ",W8,N11:N" & LastRow & ")"
    .Range("P" & LastRow + 3).Formula = "=SUMIF(V11:V" & LastRow & ",W8,P11:P" & LastRow & ")"
    .Range("R" & LastRow + 3).Formula = "=SUMIF(V11:V" & LastRow & ",W8,R11:R" & LastRow & ")"
    'Phan tich tong tien cac loai kho VTU04
    .Range("H" & LastRow + 4).Formula = "=SUMIF(V11:V" & LastRow & ",W9,H11:H" & LastRow & ")"
    .Range("K" & LastRow + 4).Formula = "=SUMIF(V11:V" & LastRow & ",W9,K11:K" & LastRow & ")"
    .Range("N" & LastRow + 4).Formula = "=SUMIF(V11:V" & LastRow & ",W9,N11:N" & LastRow & ")"
    .Range("P" & LastRow + 4).Formula = "=SUMIF(V11:V" & LastRow & ",W9,P11:P" & LastRow & ")"
    .Range("R" & LastRow + 4).Formula = "=SUMIF(V11:V" & LastRow & ",W9,R11:R" & LastRow & ")"
    'Phan tich tong tien cac loai kho VTU-DA
    .Range("H" & LastRow + 5).Formula = "=SUMIF(V11:V" & LastRow & ",W10,H11:H" & LastRow & ")"
    .Range("K" & LastRow + 5).Formula = "=SUMIF(V11:V" & LastRow & ",W10,K11:K" & LastRow & ")"
    .Range("N" & LastRow + 5).Formula = "=SUMIF(V11:V" & LastRow & ",W10,N11:N" & LastRow & ")"
    .Range("P" & LastRow + 5).Formula = "=SUMIF(V11:V" & LastRow & ",W10,P11:P" & LastRow & ")"
    .Range("R" & LastRow + 5).Formula = "=SUMIF(V11:V" & LastRow & ",W10,R11:R" & LastRow & ")"
    'xxxxxx
    .Range("D" & LastRow + 6).FormulaR1C1 = "=DocSoUni(R[-5]C[7])"
   
    .Rows("11:" & LastRow - 1 & "").RowHeight = 35
    .Rows("" & LastRow + 1 & ":" & LastRow + 10 & "").RowHeight = 23
   
    .PageSetup.PrintArea = "$A$1:$S" & LastRow + 10 & ""
   
End With

End Sub
Nhấp chuột vào đây để mở rộng...
Dùng bộ thu macro lấy lệnh
Mã: 
Sub TongHop_XuatNhap_ThiCong_ThuHoi()
Dim LastRow

With Sheets("BQT-VTU")
    'Lay Dong cuoi cung cua BQT_VTU
    LastRow = .Cells(Rows.Count, "T").End(xlUp).Row

    'Tinh Tong tien vat tu xuat, thi cong, thu hoi, mat mat
    .Range("H" & LastRow + 1).Formula = "=SUBTOTAL(9,H11:H" & LastRow & ")"
    .Range("K" & LastRow + 1).Formula = "=SUBTOTAL(9,K11:K" & LastRow & ")"
    .Range("N" & LastRow + 1).Formula = "=SUBTOTAL(9,N11:N" & LastRow & ")"
    .Range("P" & LastRow + 1).Formula = "=SUBTOTAL(9,P11:P" & LastRow & ")"
    .Range("R" & LastRow + 1).Formula = "=SUBTOTAL(9,R11:R" & LastRow & ")"

    'Phan tich tong tien cac loai kho
    .Range("H" & LastRow + 2).Resize(4).Formula = "=SUMIF(R11C22:R16C22,R[-11]C23,R11C:R16C)"
    .Range("K" & LastRow + 2).Resize(4).Formula = "=SUMIF(R11C22:R16C22,R[-11]C23,R11C:R16C)"
    .Range("N" & LastRow + 2).Resize(4).Formula = "=SUMIF(R11C22:R16C22,R[-11]C23,R11C:R16C)"
    .Range("P" & LastRow + 2).Resize(4).Formula = "=SUMIF(R11C22:R16C22,R[-11]C23,R11C:R16C)"
    .Range("R" & LastRow + 2).Resize(4).Formula = "=SUMIF(R11C22:R16C22,R[-11]C23,R11C:R16C)"

    .Range("D" & LastRow + 6).FormulaR1C1 = "=DocSoUni(R[-5]C[7])"
    
    .Rows("11:" & LastRow - 1 & "").RowHeight = 35
    .Rows("" & LastRow + 1 & ":" & LastRow + 10 & "").RowHeight = 23
    
    .PageSetup.PrintArea = "$A$1:$S" & LastRow + 10 & ""
End With
End Sub
 
Trường hợp của anh đúng với dữ liệu tĩnh và cố định, cái em mong muốn và sử dụng LastRow do dữ liệu này động
 
Ý em là vùng dữ liệu tính toán và tính sumif đó thay đổi mỗi khi mình nạp vào có thể nhiều hơn hoặc ít hơn dữ liệu demo trên ạ, nên em muốn công thức tổng quát dựa trên LastRow (cái này em oánh tại 1 ô tại cột T)
 

File đính kèm

quyenpv đã viết:
Ý em là vùng dữ liệu tính toán và tính sumif đó thay đổi mỗi khi mình nạp vào có thể nhiều hơn hoặc ít hơn dữ liệu demo trên ạ, nên em muốn công thức tổng quát dựa trên LastRow (cái này em oánh tại 1 ô tại cột T)
Nhấp chuột vào đây để mở rộng...
Mã: 
Sub TongHop_XuatNhap_ThiCong_ThuHoi()
Dim LastRow

With Sheets("BQT-VTU")
    'Lay Dong cuoi cung cua BQT_VTU
    LastRow = .Cells(Rows.Count, "T").End(xlUp).Row

    'Tinh Tong tien vat tu xuat, thi cong, thu hoi, mat mat
    .Range("H" & LastRow + 1).Formula = "=SUBTOTAL(9,H11:H" & LastRow & ")"
    .Range("K" & LastRow + 1).Formula = "=SUBTOTAL(9,K11:K" & LastRow & ")"
    .Range("N" & LastRow + 1).Formula = "=SUBTOTAL(9,N11:N" & LastRow & ")"
    .Range("P" & LastRow + 1).Formula = "=SUBTOTAL(9,P11:P" & LastRow & ")"
    .Range("R" & LastRow + 1).Formula = "=SUBTOTAL(9,R11:R" & LastRow & ")"

    'Phan tich tong tien cac loai kho
    .Range("H" & LastRow + 2).Resize(4).Formula = "=SUMIF(R11C22:R" & LastRow & "C22,R[" & 5 - LastRow & "]C23,R11C:R" & LastRow & "C)"
    .Range("K" & LastRow + 2).Resize(4).Formula = "=SUMIF(R11C22:R" & LastRow & "C22,R[" & 5 - LastRow & "]C23,R11C:R" & LastRow & "C)"
    .Range("N" & LastRow + 2).Resize(4).Formula = "=SUMIF(R11C22:R" & LastRow & "C22,R[" & 5 - LastRow & "]C23,R11C:R" & LastRow & "C)"
    .Range("P" & LastRow + 2).Resize(4).Formula = "=SUMIF(R11C22:R" & LastRow & "C22,R[" & 5 - LastRow & "]C23,R11C:R" & LastRow & "C)"
    .Range("R" & LastRow + 2).Resize(4).Formula = "=SUMIF(R11C22:R" & LastRow & "C22,R[" & 5 - LastRow & "]C23,R11C:R" & LastRow & "C)"

    .Range("D" & LastRow + 6).FormulaR1C1 = "=DocSoUni(R[-5]C[7])"
    
    .Rows("11:" & LastRow - 1 & "").RowHeight = 35
    .Rows("" & LastRow + 1 & ":" & LastRow + 10 & "").RowHeight = 23
    
    .PageSetup.PrintArea = "$A$1:$S" & LastRow + 10 & ""
End With
End Sub
 
Cám ơn anh nhiều! Em mò chỉnh tới chỉnh lui mãi mà không được
Một lần nữa cám ơn anh
 
quyenpv đã viết:
Cám ơn anh nhiều! Em mò chỉnh tới chỉnh lui mãi mà không được
Một lần nữa cám ơn anh
Nhấp chuột vào đây để mở rộng...
Rút gọn code cho bạn
Mã: 
Sub TongHop_XuatNhap_ThiCong_ThuHoi()
  Dim eRow&, j&, aCol

  Application.ScreenUpdating = False
  aCol = Array("H", "K", "N", "P", "R") 'Mang cot cong thuc
  With Sheets("BQT-VTU")
    'Lay Dong cuoi cung cua BQT_VTU
    eRow = .Cells(Rows.Count, "B").End(xlUp).Row - 2
    For j = 0 To 4
      'Tinh Tong tien vat tu xuat, thi cong, thu hoi, mat mat
      .Range(aCol(j) & eRow + 1).Formula = "=SUBTOTAL(9,R11C:R" & eRow & "C)"
      'Phan tich tong tien cac loai kho
      .Range(aCol(j) & eRow + 2).Resize(4).Formula = "=SUMIF(R11C22:R" & eRow & "C22,R[" & 5 - eRow & "]C23,R11C:R" & eRow & "C)"
    Next j
    .Range("D" & eRow + 6).FormulaR1C1 = "=DocSoUni(R[-5]C[7])"
    .Rows("11:" & eRow - 1 & "").RowHeight = 35
    .Rows("" & eRow + 1 & ":" & eRow + 10 & "").RowHeight = 23
    .PageSetup.PrintArea = "$A$1:$S" & eRow + 10 & ""
  End With
  Application.ScreenUpdating = True
End Sub
 
Em cám ơn anh!
 
Bạn phải đăng nhập hoặc đăng ký để trả lời bài viết tại đây.
Web KT

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

T
Tô màu ảnh bác Hồ bằng Conditional Formatting (1 người xem)
Trả lời
0
Đọc
16
thuctien
T
M
File excel ứng dụng trong nhập xuất kho, thu chi, bán hàng và công nợ (3 người xem)
    • Thích
    • Yêu thích
    • Cảm ơn
7 8 9
Trả lời
166
Đọc
31K
giangsimso
G
K
lấy dữ liệu từ excel sang worrd
Trả lời
1
Đọc
33
thuytrangbp28
T
chickenexcel
xin hướng dẫn code chọn hình ảnh trong file (2 người xem)
Trả lời
4
Đọc
142
thuytrangbp28
T
T
Cách cộng tổng chính xác nhất (1 người xem)
Trả lời
7
Đọc
238
THANH657
T
GPE-Trợ Lý
ĐĂNG KÝ THAM DỰ SINH NHẬT GPE TẠI HN - SỰ KIỆN TALKSHOW ĐẶC BIỆT (6 người xem)
    • Thích
2
Trả lời
26
Đọc
2K
GPE-Trợ Lý
GPE-Trợ Lý
L
xin anh chị giúp em công thức tính tổng năm theo điều kiện ạ
Trả lời
9
Đọc
227
HieuCD
H
Y
Lấy dữ liệu từ nhiều sheet khác nhau.
2
Trả lời
20
Đọc
609
lib.enigma
L
Nguyễn Duy Tuân
  • Dán lên cao
Sử dụng AI trong Excel với Add-in A-Tools AI v10
    • Thích
    • Cảm ơn
Trả lời
8
Đọc
892
Nguyễn Duy Tuân
Nguyễn Duy Tuân
T
In lặp lại nhiều dòng ở cuối các trang
Trả lời
13
Đọc
452
Hoàng Tuấn 868
H
K
Tạo mã thiết bị
Trả lời
10
Đọc
278
khoailang
K
C
Xin file lên thực đơn và định lượng món ăn cho suất ăn công nghiệp (1 người xem)
Trả lời
0
Đọc
79
Chihuong_vp90
C
T
Lỗi Khi Mở File Excel
Trả lời
3
Đọc
112
Hoàng Tuấn 868
H
H
Em chào anh chị ạ.Em cần xếp lịch nghỉ randum cho 1 số nhóm làm việc trong 1 công ty.
Trả lời
4
Đọc
158
Duy Tùng 9x
Duy Tùng 9x
1
Em hỏi chút về hàm điều kiện với liệt kê (1 người xem)
Trả lời
2
Đọc
147
123456it
1
tramcannt
data validation (1 người xem)
Trả lời
2
Đọc
132
ptm0412
ptm0412
giaiphap
Add-Ins cho Excel 2007 -2016 32bit và 64bit
    • Thích
    • Yêu thích
    • Ngạc nhiên
24 25 26
Trả lời
514
Đọc
204K
hoangtuan.net
hoangtuan.net
Nguyễn Duy Tuân
  • Dán lên cao
Phiên bản mới Add-in A-Tools AI v10 (2024)
    • Thích
2 3
Trả lời
56
Đọc
7K
Nguyễn Duy Tuân
Nguyễn Duy Tuân
H
Lỗi syntax error
Trả lời
2
Đọc
130
ptm0412
ptm0412
H
Xin các bác giúp đỡ cách đổi thời gian thành số thập phân
Trả lời
8
Đọc
173
huyphanthieu
H
Xem thêm…

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

Sinh nhật lần 19 GPE

000
Ngày
00
Giờ
00
phút
00
giây
Thân mời tham dự sự kiện sinh nhật GPE 2025
Back
Top Bottom