Nhờ viết code đối chiếu so sánh cho nhiều đối tượng! (2 người xem)

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)
Hong.Van

Hong.Van

Busy
Tham gia
7/5/12
Bài viết
2,330
Được thích
1,767
Em chào Thầy cô & anh chị, xin giúp em viết code so sánh sau:

1/ Code cho kết qủa từ cell J9 trở xuống (cột Test)

2/ Cột B em có 2 mã Tài khoản chính là 131 và 3311 và các Mã Khách hàng
a/ Mã tài khoản 131 có các Mã khách hàng bao gồm 4 ký tự, ký tự đầu tiên là chữ B
b/ Mã tài khoản 3311 có các Mã khách hàng bao gồm 4 ký tự, ký tự đầu tiên là chữ M

3/ Bây giờ dùng các mã tại cột B để dò tính toán như sau:
a/ Nếu Cell bên cột B là 131, thì kết qủa là
Ví dụ cell J11
PHP: 
=H11-I11-($Q$11-$R$11)
b/ Nếu Cell bên cột B là có ký tự đầu tiên là chữ B, thì kết qủa là
Ví dụ cell J12
PHP: 
=H12-I12-(SUMIF($O$13:$O$202;B12;$Q$13:$Q$202)-SUMIF($O$13:$O$202;B12;$R$13:$R$202))
c/Nếu Cell bên cột B là 3311, thì kết qủa là
Ví dụ cell J58
PHP: 
=I58-H58-($Z$11-$AA$11)
d/Nếu Cell bên cột B là có ký tự đầu tiên là chữ M, thì kết qủa là
Ví dụ cell J59
PHP: 
=I59-H59-(SUMIF($X$13:$X$202;B59;$Z$13:$Z$202)-SUMIF($X$13:$X$202;B59;$AA$13:$AA$202))

4/ Các lưu ý
a/ Cột B có dữ liệu liên tục từ B11 trở xuống
b/ Cột J đã làm sẵn công thức
c/ Số lượng Mã khách hàng không cố định, có thể nhiều hơn hoặc ít hơn

Em cảm ơn!
 

File đính kèm

Lần chỉnh sửa cuối:
Sắp xếp theo thời gian sắp xếp theo bầu chọn
bạn xem dùm mình làm vậy có đúng không?
trong cthức cho 2 mã 133 (hay 131 bạn? trong bài là 131) và mã 3311 thì vùng Q11 và R11 là cố định luôn phải không (tôi thấy bạn sử dụng giá trị tuyệt đối trong cthức).

Sub ThuTinh()

LR = Range("B" & Rows.Count).End(xlUp).Row 'tim dong du lieu cuoi cung
For i = 11 To LR

If Range("B" & i).Value = 131 Then
Range("J" & i).Value = Range("H" & i) - Range("I" & i) - (Range("Q11") - Range("R11"))
ElseIf Range("B" & i).Value = 3311 Then
Range("J" & i).Value = Range("H" & i) - Range("I" & i) - (Range("Z11") - Range("AA11"))

ElseIf Left(Range("B" & i), 1) = "B" Then
PTKH = Application.WorksheetFunction.SumIf(Range("X13:X202"), Range("B" & i), Range("Z13:Z202"))
PTKH = Application.WorksheetFunction.SumIf(Range("X13:X202"), Range("B" & i), Range("AA13:AA202"))
Range("J" & i).Value = Range("H" & i) - Range("I" & i) - PTKH - PTKH
ElseIf Left(Range("B" & i), 1) = "M" Then
PTKH = Application.WorksheetFunction.SumIf(Range("O13:O202"), Range("B" & i), Range("Q13:Q202"))
PTKH = Application.WorksheetFunction.SumIf(Range("O13:O202"), Range("B" & i), Range("R13:R202"))
Range("J" & i).Value = Range("H" & i) - Range("I" & i) - PTKH - PTKH

End If
Next i
End Sub
 
Upvote 0
nhapmon đã viết:
bạn xem dùm mình làm vậy có đúng không?
trong cthức cho 2 mã 133 (hay 131 bạn? trong bài là 131) và mã 3311 thì vùng Q11 và R11 là cố định luôn phải không (tôi thấy bạn sử dụng giá trị tuyệt đối trong cthức).

Sub ThuTinh()

LR = Range("B" & Rows.Count).End(xlUp).Row 'tim dong du lieu cuoi cung
For i = 11 To LR

If Range("B" & i).Value = 131 Then
Range("J" & i).Value = Range("H" & i) - Range("I" & i) - (Range("Q11") - Range("R11"))
ElseIf Range("B" & i).Value = 3311 Then
Range("J" & i).Value = Range("H" & i) - Range("I" & i) - (Range("Z11") - Range("AA11"))

ElseIf Left(Range("B" & i), 1) = "B" Then
PTKH = Application.WorksheetFunction.SumIf(Range("X13:X202"), Range("B" & i), Range("Z13:Z202"))
PTKH = Application.WorksheetFunction.SumIf(Range("X13:X202"), Range("B" & i), Range("AA13:AA202"))
Range("J" & i).Value = Range("H" & i) - Range("I" & i) - PTKH - PTKH
ElseIf Left(Range("B" & i), 1) = "M" Then
PTKH = Application.WorksheetFunction.SumIf(Range("O13:O202"), Range("B" & i), Range("Q13:Q202"))
PTKH = Application.WorksheetFunction.SumIf(Range("O13:O202"), Range("B" & i), Range("R13:R202"))
Range("J" & i).Value = Range("H" & i) - Range("I" & i) - PTKH - PTKH

End If
Next i
End Sub
Nhấp chuột vào đây để mở rộng...
Cảm ơn sự gíup đỡ của bạn
1/ Xin lỗi sự nhầm lẫn: Mã tài khoản đúng là 131 chứ không phài 133
2/mã 3311 thì vùng Z11 và AA11 là vùng cố định còn Q11 và R11 là cố định cho 131 (bạn xem công thức)
3/ Bạn xem gíup lại code, khi chạy code kết quả chưa giống cột J
Xin cảm ơn!
 

File đính kèm

Upvote 0
Từ Yêu cầu bài #1 của em, em viết code như sau:
Mã: 
Sub DoiChieu()    Dim sArray, Arr()
    Dim i As Long
    sArray = Range([B11], [B65536].End(3)).Resize(, 8).Value
    MsgBox Range([B11], [B65536].End(3)).Resize(, 8).Address
    Dim n1 As Range, n2 As Range, n3 As Range, n4 As Range, n5 As Range, n6 As Range, n7 As Range, n8 As Range, n9 As Range, n10 As Range
    Set n1 = Range("Q11")
    Set n2 = Range("R11")
    Set n3 = Range("Z11")
    Set n4 = Range("AA11")
    Set n5 = Range("O13:O202")
    Set n6 = Range("Q13:Q202")
    Set n7 = Range("R13:R202")
    Set n8 = Range("X13:X202")
    Set n9 = Range("Z13:Z202")
    Set n10 = Range("AA13:AA202")




    ReDim Arr(1 To UBound(sArray, 1), 1 To 1)
    For i = 1 To UBound(sArray, 1)
        If sArray(i, 1) = 131 Then
            Arr(i, 1) = sArray(i, 7) - sArray(i, 8) - (n1 - n2)
        ElseIf sArray(i, 1) = 3311 Then
            Arr(i, 1) = sArray(i, 8) - sArray(i, 7) - (n3 - n4)
        ElseIf Left(sArray(i, 1), 1) = "B" Then
            Arr(i, 1) = sArray(i, 7) - sArray(i, 8) - (SumIf(n5, Arr(i, 1), n6) - SumIf(n5, Arr(i, 1), n7))
        ElseIf Left(sArray(i, 1), 1) = "M" Then
            Arr(i, 1) = sArray(i, 8) - sArray(i, 7) - (SumIf(n8, Arr(i, 1), n9) - SumIf(n8, Arr(i, 1), n10))
        Else
            Arr(i, 1) = ""
        End If
    Next i
    Range("J11").Resize(UBound(Arr, 1)).Value = Arr
End Sub
Code trên em viết lẫn vừa mảng vừa công thức nên nó báo lỗi tại các dòng dưới đây
Mã: 
Arr(i, 1) = sArray(i, 7) - sArray(i, 8) - (SumIf(n5, Arr(i, 1), n6) - SumIf(n5, Arr(i, 1), n7))
và
Mã: 
Arr(i, 1) = sArray(i, 8) - sArray(i, 7) - (SumIf(n8, Arr(i, 1), n9) - SumIf(n8, Arr(i, 1), n10))
Em chưa biết cách xử lý trường hợp thêm hàm SUMIF() nói trên
Xin vui lòng giúp em! Em cảm ơn
 

File đính kèm

Upvote 0
Hong.Van đã viết:
Từ Yêu cầu bài #1 của em, em viết code như sau:
Mã: 
Sub DoiChieu()    Dim sArray, Arr()
    Dim i As Long
    sArray = Range([B11], [B65536].End(3)).Resize(, 8).Value
    MsgBox Range([B11], [B65536].End(3)).Resize(, 8).Address
    Dim n1 As Range, n2 As Range, n3 As Range, n4 As Range, n5 As Range, n6 As Range, n7 As Range, n8 As Range, n9 As Range, n10 As Range
    Set n1 = Range("Q11")
    Set n2 = Range("R11")
    Set n3 = Range("Z11")
    Set n4 = Range("AA11")
    Set n5 = Range("O13:O202")
    Set n6 = Range("Q13:Q202")
    Set n7 = Range("R13:R202")
    Set n8 = Range("X13:X202")
    Set n9 = Range("Z13:Z202")
    Set n10 = Range("AA13:AA202")




    ReDim Arr(1 To UBound(sArray, 1), 1 To 1)
    For i = 1 To UBound(sArray, 1)
        If sArray(i, 1) = 131 Then
            Arr(i, 1) = sArray(i, 7) - sArray(i, 8) - (n1 - n2)
        ElseIf sArray(i, 1) = 3311 Then
            Arr(i, 1) = sArray(i, 8) - sArray(i, 7) - (n3 - n4)
        ElseIf Left(sArray(i, 1), 1) = "B" Then
            Arr(i, 1) = sArray(i, 7) - sArray(i, 8) - (SumIf(n5, Arr(i, 1), n6) - SumIf(n5, Arr(i, 1), n7))
        ElseIf Left(sArray(i, 1), 1) = "M" Then
            Arr(i, 1) = sArray(i, 8) - sArray(i, 7) - (SumIf(n8, Arr(i, 1), n9) - SumIf(n8, Arr(i, 1), n10))
        Else
            Arr(i, 1) = ""
        End If
    Next i
    Range("J11").Resize(UBound(Arr, 1)).Value = Arr
End Sub
Code trên em viết lẫn vừa mảng vừa công thức nên nó báo lỗi tại các dòng dưới đây
Mã: 
Arr(i, 1) = sArray(i, 7) - sArray(i, 8) - (SumIf(n5, Arr(i, 1), n6) - SumIf(n5, Arr(i, 1), n7))
và
Mã: 
Arr(i, 1) = sArray(i, 8) - sArray(i, 7) - (SumIf(n8, Arr(i, 1), n9) - SumIf(n8, Arr(i, 1), n10))
Em chưa biết cách xử lý trường hợp thêm hàm SUMIF() nói trên
Xin vui lòng giúp em! Em cảm ơn
Nhấp chuột vào đây để mở rộng...
Để đơn giản và khỏi suy nghĩ thêm ta làm như sau:
- Khi báo 1 biến WF as WorksheetFunction
- Chổ nào có SUMIF thì sửa thành WF.SUMIF
Mã: 
Sub DoiChieu()
    Dim sArray, Arr()
    Dim i As Long
    sArray = Range([B11], [B65536].End(3)).Resize(, 8).Value
    Dim n1 As Range, n2 As Range, n3 As Range, n4 As Range, n5 As Range, n6 As Range, n7 As Range, n8 As Range, n9 As Range, n10 As Range
    Set n1 = Range("Q11")
    Set n2 = Range("R11")
    Set n3 = Range("Z11")
    Set n4 = Range("AA11")
    Set n5 = Range("O13:O202")
    Set n6 = Range("Q13:Q202")
    Set n7 = Range("R13:R202")
    Set n8 = Range("X13:X202")
    Set n9 = Range("Z13:Z202")
    Set n10 = Range("AA13:AA202")


    ReDim Arr(1 To UBound(sArray, 1), 1 To 1)
    [COLOR=#ff0000]Dim WF As WorksheetFunction
    Set WF = WorksheetFunction[/COLOR]
    For i = 1 To UBound(sArray, 1)
        If sArray(i, 1) = 131 Then
            Arr(i, 1) = sArray(i, 7) - sArray(i, 8) - (n1 - n2)
        ElseIf sArray(i, 1) = 3311 Then
            Arr(i, 1) = sArray(i, 8) - sArray(i, 7) - (n3 - n4)
        ElseIf Left(sArray(i, 1), 1) = "B" Then
            Arr(i, 1) = sArray(i, 7) - sArray(i, 8) - ([COLOR=#ff0000]WF.[/COLOR]SumIf(n5, Arr(i, 1), n6) -[COLOR=#ff0000] WF[/COLOR].SumIf(n5, Arr(i, 1), n7))
        ElseIf Left(sArray(i, 1), 1) = "M" Then
            Arr(i, 1) = sArray(i, 8) - sArray(i, 7) - ([COLOR=#ff0000]WF.[/COLOR]SumIf(n8, Arr(i, 1), n9) -[COLOR=#ff0000] WF.[/COLOR]SumIf(n8, Arr(i, 1), n10))
        Else
            Arr(i, 1) = ""
        End If
    Next i
    Range("J11").Resize(UBound(Arr, 1)).Value = Arr
End Sub
Chổ màu đỏ là chổ mới thêm vào
(đương nhiên đây cũng không phải là cách hay nhất)
 
Upvote 0
ndu96081631 đã viết:
Để đơn giản và khỏi suy nghĩ thêm ta làm như sau:
- Khi báo 1 biến WF as WorksheetFunction
- Chổ nào có SUMIF thì sửa thành WF.SUMIF
Mã: 
Sub DoiChieu()
    Dim sArray, Arr()
    Dim i As Long
    sArray = Range([B11], [B65536].End(3)).Resize(, 8).Value
    Dim n1 As Range, n2 As Range, n3 As Range, n4 As Range, n5 As Range, n6 As Range, n7 As Range, n8 As Range, n9 As Range, n10 As Range
    Set n1 = Range("Q11")
    Set n2 = Range("R11")
    Set n3 = Range("Z11")
    Set n4 = Range("AA11")
    Set n5 = Range("O13:O202")
    Set n6 = Range("Q13:Q202")
    Set n7 = Range("R13:R202")
    Set n8 = Range("X13:X202")
    Set n9 = Range("Z13:Z202")
    Set n10 = Range("AA13:AA202")


    ReDim Arr(1 To UBound(sArray, 1), 1 To 1)
    [COLOR=#ff0000]Dim WF As WorksheetFunction
    Set WF = WorksheetFunction[/COLOR]
    For i = 1 To UBound(sArray, 1)
        If sArray(i, 1) = 131 Then
            Arr(i, 1) = sArray(i, 7) - sArray(i, 8) - (n1 - n2)
        ElseIf sArray(i, 1) = 3311 Then
            Arr(i, 1) = sArray(i, 8) - sArray(i, 7) - (n3 - n4)
        ElseIf Left(sArray(i, 1), 1) = "B" Then
            Arr(i, 1) = sArray(i, 7) - sArray(i, 8) - ([COLOR=#ff0000]WF.[/COLOR]SumIf(n5, Arr(i, 1), n6) -[COLOR=#ff0000] WF[/COLOR].SumIf(n5, Arr(i, 1), n7))
        ElseIf Left(sArray(i, 1), 1) = "M" Then
            Arr(i, 1) = sArray(i, 8) - sArray(i, 7) - ([COLOR=#ff0000]WF.[/COLOR]SumIf(n8, Arr(i, 1), n9) -[COLOR=#ff0000] WF.[/COLOR]SumIf(n8, Arr(i, 1), n10))
        Else
            Arr(i, 1) = ""
        End If
    Next i
    Range("J11").Resize(UBound(Arr, 1)).Value = Arr
End Sub
Chổ màu đỏ là chổ mới thêm vào
(đương nhiên đây cũng không phải là cách hay nhất)
Nhấp chuột vào đây để mở rộng...
Thầy viết giùm em cách ngắn gọn & nhanh hơn
Code trên của em chưa ổn , vì các biến từ n5 đến n10 sẽ không phải cố định đến dòng 203, mà nó có thể nhiều hơn hoặc ít hơn
Em cảm ơn
 
Upvote 0
Không biết code trên em đặt công thức có đúng không, mà sao kết qủa kg cho đúng với cột J
Em thấy cthức bên dưới nó không chạy thì phải?
Mã: 
 [COLOR=#000000][I]([/I][/COLOR][COLOR=#ff0000][I]WF.[/I][/COLOR][COLOR=#000000][I]SumIf(n5, Arr(i, 1), n6) -[/I][/COLOR][COLOR=#ff0000][I] WF[/I][/COLOR][COLOR=#000000][I].SumIf(n5, Arr(i, 1), n7))
[/I][/COLOR]
([COLOR=#ff0000]WF.[/COLOR]SumIf(n8, Arr(i, 1), n9) -[COLOR=#ff0000] WF.[/COLOR]SumIf(n8, Arr(i, 1), n10))
Vui lòng kiểm tra giúp em!
 
Upvote 0
Hong.Van đã viết:
Không biết code trên em đặt công thức có đúng không, mà sao kết qủa kg cho đúng với cột J
Em thấy cthức bên dưới nó không chạy thì phải?
Mã: 
 [COLOR=#000000][I]([/I][/COLOR][COLOR=#ff0000][I]WF.[/I][/COLOR][COLOR=#000000][I]SumIf(n5, Arr(i, 1), n6) -[/I][/COLOR][COLOR=#ff0000][I] WF[/I][/COLOR][COLOR=#000000][I].SumIf(n5, Arr(i, 1), n7))
[/I][/COLOR]
([COLOR=#ff0000]WF.[/COLOR]SumIf(n8, Arr(i, 1), n9) -[COLOR=#ff0000] WF.[/COLOR]SumIf(n8, Arr(i, 1), n10))
Vui lòng kiểm tra giúp em!
Nhấp chuột vào đây để mở rộng...
Cái này em đã tìm ra
Mã: 
(WF.SumIf(n5, [COLOR=#ff0000]sArray[/COLOR](i, 1), n6) - WF.SumIf(n5, [COLOR=#ff0000]sArray[/COLOR](i, 1), n7))
(WF.SumIf(n8, [COLOR=#ff0000]sArray[/COLOR](i, 1), n9) - WF.SumIf(n8, [COLOR=#ff0000]sArray[/COLOR](i, 1), n10))
 
Upvote 0
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

NguyenBaChien
Nhờ tách lấy số và tính tổng có điều kiện của dãy số (6 người xem)
Trả lời
0
Đọc
1
NguyenBaChien
NguyenBaChien
L
[Xin giúp] Cách copy công thức từ bảng tham chiếu vào một ô (2 người xem)
Trả lời
1
Đọc
16
bebo021999
B
hoangluongvinh
Cách nhặt các dữ liệu bị trùng của 1 đối tượng (1 người xem)
Trả lời
8
Đọc
157
tt_kimbai
T
N
  • Question Question
Cách viết tiếng việt có dấu trong msgbox,....... Hoàn toàn mới (2 người xem)
    • Thích
2
Trả lời
35
Đọc
5K
ligktit
L
T
  • Question Question
Lọc dữ liệu có nhiều điều kiện (3 người xem)
Trả lời
2
Đọc
104
tt_kimbai
T
H
xin mọi người giúp em làm tròn với ạ
Trả lời
4
Đọc
91
NguyenHoangDiep
N
longhungvnpost
cả nhà giúp e tạo ô tìm kiếm gợi ý trong danh sách nhé
Trả lời
3
Đọc
105
longhungvnpost
longhungvnpost
S
Cố định độ rộng/ cao của cột/hàng? (1 người xem)
Trả lời
12
Đọc
17K
khanhnth02
K
ongke0711
  • Question Question
Tải hóa đơn điện tử (https://hoadondientu.gdt.gov.vn/) Excel Vba
    • Thích
    • Yêu thích
    • Cảm ơn
15 16 17
Trả lời
329
Đọc
26K
tt_kimbai
T
dung1983
Hỏi về import - Table/ XML cho bảng, trang có chứa JS (1 người xem)
Trả lời
0
Đọc
60
dung1983
dung1983
HeSanbi
  • Đã giải quyết
VBA_FastCode v2.22 - Công cụ học và viết mã VBA một cách nhanh nhất (*27/10/2023) (5 người xem)
    • Thích
    • Yêu thích
    • Cảm ơn
2 3
Trả lời
51
Đọc
15K
HeSanbi
HeSanbi
Manhhung90
Mail merge in ấn theo từng trang mong muốn.
Trả lời
1
Đọc
107
Hoàng Tuấn 868
H
L
Xin giúp đỡ đổi màu chữ trong mathtype powerpoint sang màu đen
Trả lời
4
Đọc
128
LINHCM
L
ongke0711
  • Question Question
Form Đăng nhập và phân quyền cơ bản
    • Thích
2 3
Trả lời
40
Đọc
5K
Gà Công Nghệ
Gà Công Nghệ
vnxgiokinhtoi
Nhờ giúp đỡ công thức lưu dữ liệu và mở lại dữ liệu cũ đã save
Trả lời
6
Đọc
213
DeTong
D
A
  • Question Question
Addin lúc hiện lúc không
Trả lời
1
Đọc
109
anhdepjai
A
T
  • Question Question
Ẩn hiện các sheet khi click vào HyperLink
Trả lời
6
Đọc
296
Gà Công Nghệ
Gà Công Nghệ
20050911
File Excel lỗi ''The file could not be accessed. Try one of the following:.."
Trả lời
17
Đọc
3K
chienthang2682
C
C
Tải kết quả xổ số 3 miền về excel
    • Thích
3 4 5
Trả lời
98
Đọc
14K
HNam_Phan
H
S
Xuất các bảng công phụ từ bảng công chính trong gg sheet
Trả lời
0
Đọc
115
sirhungvla
S
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

Đếm ngược thời gian

000
Ngày
00
Giờ
00
phút
00
giây
TALKSHOW KHÁM PHÁ SỨC MẠNH HÀM FILTER TRONG EXCEL 365
Back
Top Bottom