win-sun
Thành viên hoạt động
- Tham gia
- 19/1/09
- Bài viết
- 151
- Được thích
- 15
- Tính chính xác nhưng quá chậm, liệu có phải do dử liệu nhiều không hay còn cách nào nhanh hơn , nhờ các sư phụ chỉ giúp>
Option Explicit
Sub THXNT()
Dim HC As Long
Dim i As Long
Dim Ma As Range
Application.Calculation = xlCalculationManual
HC = S109.Range("C65500").End(xlUp).Row
S109.Select
Range("EA9:U9").EntireColumn.Hidden = False
S109.Range("A9:R" & HC + 1).ClearContents
S109.Range("A10:R" & HC + 2).Select
Call NLine
HC = S101.Range("A65000").End(xlUp).Row
S109.Range("B9:G" & HC + 7).Value = S101.Range("A2:F" & HC).Value
Range("H9:H" & HC + 7).FormulaR1C1 = "=SUMPRODUCT((DataNgay<R5C6)*((LEFT(DataLYDO,4)=""NHAP"")-(LEFT(DataLYDO,4)=""XUAT""))*(DataTEN=RC[-5])*(DataSL))+RC[-1]"
Range("I9:I" & HC + 7).FormulaR1C1 = "=SUMPRODUCT((DataNgay>=R5C6)*(DataNgay<=R5C9)*(DataLYDO=""NHAP MUA"")*(DataTEN=RC[-6])*(DataSL))"
Range("J9:J" & HC + 7).FormulaR1C1 = "=SUMPRODUCT((DataNgay>=R5C6)*(DataNgay<=R5C9)*(DataLYDO=""NHAP KHAC"")*(DataTEN=RC[-7])*(DataSL))"
Range("K9:K" & HC + 7).FormulaR1C1 = "=SUM(RC[-2]:RC[-1])"
Range("L9:L" & HC + 7).FormulaR1C1 = "=SUMPRODUCT((DataNgay>=R5C6)*(DataNgay<=R5C9)*(DataLYDO=""XUAT SU DUNG"")*(DataTEN=RC[-9])*(DataSL))"
Range("M9:M" & HC + 7).FormulaR1C1 = "=SUMPRODUCT((DataNgay>=R5C6)*(DataNgay<=R5C9)*(DataLYDO=""XUAT KHAC"")*(DataTEN=RC[-10])*(DataSL))"
Range("N9:N" & HC + 7).FormulaR1C1 = "=SUM(RC[-2]:RC[-1])"
Range("O9:O" & HC + 7).FormulaR1C1 = "=RC[-7]+RC[-4]-RC[-1]"
Range("T9:T" & HC + 7).FormulaR1C1 = "=IF(SUM(RC[-12]:RC[-5])>0,1,"""")"
With S109.Range("B9:T" & HC + 7)
.Calculate
.Value = .Value
.Sort Key1:=Range("T9"), Order1:=xlAscending, Key2:=Range( _
"B9"), Order2:=xlAscending, Header:=xlNo, OrderCustom:=1, MatchCase _
:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, _
DataOption2:=xlSortNormal
End With
i = S109.Range("T65000").End(xlUp).Row
If i < 10 Then i = 10
S109.Range("A" & i + 1 & ":T" & HC + 7).ClearContents
S109.Range("T1:T5000").ClearContents
With Range("A9:A" & i)
.FormulaR1C1 = "=ROW()-8"
.Calculate
.Value = .Value
End With
With Range("G" & i + 2 & ":O" & i + 2)
.FormulaR1C1 = "=SUM(R9C:R[-1]C)"
.Calculate
.Value = .Value
End With
Range("C" & i + 2) = "TONG CONG"
Range("G:G").EntireColumn.Hidden = True
S109.Range("A10:R" & i + 1).Select
Call YLine
S109.Range("A" & i + 2 & ":R" & i + 2).Select
Call YLineTC
Range("D5").Select
End Sub
Option Explicit
Sub THXNT()
Dim HC As Long
Dim i As Long
Dim Ma As Range
Application.Calculation = xlCalculationManual
HC = S109.Range("C65500").End(xlUp).Row
S109.Select
Range("EA9:U9").EntireColumn.Hidden = False
S109.Range("A9:R" & HC + 1).ClearContents
S109.Range("A10:R" & HC + 2).Select
Call NLine
HC = S101.Range("A65000").End(xlUp).Row
S109.Range("B9:G" & HC + 7).Value = S101.Range("A2:F" & HC).Value
Range("H9:H" & HC + 7).FormulaR1C1 = "=SUMPRODUCT((DataNgay<R5C6)*((LEFT(DataLYDO,4)=""NHAP"")-(LEFT(DataLYDO,4)=""XUAT""))*(DataTEN=RC[-5])*(DataSL))+RC[-1]"
Range("I9:I" & HC + 7).FormulaR1C1 = "=SUMPRODUCT((DataNgay>=R5C6)*(DataNgay<=R5C9)*(DataLYDO=""NHAP MUA"")*(DataTEN=RC[-6])*(DataSL))"
Range("J9:J" & HC + 7).FormulaR1C1 = "=SUMPRODUCT((DataNgay>=R5C6)*(DataNgay<=R5C9)*(DataLYDO=""NHAP KHAC"")*(DataTEN=RC[-7])*(DataSL))"
Range("K9:K" & HC + 7).FormulaR1C1 = "=SUM(RC[-2]:RC[-1])"
Range("L9:L" & HC + 7).FormulaR1C1 = "=SUMPRODUCT((DataNgay>=R5C6)*(DataNgay<=R5C9)*(DataLYDO=""XUAT SU DUNG"")*(DataTEN=RC[-9])*(DataSL))"
Range("M9:M" & HC + 7).FormulaR1C1 = "=SUMPRODUCT((DataNgay>=R5C6)*(DataNgay<=R5C9)*(DataLYDO=""XUAT KHAC"")*(DataTEN=RC[-10])*(DataSL))"
Range("N9:N" & HC + 7).FormulaR1C1 = "=SUM(RC[-2]:RC[-1])"
Range("O9:O" & HC + 7).FormulaR1C1 = "=RC[-7]+RC[-4]-RC[-1]"
Range("T9:T" & HC + 7).FormulaR1C1 = "=IF(SUM(RC[-12]:RC[-5])>0,1,"""")"
With S109.Range("B9:T" & HC + 7)
.Calculate
.Value = .Value
.Sort Key1:=Range("T9"), Order1:=xlAscending, Key2:=Range( _
"B9"), Order2:=xlAscending, Header:=xlNo, OrderCustom:=1, MatchCase _
:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, _
DataOption2:=xlSortNormal
End With
i = S109.Range("T65000").End(xlUp).Row
If i < 10 Then i = 10
S109.Range("A" & i + 1 & ":T" & HC + 7).ClearContents
S109.Range("T1:T5000").ClearContents
With Range("A9:A" & i)
.FormulaR1C1 = "=ROW()-8"
.Calculate
.Value = .Value
End With
With Range("G" & i + 2 & ":O" & i + 2)
.FormulaR1C1 = "=SUM(R9C:R[-1]C)"
.Calculate
.Value = .Value
End With
Range("C" & i + 2) = "TONG CONG"
Range("G:G").EntireColumn.Hidden = True
S109.Range("A10:R" & i + 1).Select
Call YLine
S109.Range("A" & i + 2 & ":R" & i + 2).Select
Call YLineTC
Range("D5").Select
End Sub