Sub TaoCDPS()
Dim endR As Long, eR As Long
Application.ScreenUpdating = False
Sheets("CDPS").Select
endR = Range("A65000").End(xlUp).Row - 1
If endR < 10 Then Exit Sub
Range("C10:H1000").ClearContents
With Sheets("Data")
eR = .Cells(65000, 1).End(xlUp).Row
.Range(.Cells(3, 6), .Cells(eR, 6)).Name = "TKPS_No"
.Range(.Cells(3, 7), .Cells(eR, 7)).Name = "TKPS_Co"
.Range(.Cells(3, 8), .Cells(eR, 8)).Name = "SotienPS"
End With
With Range("E10:E" & endR) 'PS No
.FormulaR1C1 = _
"=SUMPRODUCT((LEFT(TKPS_No,LEN(RC1))=TEXT(RC1,""#""))*SotienPS)"
.Value = .Value
End With
With Range("F10:F" & endR) 'PS Co
.FormulaR1C1 = _
"=SUMPRODUCT((LEFT(TKPS_Co,LEN(RC1))=TEXT(RC1,""#""))*SotienPS)"
.Value = .Value
End With
Names("TKPS_No").Delete
Names("TKPS_Co").Delete
Names("SotienPS").Delete
With Range("G10:G" & endR) 'Sd No
.FormulaR1C1 = "=MAX((RC3+RC5-RC4-RC6),0)"
.Value = .Value
End With
With Range("H10:H" & endR) 'Sd Co
.FormulaR1C1 = "=MAX(-(RC3+RC5-RC4-RC6),0)"
.Value = .Value
End With
'tao tong cong
With Range("C" & endR + 1 & ":H" & endR + 1) 'Sd Co
.FormulaR1C1 = "=SUMPRODUCT(--(LEN(R10C1:R131C1)=3)*R10C:R131C)"
End With
Application.ScreenUpdating = True
End Sub
