'Thong ti ung ho dien dan tuhocvba.net
'Ngan hang thuong mai co phan ngoai thuong viet nam Vietcombank
'So tai khoan: 0011003264055
'Chi nhanh ngan hang: Quan Hoan Kiem, Ha Noi
'Chu tai khoan: Pham Minh Hoang
Sub tuhocvba()
Dim i As Long, rend As Long, r1 As Long, r2 As Long, n1 As Long, n2 As Long
Dim shn As String
Dim s1 As String, s2 As String, s3 As String
Const rstart As Long = 2
Const coth As Integer = 8
Const cotg As Integer = 7
Application.ScreenUpdating = False
Application.DisplayAlerts = False
shn = ThisWorkbook.ActiveSheet.Name
rend = ThisWorkbook.Sheets(shn).Cells(Rows.Count, 1).End(xlUp).Row
If rend <= (rstart + 1) Then Exit Sub
'Dan dong
For i = rend To rstart + 1 Step -1
s1 = ThisWorkbook.Sheets(shn).Cells(i, coth)
s2 = ThisWorkbook.Sheets(shn).Cells(i - 1, coth)
If s1 <> "" And s2 <> "" And s1 <> s2 Then
ThisWorkbook.Sheets(shn).Rows(i).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
End If
Next i
'Ghi cong thuc
rend = ThisWorkbook.Sheets(shn).Cells(Rows.Count, 1).End(xlUp).Row
For i = rstart + 1 To rend Step 1
s1 = ThisWorkbook.Sheets(shn).Cells(i, coth)
s2 = ThisWorkbook.Sheets(shn).Cells(i + 1, coth)
s3 = ThisWorkbook.Sheets(shn).Cells(i - 1, coth)
If s1 <> s3 And s1 <> "" Then r1 = i
If s1 <> s2 And s2 = "" Then
r2 = i
'Ghi cong thuc
n1 = r1 - (i + 1)
n2 = r2 - (i + 1)
ThisWorkbook.Sheets(shn).Cells(i + 1, cotg).FormulaR1C1 = "=SUM(R[" & n1 & "]C:R[" & n2 & "]C)"
End If
Next i
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub