TheWildAfrican
Board Regular
- Joined
- Apr 23, 2013
- Messages
- 70
Dear All:
I'm trying to write a VBA code that would "Insert Rows" to separate cells with different names and "Sum" the contents of the "Separated" cells.
The file crashes each time I tried to run it.
Please the codes below. I hope you could point out what I've been doing wrong.
Thank you for your anticipated help.
Sub Sum_And_InsertRows()
Dim sum_start As Integer
Dim cnt_add_rows As Integer
Dim i As Integer
Dim fac_name As String
'initialize variable
sum_start = 0
cnt_add_rows = 0
Range("E3").Select
i = 0
While Not (IsEmpty(ActiveCell.Offset(i, 0).Value))
fac_name = ActiveCell.Offset(i, 0).Value
sum_start = i
While (ActiveCell.Offset(i, 0).Value = fac_name)
'Separating Deal names and inserting rows
If (ActiveCell.Offset(i - 1, -2).Value <> ActiveCell.Offset(i, -2).Value) Then
ActiveCell.Offset(i + cnt_add_rows, 0).EntireRow.Insert
End If
i = i + 1
Wend
ActiveCell.Offset(i - 1, 7).Value = Application.WorksheetFunction.Sum(Range(ActiveCell.Offset(sum_start, 7), ActiveCell.Offset(i - 1, 7)))
i = i + 1
Wend
End Sub
I'm trying to write a VBA code that would "Insert Rows" to separate cells with different names and "Sum" the contents of the "Separated" cells.
The file crashes each time I tried to run it.
Please the codes below. I hope you could point out what I've been doing wrong.
Thank you for your anticipated help.
Sub Sum_And_InsertRows()
Dim sum_start As Integer
Dim cnt_add_rows As Integer
Dim i As Integer
Dim fac_name As String
'initialize variable
sum_start = 0
cnt_add_rows = 0
Range("E3").Select
i = 0
While Not (IsEmpty(ActiveCell.Offset(i, 0).Value))
fac_name = ActiveCell.Offset(i, 0).Value
sum_start = i
While (ActiveCell.Offset(i, 0).Value = fac_name)
'Separating Deal names and inserting rows
If (ActiveCell.Offset(i - 1, -2).Value <> ActiveCell.Offset(i, -2).Value) Then
ActiveCell.Offset(i + cnt_add_rows, 0).EntireRow.Insert
End If
i = i + 1
Wend
ActiveCell.Offset(i - 1, 7).Value = Application.WorksheetFunction.Sum(Range(ActiveCell.Offset(sum_start, 7), ActiveCell.Offset(i - 1, 7)))
i = i + 1
Wend
End Sub