Thay công thức mảng và Sumproduct bằng code VBA

Liên hệ QC
M

mitdacdtd

Thành viên hoạt động
Tham gia
14/10/17
Bài viết
150
Được thích
23
Giới tính
Nam
Chào anh em diễn đàn.
Hiện mình có file excel dùng công thức mảng và công thức Sumproduct rất nặng file; thân nhờ anh em giúp đỡ code để giảm tải và nhanh hơn.
Trong file đính kèm, các sheet có phần đánh dấu vàng là chứa công thức mảng hoặc sumproduct cần nhờ anh em.
Cảm ơn mọi người nhiều nhé.
 

File đính kèm

Sắp xếp theo thời gian sắp xếp theo bầu chọn
mitdacdtd đã viết:
Chào anh em diễn đàn.
Hiện mình có file excel dùng công thức mảng và công thức Sumproduct rất nặng file; thân nhờ anh em giúp đỡ code để giảm tải và nhanh hơn.
Trong file đính kèm, các sheet có phần đánh dấu vàng là chứa công thức mảng hoặc sumproduct cần nhờ anh em.
Cảm ơn mọi người nhiều nhé.
Nhấp chuột vào đây để mở rộng...

Thế này được không bạn?
Mã: 
Sub Tinhtoan()

    Dim dongcuoi As Long
    Dim shThoi As String, shCat As String, shDinhMuc As String
    Dim shTonghopThoi As String, shTonghopCat As String, shTongHop As String
    
    shThoi = "TH" & ChrW(7892) & "I"
    shCat = "C" & ChrW(7854) & "T"
    shDinhMuc = ChrW(273) & ".m" & ChrW(7913) & "c d" & ChrW(7883) & "ch"
    shTonghopThoi = "TH TH" & ChrW(7892) & "I"
    shTonghopCat = "TH C" & ChrW(7854) & "T"
    shTongHop = "T" & ChrW(7893) & "ng h" & ChrW(7907) & "p"
    
    Application.ScreenUpdating = False
    
    On Error GoTo Loi
    
    With Sheets(shTongHop)
        dongcuoi = .Cells(.Rows.Count, "A").End(xlUp).Row
        If dongcuoi > 3 Then
            .Range("C4:C" & dongcuoi) = "=SUMPRODUCT('" & shCat & "'!$AB$3:$AB$1433,('" & shTonghopCat & "'!$A7=DAY('" & shCat & "'!$B$3:$B$1433))*('" & shCat & "'!$C$3:$C$1433='" & shTonghopCat & "'!$B$4))"
            .Range("D4:D" & dongcuoi) = "=SUMPRODUCT('" & shCat & "'!$BB$3:$BB$1433,('" & shTonghopCat & "'!$A7=DAY('" & shCat & "'!$B$3:$B$1433))*('" & shCat & "'!$C$3:$C$1433='" & shTonghopCat & "'!$B$4))"
            .Range("E4:E" & dongcuoi) = "=SUMPRODUCT('" & shCat & "'!$X$3:$X$1433,('" & shTonghopCat & "'!$A7=DAY('" & shCat & "'!$B$3:$B$1433))*('" & shCat & "'!$C$3:$C$1433='" & shTonghopCat & "'!$B$4))"
            .Range("C4:E" & dongcuoi).Value = .Range("C4:E" & dongcuoi).Value
            .Range("H4:H" & dongcuoi) = "=SUMPRODUCT('" & shCat & "'!$AS$3:$AS$1433,('" & shTonghopCat & "'!$A4=DAY('" & shCat & "'!$B$3:$B$1433))*('" & shCat & "'!$C$3:$C$1433='" & shTonghopCat & "'!$B4))"
            .Range("I4:I" & dongcuoi) = "=SUMPRODUCT('" & shCat & "'!$AR$3:$AR$1433,('" & shTonghopCat & "'!$A4=DAY('" & shCat & "'!$B$3:$B$1433))*('" & shCat & "'!$C$3:$C$1433='" & shTonghopCat & "'!$B4))"
            .Range("J4:J" & dongcuoi) = "=SUMPRODUCT('" & shCat & "'!$AY$3:$AY$1433,('" & shTonghopCat & "'!$A4=DAY('" & shCat & "'!$B$3:$B$1433))*('" & shCat & "'!$C$3:$C$1433='" & shTonghopCat & "'!$B4))"
            .Range("H4:J" & dongcuoi).Value = .Range("H4:J" & dongcuoi).Value
            .Range("AF4:AF" & dongcuoi) = "=SUMPRODUCT('" & shCat & "'!$AB$3:$AB$1433,('" & shTonghopCat & "'!$V4=DAY('" & shCat & "'!$B$3:$B$1433))*('" & shCat & "'!$C$3:$C$1433='" & shTonghopCat & "'!$W$4))"
            .Range("AG4:AG" & dongcuoi) = "=SUMPRODUCT('" & shCat & "'!$BB$3:$BB$1433,('" & shTonghopCat & "'!$V4=DAY('" & shCat & "'!$B$3:$B$1433))*('" & shCat & "'!$C$3:$C$1433='" & shTonghopCat & "'!$W$4))"
            .Range("AH4:AH" & dongcuoi) = "=SUMPRODUCT('" & shCat & "'!$X$3:$X$1433,('" & shTonghopCat & "'!$V4=DAY('" & shCat & "'!$B$3:$B$1433))*('" & shCat & "'!$C$3:$C$1433='" & shTonghopCat & "'!$W$4))"
            .Range("AF4:AH" & dongcuoi).Value = .Range("AF4:AH" & dongcuoi).Value
            .Range("AK4:AK" & dongcuoi) = "=SUMPRODUCT('" & shCat & "'!$AS$3:$AS$1433,('" & shTonghopCat & "'!$V4=DAY('" & shCat & "'!$B$3:$B$1433))*('" & shCat & "'!$C$3:$C$1433='" & shTonghopCat & "'!$W$4))"
            .Range("AL4:AL" & dongcuoi) = "=SUMPRODUCT('" & shCat & "'!$AR$3:$AR$1433,('" & shTonghopCat & "'!$V4=DAY('" & shCat & "'!$B$3:$B$1433))*('" & shCat & "'!$C$3:$C$1433='" & shTonghopCat & "'!$W$4))"
            .Range("AM4:AM" & dongcuoi) = "=SUMPRODUCT('" & shCat & "'!$AY$3:$AY$1433,('" & shTonghopCat & "'!$V4=DAY('" & shCat & "'!$B$3:$B$1433))*('" & shCat & "'!$C$3:$C$1433='" & shTonghopCat & "'!$W$4))"
            .Range("AK4:AM" & dongcuoi).Value = .Range("AK4:AM" & dongcuoi).Value
        End If
    End With
    
    With Sheets(shCat)
        dongcuoi = .Cells(.Rows.Count, "A").End(xlUp).Row
        If dongcuoi > 2 Then
            .Range("X3:Y" & dongcuoi).ClearContents
            .Range("X3").FormulaArray = "=VLOOKUP(D3&E3,CHOOSE({1,2},'" & shDinhMuc & "'!$G$3:$G$9212&'" & shDinhMuc & "'!$B$3:$B$9212,'" & shDinhMuc & "'!$H$3:$H$9212),2,0)"
            .Range("X3").Copy .Range("X4:X" & dongcuoi)
            .Range("Y3").FormulaArray = "=VLOOKUP(D3&E3,CHOOSE({1,2},'" & shDinhMuc & "'!$G$3:$G$9212&'" & shDinhMuc & "'!$B$3:$B$9212,'" & shDinhMuc & "'!$O$3:$O$9212),2,0)"
            .Range("Y3").Copy .Range("Y4:Y" & dongcuoi)
            .Range("X3:Y" & dongcuoi).Value = .Range("X3:Y" & dongcuoi).Value
        End If
    End With

    With Sheets(shTongHop)
        dongcuoi = .Cells(.Rows.Count, "A").End(xlUp).Row
        '..tuong tu
    End With
    
    With Sheets(shTonghopCat)
        dongcuoi = .Cells(.Rows.Count, "A").End(xlUp).Row
        '..tuong tu
    End With
    
Loi:
    Application.ScreenUpdating = True
    If Err Then MsgBox Err.Description, vbCritical + vbOKOnly
    
End Sub
 
Upvote 0
Nguyễn Hoàng Oanh Thơ đã viết:
Thế này được không bạn?
Mã: 
Sub Tinhtoan()

    Dim dongcuoi As Long
    Dim shThoi As String, shCat As String, shDinhMuc As String
    Dim shTonghopThoi As String, shTonghopCat As String, shTongHop As String
   
    shThoi = "TH" & ChrW(7892) & "I"
    shCat = "C" & ChrW(7854) & "T"
    shDinhMuc = ChrW(273) & ".m" & ChrW(7913) & "c d" & ChrW(7883) & "ch"
    shTonghopThoi = "TH TH" & ChrW(7892) & "I"
    shTonghopCat = "TH C" & ChrW(7854) & "T"
    shTongHop = "T" & ChrW(7893) & "ng h" & ChrW(7907) & "p"
   
    Application.ScreenUpdating = False
   
    On Error GoTo Loi
   
    With Sheets(shTongHop)
        dongcuoi = .Cells(.Rows.Count, "A").End(xlUp).Row
        If dongcuoi > 3 Then
            .Range("C4:C" & dongcuoi) = "=SUMPRODUCT('" & shCat & "'!$AB$3:$AB$1433,('" & shTonghopCat & "'!$A7=DAY('" & shCat & "'!$B$3:$B$1433))*('" & shCat & "'!$C$3:$C$1433='" & shTonghopCat & "'!$B$4))"
            .Range("D4:D" & dongcuoi) = "=SUMPRODUCT('" & shCat & "'!$BB$3:$BB$1433,('" & shTonghopCat & "'!$A7=DAY('" & shCat & "'!$B$3:$B$1433))*('" & shCat & "'!$C$3:$C$1433='" & shTonghopCat & "'!$B$4))"
            .Range("E4:E" & dongcuoi) = "=SUMPRODUCT('" & shCat & "'!$X$3:$X$1433,('" & shTonghopCat & "'!$A7=DAY('" & shCat & "'!$B$3:$B$1433))*('" & shCat & "'!$C$3:$C$1433='" & shTonghopCat & "'!$B$4))"
            .Range("C4:E" & dongcuoi).Value = .Range("C4:E" & dongcuoi).Value
            .Range("H4:H" & dongcuoi) = "=SUMPRODUCT('" & shCat & "'!$AS$3:$AS$1433,('" & shTonghopCat & "'!$A4=DAY('" & shCat & "'!$B$3:$B$1433))*('" & shCat & "'!$C$3:$C$1433='" & shTonghopCat & "'!$B4))"
            .Range("I4:I" & dongcuoi) = "=SUMPRODUCT('" & shCat & "'!$AR$3:$AR$1433,('" & shTonghopCat & "'!$A4=DAY('" & shCat & "'!$B$3:$B$1433))*('" & shCat & "'!$C$3:$C$1433='" & shTonghopCat & "'!$B4))"
            .Range("J4:J" & dongcuoi) = "=SUMPRODUCT('" & shCat & "'!$AY$3:$AY$1433,('" & shTonghopCat & "'!$A4=DAY('" & shCat & "'!$B$3:$B$1433))*('" & shCat & "'!$C$3:$C$1433='" & shTonghopCat & "'!$B4))"
            .Range("H4:J" & dongcuoi).Value = .Range("H4:J" & dongcuoi).Value
            .Range("AF4:AF" & dongcuoi) = "=SUMPRODUCT('" & shCat & "'!$AB$3:$AB$1433,('" & shTonghopCat & "'!$V4=DAY('" & shCat & "'!$B$3:$B$1433))*('" & shCat & "'!$C$3:$C$1433='" & shTonghopCat & "'!$W$4))"
            .Range("AG4:AG" & dongcuoi) = "=SUMPRODUCT('" & shCat & "'!$BB$3:$BB$1433,('" & shTonghopCat & "'!$V4=DAY('" & shCat & "'!$B$3:$B$1433))*('" & shCat & "'!$C$3:$C$1433='" & shTonghopCat & "'!$W$4))"
            .Range("AH4:AH" & dongcuoi) = "=SUMPRODUCT('" & shCat & "'!$X$3:$X$1433,('" & shTonghopCat & "'!$V4=DAY('" & shCat & "'!$B$3:$B$1433))*('" & shCat & "'!$C$3:$C$1433='" & shTonghopCat & "'!$W$4))"
            .Range("AF4:AH" & dongcuoi).Value = .Range("AF4:AH" & dongcuoi).Value
            .Range("AK4:AK" & dongcuoi) = "=SUMPRODUCT('" & shCat & "'!$AS$3:$AS$1433,('" & shTonghopCat & "'!$V4=DAY('" & shCat & "'!$B$3:$B$1433))*('" & shCat & "'!$C$3:$C$1433='" & shTonghopCat & "'!$W$4))"
            .Range("AL4:AL" & dongcuoi) = "=SUMPRODUCT('" & shCat & "'!$AR$3:$AR$1433,('" & shTonghopCat & "'!$V4=DAY('" & shCat & "'!$B$3:$B$1433))*('" & shCat & "'!$C$3:$C$1433='" & shTonghopCat & "'!$W$4))"
            .Range("AM4:AM" & dongcuoi) = "=SUMPRODUCT('" & shCat & "'!$AY$3:$AY$1433,('" & shTonghopCat & "'!$V4=DAY('" & shCat & "'!$B$3:$B$1433))*('" & shCat & "'!$C$3:$C$1433='" & shTonghopCat & "'!$W$4))"
            .Range("AK4:AM" & dongcuoi).Value = .Range("AK4:AM" & dongcuoi).Value
        End If
    End With
   
    With Sheets(shCat)
        dongcuoi = .Cells(.Rows.Count, "A").End(xlUp).Row
        If dongcuoi > 2 Then
            .Range("X3:Y" & dongcuoi).ClearContents
            .Range("X3").FormulaArray = "=VLOOKUP(D3&E3,CHOOSE({1,2},'" & shDinhMuc & "'!$G$3:$G$9212&'" & shDinhMuc & "'!$B$3:$B$9212,'" & shDinhMuc & "'!$H$3:$H$9212),2,0)"
            .Range("X3").Copy .Range("X4:X" & dongcuoi)
            .Range("Y3").FormulaArray = "=VLOOKUP(D3&E3,CHOOSE({1,2},'" & shDinhMuc & "'!$G$3:$G$9212&'" & shDinhMuc & "'!$B$3:$B$9212,'" & shDinhMuc & "'!$O$3:$O$9212),2,0)"
            .Range("Y3").Copy .Range("Y4:Y" & dongcuoi)
            .Range("X3:Y" & dongcuoi).Value = .Range("X3:Y" & dongcuoi).Value
        End If
    End With

    With Sheets(shTongHop)
        dongcuoi = .Cells(.Rows.Count, "A").End(xlUp).Row
        '..tuong tu
    End With
   
    With Sheets(shTonghopCat)
        dongcuoi = .Cells(.Rows.Count, "A").End(xlUp).Row
        '..tuong tu
    End With
   
Loi:
    Application.ScreenUpdating = True
    If Err Then MsgBox Err.Description, vbCritical + vbOKOnly
   
End Sub
Nhấp chuột vào đây để mở rộng...
Cảm ơn bạn rất nhiều, mình kiểm tra lại đã nhé.
Bài đã được tự động gộp:

Nguyễn Hoàng Oanh Thơ đã viết:
Thế này được không bạn?
Mã: 
Sub Tinhtoan()

    Dim dongcuoi As Long
    Dim shThoi As String, shCat As String, shDinhMuc As String
    Dim shTonghopThoi As String, shTonghopCat As String, shTongHop As String
   
    shThoi = "TH" & ChrW(7892) & "I"
    shCat = "C" & ChrW(7854) & "T"
    shDinhMuc = ChrW(273) & ".m" & ChrW(7913) & "c d" & ChrW(7883) & "ch"
    shTonghopThoi = "TH TH" & ChrW(7892) & "I"
    shTonghopCat = "TH C" & ChrW(7854) & "T"
    shTongHop = "T" & ChrW(7893) & "ng h" & ChrW(7907) & "p"
   
    Application.ScreenUpdating = False
   
    On Error GoTo Loi
   
    With Sheets(shTongHop)
        dongcuoi = .Cells(.Rows.Count, "A").End(xlUp).Row
        If dongcuoi > 3 Then
            .Range("C4:C" & dongcuoi) = "=SUMPRODUCT('" & shCat & "'!$AB$3:$AB$1433,('" & shTonghopCat & "'!$A7=DAY('" & shCat & "'!$B$3:$B$1433))*('" & shCat & "'!$C$3:$C$1433='" & shTonghopCat & "'!$B$4))"
            .Range("D4:D" & dongcuoi) = "=SUMPRODUCT('" & shCat & "'!$BB$3:$BB$1433,('" & shTonghopCat & "'!$A7=DAY('" & shCat & "'!$B$3:$B$1433))*('" & shCat & "'!$C$3:$C$1433='" & shTonghopCat & "'!$B$4))"
            .Range("E4:E" & dongcuoi) = "=SUMPRODUCT('" & shCat & "'!$X$3:$X$1433,('" & shTonghopCat & "'!$A7=DAY('" & shCat & "'!$B$3:$B$1433))*('" & shCat & "'!$C$3:$C$1433='" & shTonghopCat & "'!$B$4))"
            .Range("C4:E" & dongcuoi).Value = .Range("C4:E" & dongcuoi).Value
            .Range("H4:H" & dongcuoi) = "=SUMPRODUCT('" & shCat & "'!$AS$3:$AS$1433,('" & shTonghopCat & "'!$A4=DAY('" & shCat & "'!$B$3:$B$1433))*('" & shCat & "'!$C$3:$C$1433='" & shTonghopCat & "'!$B4))"
            .Range("I4:I" & dongcuoi) = "=SUMPRODUCT('" & shCat & "'!$AR$3:$AR$1433,('" & shTonghopCat & "'!$A4=DAY('" & shCat & "'!$B$3:$B$1433))*('" & shCat & "'!$C$3:$C$1433='" & shTonghopCat & "'!$B4))"
            .Range("J4:J" & dongcuoi) = "=SUMPRODUCT('" & shCat & "'!$AY$3:$AY$1433,('" & shTonghopCat & "'!$A4=DAY('" & shCat & "'!$B$3:$B$1433))*('" & shCat & "'!$C$3:$C$1433='" & shTonghopCat & "'!$B4))"
            .Range("H4:J" & dongcuoi).Value = .Range("H4:J" & dongcuoi).Value
            .Range("AF4:AF" & dongcuoi) = "=SUMPRODUCT('" & shCat & "'!$AB$3:$AB$1433,('" & shTonghopCat & "'!$V4=DAY('" & shCat & "'!$B$3:$B$1433))*('" & shCat & "'!$C$3:$C$1433='" & shTonghopCat & "'!$W$4))"
            .Range("AG4:AG" & dongcuoi) = "=SUMPRODUCT('" & shCat & "'!$BB$3:$BB$1433,('" & shTonghopCat & "'!$V4=DAY('" & shCat & "'!$B$3:$B$1433))*('" & shCat & "'!$C$3:$C$1433='" & shTonghopCat & "'!$W$4))"
            .Range("AH4:AH" & dongcuoi) = "=SUMPRODUCT('" & shCat & "'!$X$3:$X$1433,('" & shTonghopCat & "'!$V4=DAY('" & shCat & "'!$B$3:$B$1433))*('" & shCat & "'!$C$3:$C$1433='" & shTonghopCat & "'!$W$4))"
            .Range("AF4:AH" & dongcuoi).Value = .Range("AF4:AH" & dongcuoi).Value
            .Range("AK4:AK" & dongcuoi) = "=SUMPRODUCT('" & shCat & "'!$AS$3:$AS$1433,('" & shTonghopCat & "'!$V4=DAY('" & shCat & "'!$B$3:$B$1433))*('" & shCat & "'!$C$3:$C$1433='" & shTonghopCat & "'!$W$4))"
            .Range("AL4:AL" & dongcuoi) = "=SUMPRODUCT('" & shCat & "'!$AR$3:$AR$1433,('" & shTonghopCat & "'!$V4=DAY('" & shCat & "'!$B$3:$B$1433))*('" & shCat & "'!$C$3:$C$1433='" & shTonghopCat & "'!$W$4))"
            .Range("AM4:AM" & dongcuoi) = "=SUMPRODUCT('" & shCat & "'!$AY$3:$AY$1433,('" & shTonghopCat & "'!$V4=DAY('" & shCat & "'!$B$3:$B$1433))*('" & shCat & "'!$C$3:$C$1433='" & shTonghopCat & "'!$W$4))"
            .Range("AK4:AM" & dongcuoi).Value = .Range("AK4:AM" & dongcuoi).Value
        End If
    End With
   
    With Sheets(shCat)
        dongcuoi = .Cells(.Rows.Count, "A").End(xlUp).Row
        If dongcuoi > 2 Then
            .Range("X3:Y" & dongcuoi).ClearContents
            .Range("X3").FormulaArray = "=VLOOKUP(D3&E3,CHOOSE({1,2},'" & shDinhMuc & "'!$G$3:$G$9212&'" & shDinhMuc & "'!$B$3:$B$9212,'" & shDinhMuc & "'!$H$3:$H$9212),2,0)"
            .Range("X3").Copy .Range("X4:X" & dongcuoi)
            .Range("Y3").FormulaArray = "=VLOOKUP(D3&E3,CHOOSE({1,2},'" & shDinhMuc & "'!$G$3:$G$9212&'" & shDinhMuc & "'!$B$3:$B$9212,'" & shDinhMuc & "'!$O$3:$O$9212),2,0)"
            .Range("Y3").Copy .Range("Y4:Y" & dongcuoi)
            .Range("X3:Y" & dongcuoi).Value = .Range("X3:Y" & dongcuoi).Value
        End If
    End With

    With Sheets(shTongHop)
        dongcuoi = .Cells(.Rows.Count, "A").End(xlUp).Row
        '..tuong tu
    End With
   
    With Sheets(shTonghopCat)
        dongcuoi = .Cells(.Rows.Count, "A").End(xlUp).Row
        '..tuong tu
    End With
   
Loi:
    Application.ScreenUpdating = True
    If Err Then MsgBox Err.Description, vbCritical + vbOKOnly
   
End Sub
Nhấp chuột vào đây để mở rộng...
Phần TH Thổi và TH Cắt chưa được bạn ạ.
 
Lần chỉnh sửa cuối:
Upvote 0
mitdacdtd đã viết:
Cảm ơn bạn rất nhiều, mình kiểm tra lại đã nhé.
Bài đã được tự động gộp:


Phần TH Thổi và TH Cắt chưa được bạn ạ.
Nhấp chuột vào đây để mở rộng...

Hi, 2 sheet đó OT không làm nên đã ghi là tương tự.
Nếu bạn không thể bổ sung được lúc nào ngồi máy OT xử lý nốt giúp bạn.
 
Upvote 0
Nguyễn Hoàng Oanh Thơ đã viết:
Hi, 2 sheet đó OT không làm nên đã ghi là tương tự.
Nếu bạn không thể bổ sung được lúc nào ngồi máy OT xử lý nốt giúp bạn.
Nhấp chuột vào đây để mở rộng...
Giúp mình toàn bộ được không OT, mình dốt cái này lắm. Với lại đoạn mã này:
.Range("C4:C" & dongcuoi) = "=SUMPRODUCT('" & shCat & "'!$AB$3:$AB$1433,('" & shTonghopCat & "'!$A7=DAY('" & shCat & "'!$B$3:$B$1433))*('" & shCat & "'!$C$3:$C$1433='" & shTonghopCat & "'!$B$4))"
.Range("D4:D" & dongcuoi) = "=SUMPRODUCT('" & shCat & "'!$BB$3:$BB$1433,('" & shTonghopCat & "'!$A7=DAY('" & shCat & "'!$B$3:$B$1433))*('" & shCat & "'!$C$3:$C$1433='" & shTonghopCat & "'!$B$4))"
.Range("E4:E" & dongcuoi) = "=SUMPRODUCT('" & shCat & "'!$X$3:$X$1433,('" & shTonghopCat & "'!$A7=DAY('" & shCat & "'!$B$3:$B$1433))*('" & shCat & "'!$C$3:$C$1433='" & shTonghopCat & "'!$B$4))"
Chỗ A7 phải đổi thành A4 mới đúng. Còn phần định mức đang lấy ở sheet đ.mức dịch
 
Upvote 0
Bạn thử & kiểm tra nhé:
Mã: 
Option Explicit

Sub Tinhtoan()

    Dim dongcuoi As Long
    Dim shThoi As String, shCat As String, shDinhMuc As String
    Dim shTonghopThoi As String, shTonghopCat As String, shTongHop As String
    
    shThoi = "TH" & ChrW(7892) & "I"
    shCat = "C" & ChrW(7854) & "T"
    shDinhMuc = ChrW(273) & ".m" & ChrW(7913) & "c d" & ChrW(7883) & "ch"
    shTonghopThoi = "TH TH" & ChrW(7892) & "I"
    shTonghopCat = "TH C" & ChrW(7854) & "T"
    shTongHop = "T" & ChrW(7893) & "ng h" & ChrW(7907) & "p"
    
    Application.ScreenUpdating = False
    
    On Error GoTo Loi
    
    With Sheets(shTongHop)
        dongcuoi = .Cells(.Rows.Count, "A").End(xlUp).Row
        If dongcuoi > 3 Then
            .Range("C4:C" & dongcuoi) = "=SUMPRODUCT('" & shCat & "'!$AB$3:$AB$1433,('" & shTonghopCat & "'!$A4=DAY('" & shCat & "'!$B$3:$B$1433))*('" & shCat & "'!$C$3:$C$1433='" & shTonghopCat & "'!$B$4))"
            .Range("D4:D" & dongcuoi) = "=SUMPRODUCT('" & shCat & "'!$BB$3:$BB$1433,('" & shTonghopCat & "'!$A4=DAY('" & shCat & "'!$B$3:$B$1433))*('" & shCat & "'!$C$3:$C$1433='" & shTonghopCat & "'!$B$4))"
            .Range("E4:E" & dongcuoi) = "=SUMPRODUCT('" & shCat & "'!$X$3:$X$1433,('" & shTonghopCat & "'!$A4=DAY('" & shCat & "'!$B$3:$B$1433))*('" & shCat & "'!$C$3:$C$1433='" & shTonghopCat & "'!$B$4))"
            .Range("C4:E" & dongcuoi).Value = .Range("C4:E" & dongcuoi).Value
            .Range("H4:H" & dongcuoi) = "=SUMPRODUCT('" & shCat & "'!$AS$3:$AS$1433,('" & shTonghopCat & "'!$A4=DAY('" & shCat & "'!$B$3:$B$1433))*('" & shCat & "'!$C$3:$C$1433='" & shTonghopCat & "'!$B4))"
            .Range("I4:I" & dongcuoi) = "=SUMPRODUCT('" & shCat & "'!$AR$3:$AR$1433,('" & shTonghopCat & "'!$A4=DAY('" & shCat & "'!$B$3:$B$1433))*('" & shCat & "'!$C$3:$C$1433='" & shTonghopCat & "'!$B4))"
            .Range("J4:J" & dongcuoi) = "=SUMPRODUCT('" & shCat & "'!$AY$3:$AY$1433,('" & shTonghopCat & "'!$A4=DAY('" & shCat & "'!$B$3:$B$1433))*('" & shCat & "'!$C$3:$C$1433='" & shTonghopCat & "'!$B4))"
            .Range("H4:J" & dongcuoi).Value = .Range("H4:J" & dongcuoi).Value
            .Range("AF4:AF" & dongcuoi) = "=SUMPRODUCT('" & shCat & "'!$AB$3:$AB$1433,('" & shTonghopCat & "'!$V4=DAY('" & shCat & "'!$B$3:$B$1433))*('" & shCat & "'!$C$3:$C$1433='" & shTonghopCat & "'!$W$4))"
            .Range("AG4:AG" & dongcuoi) = "=SUMPRODUCT('" & shCat & "'!$BB$3:$BB$1433,('" & shTonghopCat & "'!$V4=DAY('" & shCat & "'!$B$3:$B$1433))*('" & shCat & "'!$C$3:$C$1433='" & shTonghopCat & "'!$W$4))"
            .Range("AH4:AH" & dongcuoi) = "=SUMPRODUCT('" & shCat & "'!$X$3:$X$1433,('" & shTonghopCat & "'!$V4=DAY('" & shCat & "'!$B$3:$B$1433))*('" & shCat & "'!$C$3:$C$1433='" & shTonghopCat & "'!$W$4))"
            .Range("AF4:AH" & dongcuoi).Value = .Range("AF4:AH" & dongcuoi).Value
            .Range("AK4:AK" & dongcuoi) = "=SUMPRODUCT('" & shCat & "'!$AS$3:$AS$1433,('" & shTonghopCat & "'!$V4=DAY('" & shCat & "'!$B$3:$B$1433))*('" & shCat & "'!$C$3:$C$1433='" & shTonghopCat & "'!$W$4))"
            .Range("AL4:AL" & dongcuoi) = "=SUMPRODUCT('" & shCat & "'!$AR$3:$AR$1433,('" & shTonghopCat & "'!$V4=DAY('" & shCat & "'!$B$3:$B$1433))*('" & shCat & "'!$C$3:$C$1433='" & shTonghopCat & "'!$W$4))"
            .Range("AM4:AM" & dongcuoi) = "=SUMPRODUCT('" & shCat & "'!$AY$3:$AY$1433,('" & shTonghopCat & "'!$V4=DAY('" & shCat & "'!$B$3:$B$1433))*('" & shCat & "'!$C$3:$C$1433='" & shTonghopCat & "'!$W$4))"
            .Range("AK4:AM" & dongcuoi).Value = .Range("AK4:AM" & dongcuoi).Value
        End If
    End With
    
    With Sheets(shCat)
        dongcuoi = .Cells(.Rows.Count, "A").End(xlUp).Row
        If dongcuoi > 2 Then
            .Range("X3:Y" & dongcuoi).ClearContents
            .Range("X3").FormulaArray = "=VLOOKUP(D3&E3,CHOOSE({1,2},'" & shDinhMuc & "'!$G$3:$G$9212&'" & shDinhMuc & "'!$B$3:$B$9212,'" & shDinhMuc & "'!$H$3:$H$9212),2,0)"
            .Range("X3").Copy .Range("X4:X" & dongcuoi)
            .Range("Y3").FormulaArray = "=VLOOKUP(D3&E3,CHOOSE({1,2},'" & shDinhMuc & "'!$G$3:$G$9212&'" & shDinhMuc & "'!$B$3:$B$9212,'" & shDinhMuc & "'!$O$3:$O$9212),2,0)"
            .Range("Y3").Copy .Range("Y4:Y" & dongcuoi)
            .Range("X3:Y" & dongcuoi).Value = .Range("X3:Y" & dongcuoi).Value
        End If
    End With

    With Sheets(shTonghopThoi)
        dongcuoi = .Cells(.Rows.Count, "A").End(xlUp).Row
        With .Range("D4:D" & dongcuoi)
            .Offset(, 0) = "=SUMPRODUCT('" & shThoi & "'!$AF$4:$AF$1311,($A4=DAY('" & shThoi & "'!$A$4:$A$1311))*('" & shThoi & "'!$B$4:$B$1311=$B$4))"
            .Offset(, 2) = "=SUMPRODUCT('" & shThoi & "'!$AJ$4:$AJ$1942,($A4=DAY('" & shThoi & "'!$A$4:$A$1942))*('" & shThoi & "'!$B$4:$B$1942=$B$4))"
            .Offset(, 3) = "=SUMPRODUCT('" & shThoi & "'!$AG$4:$AG$1942,($A4=DAY('" & shThoi & "'!$A$4:$A$1942))*('" & shThoi & "'!$B$4:$B$1942=$B$4))"
            .Offset(, 4) = "=SUMPRODUCT('" & shThoi & "'!$AH$4:$AH$1942,($A4=DAY('" & shThoi & "'!$A$4:$A$1942))*('" & shThoi & "'!$B$4:$B$1942=$B$4))"
            .Offset(, 5) = "=SUMPRODUCT($DK$5:$DK$19,($CK$6=DAY($DJ$5:$DJ$19))*($CL$4=$DK$3))"
            .Offset(, 6) = "=SUMPRODUCT('" & shThoi & "'!$AX$4:$AX$133,($A4=DAY('" & shThoi & "'!$A$4:$A$133))*('" & shThoi & "'!$B$4:$B$133=$B$4))"
            .Offset(, 14) = "=SUMPRODUCT('" & shThoi & "'!$AF$4:$AF$1311,($A4=DAY('" & shThoi & "'!$A$4:$A$1311))*('" & shThoi & "'!$B$4:$B$1311=$O$4))"
            .Offset(, 16) = "=SUMPRODUCT('" & shThoi & "'!$AJ$4:$AJ$1311,($A4=DAY('" & shThoi & "'!$A$4:$A$1311))*('" & shThoi & "'!$B$4:$B$1311=$O$4))"
            .Offset(, 17) = "=SUMPRODUCT('" & shThoi & "'!$AG$4:$AG$1311,($A4=DAY('" & shThoi & "'!$A$4:$A$1311))*('" & shThoi & "'!$B$4:$B$1311=$O$4))"
            .Offset(, 18) = "=SUMPRODUCT('" & shThoi & "'!$AH$4:$AH$133,($A4=DAY('" & shThoi & "'!$A$4:$A$133))*('" & shThoi & "'!$B$4:$B$133=$O$4))"
            .Offset(, 19) = "=SUMPRODUCT('" & shThoi & "'!$AI$4:$AI$133,($A4=DAY('" & shThoi & "'!$A$4:$A$133))*('" & shThoi & "'!$B$4:$B$133=$O$4))"
            .Offset(, 20) = "=SUMPRODUCT('" & shThoi & "'!$AX$4:$AX$133,($A4=DAY('" & shThoi & "'!$A$4:$A$133))*('" & shThoi & "'!$B$4:$B$133=$O$4))"
            .Offset(, 21) = "=SUMPRODUCT('" & shThoi & "'!$AP$4:$AP$628,($A4=DAY('" & shThoi & "'!$A$4:$A$628))*('" & shThoi & "'!$B$4:$B$628=$O$4))"
            .Offset(, 0).Value = .Offset(, 0).Value: .Offset(, 2).Resize(, 6).Value = .Offset(, 2).Resize(, 6).Value
            .Offset(, 14).Value = .Offset(, 14).Value: .Offset(, 16).Resize(, 7).Value = .Offset(, 16).Resize(, 7).Value
        End With
    End With
    
    With Sheets(shTonghopCat)
        dongcuoi = .Cells(.Rows.Count, "A").End(xlUp).Row
        With .Range("E4:E" & dongcuoi)
            .Offset(, 0) = "=SUMPRODUCT('" & shCat & "'!$AB$3:$AB$1433,($A4=DAY('" & shCat & "'!$B$3:$B$1433))*('" & shCat & "'!$C$3:$C$1433=$B$4))"
            .Offset(, 4) = "=SUMPRODUCT('" & shCat & "'!$AR$3:$AR$1433,($A4=DAY('" & shCat & "'!$B$3:$B$1433))*('" & shCat & "'!$C$3:$C$1433=$B4))"
            .Offset(, 5) = "=SUMPRODUCT('" & shCat & "'!$AS$3:$AS$1433,($A4=DAY('" & shCat & "'!$B$3:$B$1433))*('" & shCat & "'!$C$3:$C$1433=$B4))"
            .Offset(, 6) = "=SUMPRODUCT('" & shCat & "'!$AY$3:$AY$3002,($A4=DAY('" & shCat & "'!$B$3:$B$3002))*('" & shCat & "'!$C$3:$C$3002=$B4))"
            .Offset(, 7) = "=SUMPRODUCT('" & shCat & "'!$AU$3:$AU$3002,($A4=DAY('" & shCat & "'!$B$3:$B$3002))*('" & shCat & "'!$C$3:$C$3002=$B4))"
            .Offset(, 8) = "=SUMPRODUCT('" & shCat & "'!$AT$3:$AT$3002,($A4=DAY('" & shCat & "'!$B$3:$B$3002))*('" & shCat & "'!$C$3:$C$3002=$B4))"
            .Offset(, 20) = "=SUMPRODUCT('" & shCat & "'!$AB$3:$AB$1433,($V4=DAY('" & shCat & "'!$B$3:$B$1433))*('" & shCat & "'!$C$3:$C$1433=$W$4))"
            .Offset(, 24) = "=SUMPRODUCT('" & shCat & "'!$AR$3:$AR$3002,($V4=DAY('" & shCat & "'!$B$3:$B$3002))*('" & shCat & "'!$C$3:$C$3002=$W$4))"
            .Offset(, 25) = "=SUMPRODUCT('" & shCat & "'!$AS$3:$AS$1433,($V4=DAY('" & shCat & "'!$B$3:$B$1433))*('" & shCat & "'!$C$3:$C$1433=$W$4))"
            .Offset(, 26) = "=SUMPRODUCT('" & shCat & "'!$AY$3:$AY$1433,($V4=DAY('" & shCat & "'!$B$3:$B$1433))*('" & shCat & "'!$C$3:$C$1433=$W$4))"
            .Offset(, 0).Value = .Offset(, 0).Value:    .Offset(, 4).Resize(, 5).Value = .Offset(, 4).Resize(, 5).Value
            .Offset(, 20).Value = .Offset(, 20).Value:  .Offset(, 24).Resize(, 3).Value = .Offset(, 24).Resize(, 3).Value
        End With
    End With
    
Loi:
    Application.ScreenUpdating = True
    If Err Then MsgBox Err.Description, vbCritical + vbOKOnly
    
End Sub
 
Upvote 0
Nguyễn Hoàng Oanh Thơ đã viết:
Bạn thử & kiểm tra nhé:
Mã: 
Option Explicit

Sub Tinhtoan()

    Dim dongcuoi As Long
    Dim shThoi As String, shCat As String, shDinhMuc As String
    Dim shTonghopThoi As String, shTonghopCat As String, shTongHop As String
   
    shThoi = "TH" & ChrW(7892) & "I"
    shCat = "C" & ChrW(7854) & "T"
    shDinhMuc = ChrW(273) & ".m" & ChrW(7913) & "c d" & ChrW(7883) & "ch"
    shTonghopThoi = "TH TH" & ChrW(7892) & "I"
    shTonghopCat = "TH C" & ChrW(7854) & "T"
    shTongHop = "T" & ChrW(7893) & "ng h" & ChrW(7907) & "p"
   
    Application.ScreenUpdating = False
   
    On Error GoTo Loi
   
    With Sheets(shTongHop)
        dongcuoi = .Cells(.Rows.Count, "A").End(xlUp).Row
        If dongcuoi > 3 Then
            .Range("C4:C" & dongcuoi) = "=SUMPRODUCT('" & shCat & "'!$AB$3:$AB$1433,('" & shTonghopCat & "'!$A4=DAY('" & shCat & "'!$B$3:$B$1433))*('" & shCat & "'!$C$3:$C$1433='" & shTonghopCat & "'!$B$4))"
            .Range("D4:D" & dongcuoi) = "=SUMPRODUCT('" & shCat & "'!$BB$3:$BB$1433,('" & shTonghopCat & "'!$A4=DAY('" & shCat & "'!$B$3:$B$1433))*('" & shCat & "'!$C$3:$C$1433='" & shTonghopCat & "'!$B$4))"
            .Range("E4:E" & dongcuoi) = "=SUMPRODUCT('" & shCat & "'!$X$3:$X$1433,('" & shTonghopCat & "'!$A4=DAY('" & shCat & "'!$B$3:$B$1433))*('" & shCat & "'!$C$3:$C$1433='" & shTonghopCat & "'!$B$4))"
            .Range("C4:E" & dongcuoi).Value = .Range("C4:E" & dongcuoi).Value
            .Range("H4:H" & dongcuoi) = "=SUMPRODUCT('" & shCat & "'!$AS$3:$AS$1433,('" & shTonghopCat & "'!$A4=DAY('" & shCat & "'!$B$3:$B$1433))*('" & shCat & "'!$C$3:$C$1433='" & shTonghopCat & "'!$B4))"
            .Range("I4:I" & dongcuoi) = "=SUMPRODUCT('" & shCat & "'!$AR$3:$AR$1433,('" & shTonghopCat & "'!$A4=DAY('" & shCat & "'!$B$3:$B$1433))*('" & shCat & "'!$C$3:$C$1433='" & shTonghopCat & "'!$B4))"
            .Range("J4:J" & dongcuoi) = "=SUMPRODUCT('" & shCat & "'!$AY$3:$AY$1433,('" & shTonghopCat & "'!$A4=DAY('" & shCat & "'!$B$3:$B$1433))*('" & shCat & "'!$C$3:$C$1433='" & shTonghopCat & "'!$B4))"
            .Range("H4:J" & dongcuoi).Value = .Range("H4:J" & dongcuoi).Value
            .Range("AF4:AF" & dongcuoi) = "=SUMPRODUCT('" & shCat & "'!$AB$3:$AB$1433,('" & shTonghopCat & "'!$V4=DAY('" & shCat & "'!$B$3:$B$1433))*('" & shCat & "'!$C$3:$C$1433='" & shTonghopCat & "'!$W$4))"
            .Range("AG4:AG" & dongcuoi) = "=SUMPRODUCT('" & shCat & "'!$BB$3:$BB$1433,('" & shTonghopCat & "'!$V4=DAY('" & shCat & "'!$B$3:$B$1433))*('" & shCat & "'!$C$3:$C$1433='" & shTonghopCat & "'!$W$4))"
            .Range("AH4:AH" & dongcuoi) = "=SUMPRODUCT('" & shCat & "'!$X$3:$X$1433,('" & shTonghopCat & "'!$V4=DAY('" & shCat & "'!$B$3:$B$1433))*('" & shCat & "'!$C$3:$C$1433='" & shTonghopCat & "'!$W$4))"
            .Range("AF4:AH" & dongcuoi).Value = .Range("AF4:AH" & dongcuoi).Value
            .Range("AK4:AK" & dongcuoi) = "=SUMPRODUCT('" & shCat & "'!$AS$3:$AS$1433,('" & shTonghopCat & "'!$V4=DAY('" & shCat & "'!$B$3:$B$1433))*('" & shCat & "'!$C$3:$C$1433='" & shTonghopCat & "'!$W$4))"
            .Range("AL4:AL" & dongcuoi) = "=SUMPRODUCT('" & shCat & "'!$AR$3:$AR$1433,('" & shTonghopCat & "'!$V4=DAY('" & shCat & "'!$B$3:$B$1433))*('" & shCat & "'!$C$3:$C$1433='" & shTonghopCat & "'!$W$4))"
            .Range("AM4:AM" & dongcuoi) = "=SUMPRODUCT('" & shCat & "'!$AY$3:$AY$1433,('" & shTonghopCat & "'!$V4=DAY('" & shCat & "'!$B$3:$B$1433))*('" & shCat & "'!$C$3:$C$1433='" & shTonghopCat & "'!$W$4))"
            .Range("AK4:AM" & dongcuoi).Value = .Range("AK4:AM" & dongcuoi).Value
        End If
    End With
   
    With Sheets(shCat)
        dongcuoi = .Cells(.Rows.Count, "A").End(xlUp).Row
        If dongcuoi > 2 Then
            .Range("X3:Y" & dongcuoi).ClearContents
            .Range("X3").FormulaArray = "=VLOOKUP(D3&E3,CHOOSE({1,2},'" & shDinhMuc & "'!$G$3:$G$9212&'" & shDinhMuc & "'!$B$3:$B$9212,'" & shDinhMuc & "'!$H$3:$H$9212),2,0)"
            .Range("X3").Copy .Range("X4:X" & dongcuoi)
            .Range("Y3").FormulaArray = "=VLOOKUP(D3&E3,CHOOSE({1,2},'" & shDinhMuc & "'!$G$3:$G$9212&'" & shDinhMuc & "'!$B$3:$B$9212,'" & shDinhMuc & "'!$O$3:$O$9212),2,0)"
            .Range("Y3").Copy .Range("Y4:Y" & dongcuoi)
            .Range("X3:Y" & dongcuoi).Value = .Range("X3:Y" & dongcuoi).Value
        End If
    End With

    With Sheets(shTonghopThoi)
        dongcuoi = .Cells(.Rows.Count, "A").End(xlUp).Row
        With .Range("D4:D" & dongcuoi)
            .Offset(, 0) = "=SUMPRODUCT('" & shThoi & "'!$AF$4:$AF$1311,($A4=DAY('" & shThoi & "'!$A$4:$A$1311))*('" & shThoi & "'!$B$4:$B$1311=$B$4))"
            .Offset(, 2) = "=SUMPRODUCT('" & shThoi & "'!$AJ$4:$AJ$1942,($A4=DAY('" & shThoi & "'!$A$4:$A$1942))*('" & shThoi & "'!$B$4:$B$1942=$B$4))"
            .Offset(, 3) = "=SUMPRODUCT('" & shThoi & "'!$AG$4:$AG$1942,($A4=DAY('" & shThoi & "'!$A$4:$A$1942))*('" & shThoi & "'!$B$4:$B$1942=$B$4))"
            .Offset(, 4) = "=SUMPRODUCT('" & shThoi & "'!$AH$4:$AH$1942,($A4=DAY('" & shThoi & "'!$A$4:$A$1942))*('" & shThoi & "'!$B$4:$B$1942=$B$4))"
            .Offset(, 5) = "=SUMPRODUCT($DK$5:$DK$19,($CK$6=DAY($DJ$5:$DJ$19))*($CL$4=$DK$3))"
            .Offset(, 6) = "=SUMPRODUCT('" & shThoi & "'!$AX$4:$AX$133,($A4=DAY('" & shThoi & "'!$A$4:$A$133))*('" & shThoi & "'!$B$4:$B$133=$B$4))"
            .Offset(, 14) = "=SUMPRODUCT('" & shThoi & "'!$AF$4:$AF$1311,($A4=DAY('" & shThoi & "'!$A$4:$A$1311))*('" & shThoi & "'!$B$4:$B$1311=$O$4))"
            .Offset(, 16) = "=SUMPRODUCT('" & shThoi & "'!$AJ$4:$AJ$1311,($A4=DAY('" & shThoi & "'!$A$4:$A$1311))*('" & shThoi & "'!$B$4:$B$1311=$O$4))"
            .Offset(, 17) = "=SUMPRODUCT('" & shThoi & "'!$AG$4:$AG$1311,($A4=DAY('" & shThoi & "'!$A$4:$A$1311))*('" & shThoi & "'!$B$4:$B$1311=$O$4))"
            .Offset(, 18) = "=SUMPRODUCT('" & shThoi & "'!$AH$4:$AH$133,($A4=DAY('" & shThoi & "'!$A$4:$A$133))*('" & shThoi & "'!$B$4:$B$133=$O$4))"
            .Offset(, 19) = "=SUMPRODUCT('" & shThoi & "'!$AI$4:$AI$133,($A4=DAY('" & shThoi & "'!$A$4:$A$133))*('" & shThoi & "'!$B$4:$B$133=$O$4))"
            .Offset(, 20) = "=SUMPRODUCT('" & shThoi & "'!$AX$4:$AX$133,($A4=DAY('" & shThoi & "'!$A$4:$A$133))*('" & shThoi & "'!$B$4:$B$133=$O$4))"
            .Offset(, 21) = "=SUMPRODUCT('" & shThoi & "'!$AP$4:$AP$628,($A4=DAY('" & shThoi & "'!$A$4:$A$628))*('" & shThoi & "'!$B$4:$B$628=$O$4))"
            .Offset(, 0).Value = .Offset(, 0).Value: .Offset(, 2).Resize(, 6).Value = .Offset(, 2).Resize(, 6).Value
            .Offset(, 14).Value = .Offset(, 14).Value: .Offset(, 16).Resize(, 7).Value = .Offset(, 16).Resize(, 7).Value
        End With
    End With
   
    With Sheets(shTonghopCat)
        dongcuoi = .Cells(.Rows.Count, "A").End(xlUp).Row
        With .Range("E4:E" & dongcuoi)
            .Offset(, 0) = "=SUMPRODUCT('" & shCat & "'!$AB$3:$AB$1433,($A4=DAY('" & shCat & "'!$B$3:$B$1433))*('" & shCat & "'!$C$3:$C$1433=$B$4))"
            .Offset(, 4) = "=SUMPRODUCT('" & shCat & "'!$AR$3:$AR$1433,($A4=DAY('" & shCat & "'!$B$3:$B$1433))*('" & shCat & "'!$C$3:$C$1433=$B4))"
            .Offset(, 5) = "=SUMPRODUCT('" & shCat & "'!$AS$3:$AS$1433,($A4=DAY('" & shCat & "'!$B$3:$B$1433))*('" & shCat & "'!$C$3:$C$1433=$B4))"
            .Offset(, 6) = "=SUMPRODUCT('" & shCat & "'!$AY$3:$AY$3002,($A4=DAY('" & shCat & "'!$B$3:$B$3002))*('" & shCat & "'!$C$3:$C$3002=$B4))"
            .Offset(, 7) = "=SUMPRODUCT('" & shCat & "'!$AU$3:$AU$3002,($A4=DAY('" & shCat & "'!$B$3:$B$3002))*('" & shCat & "'!$C$3:$C$3002=$B4))"
            .Offset(, 8) = "=SUMPRODUCT('" & shCat & "'!$AT$3:$AT$3002,($A4=DAY('" & shCat & "'!$B$3:$B$3002))*('" & shCat & "'!$C$3:$C$3002=$B4))"
            .Offset(, 20) = "=SUMPRODUCT('" & shCat & "'!$AB$3:$AB$1433,($V4=DAY('" & shCat & "'!$B$3:$B$1433))*('" & shCat & "'!$C$3:$C$1433=$W$4))"
            .Offset(, 24) = "=SUMPRODUCT('" & shCat & "'!$AR$3:$AR$3002,($V4=DAY('" & shCat & "'!$B$3:$B$3002))*('" & shCat & "'!$C$3:$C$3002=$W$4))"
            .Offset(, 25) = "=SUMPRODUCT('" & shCat & "'!$AS$3:$AS$1433,($V4=DAY('" & shCat & "'!$B$3:$B$1433))*('" & shCat & "'!$C$3:$C$1433=$W$4))"
            .Offset(, 26) = "=SUMPRODUCT('" & shCat & "'!$AY$3:$AY$1433,($V4=DAY('" & shCat & "'!$B$3:$B$1433))*('" & shCat & "'!$C$3:$C$1433=$W$4))"
            .Offset(, 0).Value = .Offset(, 0).Value:    .Offset(, 4).Resize(, 5).Value = .Offset(, 4).Resize(, 5).Value
            .Offset(, 20).Value = .Offset(, 20).Value:  .Offset(, 24).Resize(, 3).Value = .Offset(, 24).Resize(, 3).Value
        End With
    End With
   
Loi:
    Application.ScreenUpdating = True
    If Err Then MsgBox Err.Description, vbCritical + vbOKOnly
   
End Sub
Nhấp chuột vào đây để mở rộng...
Code sao có hàm mảng ở đó vậy ta.Làm cái bỏ hết ct đi.
 
Upvote 0
Nguyễn Hoàng Oanh Thơ đã viết:
Bạn thử & kiểm tra nhé:
Mã: 
Option Explicit

Sub Tinhtoan()

    Dim dongcuoi As Long
    Dim shThoi As String, shCat As String, shDinhMuc As String
    Dim shTonghopThoi As String, shTonghopCat As String, shTongHop As String
   
    shThoi = "TH" & ChrW(7892) & "I"
    shCat = "C" & ChrW(7854) & "T"
    shDinhMuc = ChrW(273) & ".m" & ChrW(7913) & "c d" & ChrW(7883) & "ch"
    shTonghopThoi = "TH TH" & ChrW(7892) & "I"
    shTonghopCat = "TH C" & ChrW(7854) & "T"
    shTongHop = "T" & ChrW(7893) & "ng h" & ChrW(7907) & "p"
   
    Application.ScreenUpdating = False
   
    On Error GoTo Loi
   
    With Sheets(shTongHop)
        dongcuoi = .Cells(.Rows.Count, "A").End(xlUp).Row
        If dongcuoi > 3 Then
            .Range("C4:C" & dongcuoi) = "=SUMPRODUCT('" & shCat & "'!$AB$3:$AB$1433,('" & shTonghopCat & "'!$A4=DAY('" & shCat & "'!$B$3:$B$1433))*('" & shCat & "'!$C$3:$C$1433='" & shTonghopCat & "'!$B$4))"
            .Range("D4:D" & dongcuoi) = "=SUMPRODUCT('" & shCat & "'!$BB$3:$BB$1433,('" & shTonghopCat & "'!$A4=DAY('" & shCat & "'!$B$3:$B$1433))*('" & shCat & "'!$C$3:$C$1433='" & shTonghopCat & "'!$B$4))"
            .Range("E4:E" & dongcuoi) = "=SUMPRODUCT('" & shCat & "'!$X$3:$X$1433,('" & shTonghopCat & "'!$A4=DAY('" & shCat & "'!$B$3:$B$1433))*('" & shCat & "'!$C$3:$C$1433='" & shTonghopCat & "'!$B$4))"
            .Range("C4:E" & dongcuoi).Value = .Range("C4:E" & dongcuoi).Value
            .Range("H4:H" & dongcuoi) = "=SUMPRODUCT('" & shCat & "'!$AS$3:$AS$1433,('" & shTonghopCat & "'!$A4=DAY('" & shCat & "'!$B$3:$B$1433))*('" & shCat & "'!$C$3:$C$1433='" & shTonghopCat & "'!$B4))"
            .Range("I4:I" & dongcuoi) = "=SUMPRODUCT('" & shCat & "'!$AR$3:$AR$1433,('" & shTonghopCat & "'!$A4=DAY('" & shCat & "'!$B$3:$B$1433))*('" & shCat & "'!$C$3:$C$1433='" & shTonghopCat & "'!$B4))"
            .Range("J4:J" & dongcuoi) = "=SUMPRODUCT('" & shCat & "'!$AY$3:$AY$1433,('" & shTonghopCat & "'!$A4=DAY('" & shCat & "'!$B$3:$B$1433))*('" & shCat & "'!$C$3:$C$1433='" & shTonghopCat & "'!$B4))"
            .Range("H4:J" & dongcuoi).Value = .Range("H4:J" & dongcuoi).Value
            .Range("AF4:AF" & dongcuoi) = "=SUMPRODUCT('" & shCat & "'!$AB$3:$AB$1433,('" & shTonghopCat & "'!$V4=DAY('" & shCat & "'!$B$3:$B$1433))*('" & shCat & "'!$C$3:$C$1433='" & shTonghopCat & "'!$W$4))"
            .Range("AG4:AG" & dongcuoi) = "=SUMPRODUCT('" & shCat & "'!$BB$3:$BB$1433,('" & shTonghopCat & "'!$V4=DAY('" & shCat & "'!$B$3:$B$1433))*('" & shCat & "'!$C$3:$C$1433='" & shTonghopCat & "'!$W$4))"
            .Range("AH4:AH" & dongcuoi) = "=SUMPRODUCT('" & shCat & "'!$X$3:$X$1433,('" & shTonghopCat & "'!$V4=DAY('" & shCat & "'!$B$3:$B$1433))*('" & shCat & "'!$C$3:$C$1433='" & shTonghopCat & "'!$W$4))"
            .Range("AF4:AH" & dongcuoi).Value = .Range("AF4:AH" & dongcuoi).Value
            .Range("AK4:AK" & dongcuoi) = "=SUMPRODUCT('" & shCat & "'!$AS$3:$AS$1433,('" & shTonghopCat & "'!$V4=DAY('" & shCat & "'!$B$3:$B$1433))*('" & shCat & "'!$C$3:$C$1433='" & shTonghopCat & "'!$W$4))"
            .Range("AL4:AL" & dongcuoi) = "=SUMPRODUCT('" & shCat & "'!$AR$3:$AR$1433,('" & shTonghopCat & "'!$V4=DAY('" & shCat & "'!$B$3:$B$1433))*('" & shCat & "'!$C$3:$C$1433='" & shTonghopCat & "'!$W$4))"
            .Range("AM4:AM" & dongcuoi) = "=SUMPRODUCT('" & shCat & "'!$AY$3:$AY$1433,('" & shTonghopCat & "'!$V4=DAY('" & shCat & "'!$B$3:$B$1433))*('" & shCat & "'!$C$3:$C$1433='" & shTonghopCat & "'!$W$4))"
            .Range("AK4:AM" & dongcuoi).Value = .Range("AK4:AM" & dongcuoi).Value
        End If
    End With
   
    With Sheets(shCat)
        dongcuoi = .Cells(.Rows.Count, "A").End(xlUp).Row
        If dongcuoi > 2 Then
            .Range("X3:Y" & dongcuoi).ClearContents
            .Range("X3").FormulaArray = "=VLOOKUP(D3&E3,CHOOSE({1,2},'" & shDinhMuc & "'!$G$3:$G$9212&'" & shDinhMuc & "'!$B$3:$B$9212,'" & shDinhMuc & "'!$H$3:$H$9212),2,0)"
            .Range("X3").Copy .Range("X4:X" & dongcuoi)
            .Range("Y3").FormulaArray = "=VLOOKUP(D3&E3,CHOOSE({1,2},'" & shDinhMuc & "'!$G$3:$G$9212&'" & shDinhMuc & "'!$B$3:$B$9212,'" & shDinhMuc & "'!$O$3:$O$9212),2,0)"
            .Range("Y3").Copy .Range("Y4:Y" & dongcuoi)
            .Range("X3:Y" & dongcuoi).Value = .Range("X3:Y" & dongcuoi).Value
        End If
    End With

    With Sheets(shTonghopThoi)
        dongcuoi = .Cells(.Rows.Count, "A").End(xlUp).Row
        With .Range("D4:D" & dongcuoi)
            .Offset(, 0) = "=SUMPRODUCT('" & shThoi & "'!$AF$4:$AF$1311,($A4=DAY('" & shThoi & "'!$A$4:$A$1311))*('" & shThoi & "'!$B$4:$B$1311=$B$4))"
            .Offset(, 2) = "=SUMPRODUCT('" & shThoi & "'!$AJ$4:$AJ$1942,($A4=DAY('" & shThoi & "'!$A$4:$A$1942))*('" & shThoi & "'!$B$4:$B$1942=$B$4))"
            .Offset(, 3) = "=SUMPRODUCT('" & shThoi & "'!$AG$4:$AG$1942,($A4=DAY('" & shThoi & "'!$A$4:$A$1942))*('" & shThoi & "'!$B$4:$B$1942=$B$4))"
            .Offset(, 4) = "=SUMPRODUCT('" & shThoi & "'!$AH$4:$AH$1942,($A4=DAY('" & shThoi & "'!$A$4:$A$1942))*('" & shThoi & "'!$B$4:$B$1942=$B$4))"
            .Offset(, 5) = "=SUMPRODUCT($DK$5:$DK$19,($CK$6=DAY($DJ$5:$DJ$19))*($CL$4=$DK$3))"
            .Offset(, 6) = "=SUMPRODUCT('" & shThoi & "'!$AX$4:$AX$133,($A4=DAY('" & shThoi & "'!$A$4:$A$133))*('" & shThoi & "'!$B$4:$B$133=$B$4))"
            .Offset(, 14) = "=SUMPRODUCT('" & shThoi & "'!$AF$4:$AF$1311,($A4=DAY('" & shThoi & "'!$A$4:$A$1311))*('" & shThoi & "'!$B$4:$B$1311=$O$4))"
            .Offset(, 16) = "=SUMPRODUCT('" & shThoi & "'!$AJ$4:$AJ$1311,($A4=DAY('" & shThoi & "'!$A$4:$A$1311))*('" & shThoi & "'!$B$4:$B$1311=$O$4))"
            .Offset(, 17) = "=SUMPRODUCT('" & shThoi & "'!$AG$4:$AG$1311,($A4=DAY('" & shThoi & "'!$A$4:$A$1311))*('" & shThoi & "'!$B$4:$B$1311=$O$4))"
            .Offset(, 18) = "=SUMPRODUCT('" & shThoi & "'!$AH$4:$AH$133,($A4=DAY('" & shThoi & "'!$A$4:$A$133))*('" & shThoi & "'!$B$4:$B$133=$O$4))"
            .Offset(, 19) = "=SUMPRODUCT('" & shThoi & "'!$AI$4:$AI$133,($A4=DAY('" & shThoi & "'!$A$4:$A$133))*('" & shThoi & "'!$B$4:$B$133=$O$4))"
            .Offset(, 20) = "=SUMPRODUCT('" & shThoi & "'!$AX$4:$AX$133,($A4=DAY('" & shThoi & "'!$A$4:$A$133))*('" & shThoi & "'!$B$4:$B$133=$O$4))"
            .Offset(, 21) = "=SUMPRODUCT('" & shThoi & "'!$AP$4:$AP$628,($A4=DAY('" & shThoi & "'!$A$4:$A$628))*('" & shThoi & "'!$B$4:$B$628=$O$4))"
            .Offset(, 0).Value = .Offset(, 0).Value: .Offset(, 2).Resize(, 6).Value = .Offset(, 2).Resize(, 6).Value
            .Offset(, 14).Value = .Offset(, 14).Value: .Offset(, 16).Resize(, 7).Value = .Offset(, 16).Resize(, 7).Value
        End With
    End With
   
    With Sheets(shTonghopCat)
        dongcuoi = .Cells(.Rows.Count, "A").End(xlUp).Row
        With .Range("E4:E" & dongcuoi)
            .Offset(, 0) = "=SUMPRODUCT('" & shCat & "'!$AB$3:$AB$1433,($A4=DAY('" & shCat & "'!$B$3:$B$1433))*('" & shCat & "'!$C$3:$C$1433=$B$4))"
            .Offset(, 4) = "=SUMPRODUCT('" & shCat & "'!$AR$3:$AR$1433,($A4=DAY('" & shCat & "'!$B$3:$B$1433))*('" & shCat & "'!$C$3:$C$1433=$B4))"
            .Offset(, 5) = "=SUMPRODUCT('" & shCat & "'!$AS$3:$AS$1433,($A4=DAY('" & shCat & "'!$B$3:$B$1433))*('" & shCat & "'!$C$3:$C$1433=$B4))"
            .Offset(, 6) = "=SUMPRODUCT('" & shCat & "'!$AY$3:$AY$3002,($A4=DAY('" & shCat & "'!$B$3:$B$3002))*('" & shCat & "'!$C$3:$C$3002=$B4))"
            .Offset(, 7) = "=SUMPRODUCT('" & shCat & "'!$AU$3:$AU$3002,($A4=DAY('" & shCat & "'!$B$3:$B$3002))*('" & shCat & "'!$C$3:$C$3002=$B4))"
            .Offset(, 8) = "=SUMPRODUCT('" & shCat & "'!$AT$3:$AT$3002,($A4=DAY('" & shCat & "'!$B$3:$B$3002))*('" & shCat & "'!$C$3:$C$3002=$B4))"
            .Offset(, 20) = "=SUMPRODUCT('" & shCat & "'!$AB$3:$AB$1433,($V4=DAY('" & shCat & "'!$B$3:$B$1433))*('" & shCat & "'!$C$3:$C$1433=$W$4))"
            .Offset(, 24) = "=SUMPRODUCT('" & shCat & "'!$AR$3:$AR$3002,($V4=DAY('" & shCat & "'!$B$3:$B$3002))*('" & shCat & "'!$C$3:$C$3002=$W$4))"
            .Offset(, 25) = "=SUMPRODUCT('" & shCat & "'!$AS$3:$AS$1433,($V4=DAY('" & shCat & "'!$B$3:$B$1433))*('" & shCat & "'!$C$3:$C$1433=$W$4))"
            .Offset(, 26) = "=SUMPRODUCT('" & shCat & "'!$AY$3:$AY$1433,($V4=DAY('" & shCat & "'!$B$3:$B$1433))*('" & shCat & "'!$C$3:$C$1433=$W$4))"
            .Offset(, 0).Value = .Offset(, 0).Value:    .Offset(, 4).Resize(, 5).Value = .Offset(, 4).Resize(, 5).Value
            .Offset(, 20).Value = .Offset(, 20).Value:  .Offset(, 24).Resize(, 3).Value = .Offset(, 24).Resize(, 3).Value
        End With
    End With
   
Loi:
    Application.ScreenUpdating = True
    If Err Then MsgBox Err.Description, vbCritical + vbOKOnly
   
End Sub
Nhấp chuột vào đây để mở rộng...
Làm VBA mà kết hợp công thức mai mốt có thêm thắt, sửa chữa cũng mệt lắm nha
 
Upvote 0
snow25 đã viết:
Code sao có hàm mảng ở đó vậy ta.Làm cái bỏ hết ct đi.
Nhấp chuột vào đây để mở rộng...
Bác giúp em với.
Bài đã được tự động gộp:

Nguyễn Hoàng Oanh Thơ đã viết:
Bạn thử & kiểm tra nhé:
Mã: 
Option Explicit

Sub Tinhtoan()

    Dim dongcuoi As Long
    Dim shThoi As String, shCat As String, shDinhMuc As String
    Dim shTonghopThoi As String, shTonghopCat As String, shTongHop As String
   
    shThoi = "TH" & ChrW(7892) & "I"
    shCat = "C" & ChrW(7854) & "T"
    shDinhMuc = ChrW(273) & ".m" & ChrW(7913) & "c d" & ChrW(7883) & "ch"
    shTonghopThoi = "TH TH" & ChrW(7892) & "I"
    shTonghopCat = "TH C" & ChrW(7854) & "T"
    shTongHop = "T" & ChrW(7893) & "ng h" & ChrW(7907) & "p"
   
    Application.ScreenUpdating = False
   
    On Error GoTo Loi
   
    With Sheets(shTongHop)
        dongcuoi = .Cells(.Rows.Count, "A").End(xlUp).Row
        If dongcuoi > 3 Then
            .Range("C4:C" & dongcuoi) = "=SUMPRODUCT('" & shCat & "'!$AB$3:$AB$1433,('" & shTonghopCat & "'!$A4=DAY('" & shCat & "'!$B$3:$B$1433))*('" & shCat & "'!$C$3:$C$1433='" & shTonghopCat & "'!$B$4))"
            .Range("D4:D" & dongcuoi) = "=SUMPRODUCT('" & shCat & "'!$BB$3:$BB$1433,('" & shTonghopCat & "'!$A4=DAY('" & shCat & "'!$B$3:$B$1433))*('" & shCat & "'!$C$3:$C$1433='" & shTonghopCat & "'!$B$4))"
            .Range("E4:E" & dongcuoi) = "=SUMPRODUCT('" & shCat & "'!$X$3:$X$1433,('" & shTonghopCat & "'!$A4=DAY('" & shCat & "'!$B$3:$B$1433))*('" & shCat & "'!$C$3:$C$1433='" & shTonghopCat & "'!$B$4))"
            .Range("C4:E" & dongcuoi).Value = .Range("C4:E" & dongcuoi).Value
            .Range("H4:H" & dongcuoi) = "=SUMPRODUCT('" & shCat & "'!$AS$3:$AS$1433,('" & shTonghopCat & "'!$A4=DAY('" & shCat & "'!$B$3:$B$1433))*('" & shCat & "'!$C$3:$C$1433='" & shTonghopCat & "'!$B4))"
            .Range("I4:I" & dongcuoi) = "=SUMPRODUCT('" & shCat & "'!$AR$3:$AR$1433,('" & shTonghopCat & "'!$A4=DAY('" & shCat & "'!$B$3:$B$1433))*('" & shCat & "'!$C$3:$C$1433='" & shTonghopCat & "'!$B4))"
            .Range("J4:J" & dongcuoi) = "=SUMPRODUCT('" & shCat & "'!$AY$3:$AY$1433,('" & shTonghopCat & "'!$A4=DAY('" & shCat & "'!$B$3:$B$1433))*('" & shCat & "'!$C$3:$C$1433='" & shTonghopCat & "'!$B4))"
            .Range("H4:J" & dongcuoi).Value = .Range("H4:J" & dongcuoi).Value
            .Range("AF4:AF" & dongcuoi) = "=SUMPRODUCT('" & shCat & "'!$AB$3:$AB$1433,('" & shTonghopCat & "'!$V4=DAY('" & shCat & "'!$B$3:$B$1433))*('" & shCat & "'!$C$3:$C$1433='" & shTonghopCat & "'!$W$4))"
            .Range("AG4:AG" & dongcuoi) = "=SUMPRODUCT('" & shCat & "'!$BB$3:$BB$1433,('" & shTonghopCat & "'!$V4=DAY('" & shCat & "'!$B$3:$B$1433))*('" & shCat & "'!$C$3:$C$1433='" & shTonghopCat & "'!$W$4))"
            .Range("AH4:AH" & dongcuoi) = "=SUMPRODUCT('" & shCat & "'!$X$3:$X$1433,('" & shTonghopCat & "'!$V4=DAY('" & shCat & "'!$B$3:$B$1433))*('" & shCat & "'!$C$3:$C$1433='" & shTonghopCat & "'!$W$4))"
            .Range("AF4:AH" & dongcuoi).Value = .Range("AF4:AH" & dongcuoi).Value
            .Range("AK4:AK" & dongcuoi) = "=SUMPRODUCT('" & shCat & "'!$AS$3:$AS$1433,('" & shTonghopCat & "'!$V4=DAY('" & shCat & "'!$B$3:$B$1433))*('" & shCat & "'!$C$3:$C$1433='" & shTonghopCat & "'!$W$4))"
            .Range("AL4:AL" & dongcuoi) = "=SUMPRODUCT('" & shCat & "'!$AR$3:$AR$1433,('" & shTonghopCat & "'!$V4=DAY('" & shCat & "'!$B$3:$B$1433))*('" & shCat & "'!$C$3:$C$1433='" & shTonghopCat & "'!$W$4))"
            .Range("AM4:AM" & dongcuoi) = "=SUMPRODUCT('" & shCat & "'!$AY$3:$AY$1433,('" & shTonghopCat & "'!$V4=DAY('" & shCat & "'!$B$3:$B$1433))*('" & shCat & "'!$C$3:$C$1433='" & shTonghopCat & "'!$W$4))"
            .Range("AK4:AM" & dongcuoi).Value = .Range("AK4:AM" & dongcuoi).Value
        End If
    End With
   
    With Sheets(shCat)
        dongcuoi = .Cells(.Rows.Count, "A").End(xlUp).Row
        If dongcuoi > 2 Then
            .Range("X3:Y" & dongcuoi).ClearContents
            .Range("X3").FormulaArray = "=VLOOKUP(D3&E3,CHOOSE({1,2},'" & shDinhMuc & "'!$G$3:$G$9212&'" & shDinhMuc & "'!$B$3:$B$9212,'" & shDinhMuc & "'!$H$3:$H$9212),2,0)"
            .Range("X3").Copy .Range("X4:X" & dongcuoi)
            .Range("Y3").FormulaArray = "=VLOOKUP(D3&E3,CHOOSE({1,2},'" & shDinhMuc & "'!$G$3:$G$9212&'" & shDinhMuc & "'!$B$3:$B$9212,'" & shDinhMuc & "'!$O$3:$O$9212),2,0)"
            .Range("Y3").Copy .Range("Y4:Y" & dongcuoi)
            .Range("X3:Y" & dongcuoi).Value = .Range("X3:Y" & dongcuoi).Value
        End If
    End With

    With Sheets(shTonghopThoi)
        dongcuoi = .Cells(.Rows.Count, "A").End(xlUp).Row
        With .Range("D4:D" & dongcuoi)
            .Offset(, 0) = "=SUMPRODUCT('" & shThoi & "'!$AF$4:$AF$1311,($A4=DAY('" & shThoi & "'!$A$4:$A$1311))*('" & shThoi & "'!$B$4:$B$1311=$B$4))"
            .Offset(, 2) = "=SUMPRODUCT('" & shThoi & "'!$AJ$4:$AJ$1942,($A4=DAY('" & shThoi & "'!$A$4:$A$1942))*('" & shThoi & "'!$B$4:$B$1942=$B$4))"
            .Offset(, 3) = "=SUMPRODUCT('" & shThoi & "'!$AG$4:$AG$1942,($A4=DAY('" & shThoi & "'!$A$4:$A$1942))*('" & shThoi & "'!$B$4:$B$1942=$B$4))"
            .Offset(, 4) = "=SUMPRODUCT('" & shThoi & "'!$AH$4:$AH$1942,($A4=DAY('" & shThoi & "'!$A$4:$A$1942))*('" & shThoi & "'!$B$4:$B$1942=$B$4))"
            .Offset(, 5) = "=SUMPRODUCT($DK$5:$DK$19,($CK$6=DAY($DJ$5:$DJ$19))*($CL$4=$DK$3))"
            .Offset(, 6) = "=SUMPRODUCT('" & shThoi & "'!$AX$4:$AX$133,($A4=DAY('" & shThoi & "'!$A$4:$A$133))*('" & shThoi & "'!$B$4:$B$133=$B$4))"
            .Offset(, 14) = "=SUMPRODUCT('" & shThoi & "'!$AF$4:$AF$1311,($A4=DAY('" & shThoi & "'!$A$4:$A$1311))*('" & shThoi & "'!$B$4:$B$1311=$O$4))"
            .Offset(, 16) = "=SUMPRODUCT('" & shThoi & "'!$AJ$4:$AJ$1311,($A4=DAY('" & shThoi & "'!$A$4:$A$1311))*('" & shThoi & "'!$B$4:$B$1311=$O$4))"
            .Offset(, 17) = "=SUMPRODUCT('" & shThoi & "'!$AG$4:$AG$1311,($A4=DAY('" & shThoi & "'!$A$4:$A$1311))*('" & shThoi & "'!$B$4:$B$1311=$O$4))"
            .Offset(, 18) = "=SUMPRODUCT('" & shThoi & "'!$AH$4:$AH$133,($A4=DAY('" & shThoi & "'!$A$4:$A$133))*('" & shThoi & "'!$B$4:$B$133=$O$4))"
            .Offset(, 19) = "=SUMPRODUCT('" & shThoi & "'!$AI$4:$AI$133,($A4=DAY('" & shThoi & "'!$A$4:$A$133))*('" & shThoi & "'!$B$4:$B$133=$O$4))"
            .Offset(, 20) = "=SUMPRODUCT('" & shThoi & "'!$AX$4:$AX$133,($A4=DAY('" & shThoi & "'!$A$4:$A$133))*('" & shThoi & "'!$B$4:$B$133=$O$4))"
            .Offset(, 21) = "=SUMPRODUCT('" & shThoi & "'!$AP$4:$AP$628,($A4=DAY('" & shThoi & "'!$A$4:$A$628))*('" & shThoi & "'!$B$4:$B$628=$O$4))"
            .Offset(, 0).Value = .Offset(, 0).Value: .Offset(, 2).Resize(, 6).Value = .Offset(, 2).Resize(, 6).Value
            .Offset(, 14).Value = .Offset(, 14).Value: .Offset(, 16).Resize(, 7).Value = .Offset(, 16).Resize(, 7).Value
        End With
    End With
   
    With Sheets(shTonghopCat)
        dongcuoi = .Cells(.Rows.Count, "A").End(xlUp).Row
        With .Range("E4:E" & dongcuoi)
            .Offset(, 0) = "=SUMPRODUCT('" & shCat & "'!$AB$3:$AB$1433,($A4=DAY('" & shCat & "'!$B$3:$B$1433))*('" & shCat & "'!$C$3:$C$1433=$B$4))"
            .Offset(, 4) = "=SUMPRODUCT('" & shCat & "'!$AR$3:$AR$1433,($A4=DAY('" & shCat & "'!$B$3:$B$1433))*('" & shCat & "'!$C$3:$C$1433=$B4))"
            .Offset(, 5) = "=SUMPRODUCT('" & shCat & "'!$AS$3:$AS$1433,($A4=DAY('" & shCat & "'!$B$3:$B$1433))*('" & shCat & "'!$C$3:$C$1433=$B4))"
            .Offset(, 6) = "=SUMPRODUCT('" & shCat & "'!$AY$3:$AY$3002,($A4=DAY('" & shCat & "'!$B$3:$B$3002))*('" & shCat & "'!$C$3:$C$3002=$B4))"
            .Offset(, 7) = "=SUMPRODUCT('" & shCat & "'!$AU$3:$AU$3002,($A4=DAY('" & shCat & "'!$B$3:$B$3002))*('" & shCat & "'!$C$3:$C$3002=$B4))"
            .Offset(, 8) = "=SUMPRODUCT('" & shCat & "'!$AT$3:$AT$3002,($A4=DAY('" & shCat & "'!$B$3:$B$3002))*('" & shCat & "'!$C$3:$C$3002=$B4))"
            .Offset(, 20) = "=SUMPRODUCT('" & shCat & "'!$AB$3:$AB$1433,($V4=DAY('" & shCat & "'!$B$3:$B$1433))*('" & shCat & "'!$C$3:$C$1433=$W$4))"
            .Offset(, 24) = "=SUMPRODUCT('" & shCat & "'!$AR$3:$AR$3002,($V4=DAY('" & shCat & "'!$B$3:$B$3002))*('" & shCat & "'!$C$3:$C$3002=$W$4))"
            .Offset(, 25) = "=SUMPRODUCT('" & shCat & "'!$AS$3:$AS$1433,($V4=DAY('" & shCat & "'!$B$3:$B$1433))*('" & shCat & "'!$C$3:$C$1433=$W$4))"
            .Offset(, 26) = "=SUMPRODUCT('" & shCat & "'!$AY$3:$AY$1433,($V4=DAY('" & shCat & "'!$B$3:$B$1433))*('" & shCat & "'!$C$3:$C$1433=$W$4))"
            .Offset(, 0).Value = .Offset(, 0).Value:    .Offset(, 4).Resize(, 5).Value = .Offset(, 4).Resize(, 5).Value
            .Offset(, 20).Value = .Offset(, 20).Value:  .Offset(, 24).Resize(, 3).Value = .Offset(, 24).Resize(, 3).Value
        End With
    End With
   
Loi:
    Application.ScreenUpdating = True
    If Err Then MsgBox Err.Description, vbCritical + vbOKOnly
   
End Sub
Nhấp chuột vào đây để mở rộng...
Mai mình kiểm tra lại nha OT. Nhưng cơ bản file vẫn rất nặng OT ạ.
 
Upvote 0

File đính kèm

  • 1557103169161.png
    1557103169161.png
    257.5 KB · Đọc: 11
  • 1557103182130.png
    1557103182130.png
    229.4 KB · Đọc: 11
Upvote 0
mitdacdtd đã viết:
OT ơi, phần định mức nó vẫn chưa ra số đúng theo file ảnh mình đính kèm ấy. Xem lại giúp mình với
Nhấp chuột vào đây để mở rộng...
Chào bạn, OT xem ảnh sheets "CẮT" ở cột X,Y của bạn bị lỗi số liệu giống nhau OT thấy máy của OT không bị như bạn.
Có thể máy tính của bạn đang ở chế độ Manual,

Ngay dòng đầu tiên của sub: nghĩa là sau dòng Sub Tinhtoan() , bạn thêm dòng này xem được không ạ:
Application.Calculation = xlCalculationAutomatic

Còn cột E của bạn ở sheets"Tổng hợp" ngay từ đầu bài 1 bạn gửi file công thức của bạn vẫn bị lỗi "#NA" mà.. OT không biết công thức đúng là gì nữa nên không dám sửa.

Toàn bộ công thức của bạn chỉ copy vào OT chỉ copy vào code thôi ạ.

-----------------

Hic,nhưng mà code kiểu dở dở ương ương thế này cũng không hơn ban đầu là mấy,
Bạn cố gắng giải thích rõ hơn cho anh chàng #14 hiểu được anh ấy sẽ code cho ạ,
code của anh ấy chạy rẹt loáng cái là xong :D
 
Lần chỉnh sửa cuối:
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

Mewwo98
Thay dấu ngăn cách sau chữ số hàng nghìn, hàng triệu, hàng tỷ trong Mail Merge
Trả lời
2
Đọc
45
ptm0412
ptm0412
ptm0412
  • Question Question
Lấy dữ liệu web nhiều trang về file Excel bằng Power Query
    • Thích
Trả lời
5
Đọc
2K
ptm0412
ptm0412
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
14 15 16
Trả lời
309
Đọc
23K
ongke0711
ongke0711
H
Công thức tìm kết quả
Trả lời
11
Đọc
104
VetMini
V
V
Hàm dò tìm lấy giá trị của lần xuất hiện thứ n trong excel.
Trả lời
9
Đọc
1K
VetMini
V
Huyen9
Lỗi không mailings được.
Trả lời
2
Đọc
48
Huyen9
Huyen9
N
Nhờ các anh chỉ chỉnh lỗi giúp em với ạ, em cảm ơn ạ.
Trả lời
3
Đọc
70
minhminh27
minhminh27
T
File quản lý thiết bị trong trường học!
Trả lời
4
Đọc
127
VetMini
V
Xoainon3005
Xin file mẫu kế hoạch sản xuất theo tháng ngành động cơ
Trả lời
1
Đọc
119
VetMini
V
Momominh
Sơ đồ hàng hóa trong Container
Trả lời
3
Đọc
117
VetMini
V
L
Viết phần mềm đánh lô bằng Visual Basic
    • Thích
2 3
Trả lời
51
Đọc
103K
taimeanh
T
hungtin1997
  • Question Question
[Hỏi] Tạo Hyperlink đến ô chạy ngang theo cột
Trả lời
6
Đọc
406
A HOANG 620
A
LuuAnh980
Add in tạo QR code miễn phí.
Trả lời
14
Đọc
546
htin1997
htin1997
tramcannt
Các bác cho hỏi làm cách nào để thu nhỏ thanh công thức này với ạ. Em cảm ơn.
Trả lời
2
Đọc
56
Nhattanktnn
Nhattanktnn
S
  • Question Question
Điều chỉnh kích thước dòng tự động theo trang in
Trả lời
5
Đọc
141
HUONGHCKT
H
A
Tạo vé Loto bằng Excel
2
Trả lời
20
Đọc
23K
Tuấn tata
Tuấn tata
Nauduong
Cách chuyển định dạng từ PDF sang Image
Trả lời
9
Đọc
262
Châu Bá Thông
Châu Bá Thông
minhduongct
  • Question Question
Viết code VBA thay cho dùng hàm vlookup
Trả lời
6
Đọc
1K
VetMini
V
N
Thử viết code lập trình đa luồng bằng TwinBasic và khả năng ứng dụng vào COM add-in chạy trong Excel
    • Thích
Trả lời
0
Đọc
173
nguyendang95
N
Thanh271188
Tìm giá trị xuất hiện bất kì (không phải giá trị đầu, cuối) trong excel
    • Cười
Trả lời
4
Đọc
228
Duy Tùng 9x
Duy Tùng 9x
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
Chúc mừng năm mới Ất Tỵ 2025
Back
Top Bottom