Inserting Rows and Summing Subtotal In VBA...

[TheWildAfrican]

Dear All:

I've been working on this code for 4 days without any luck....... I'm trying to insert a row whenever the ActiveCell data (B10) <> the previous Cell data (B9). At the same time I'd like to sum the subtotal of the cells on Cell (M11). The code I wrote below doesn't work (In fact, this is the 3rd version of the code). Could you please help check the code and advise on what I'm doing wrong?.....

Thanks for your anticipated help.

OBER RESPEKT,

TWA

Code:
Sub HuntingGoodF()
'Columns from B: Product Identifier=0

Dim sum_start As Integer
Dim sum_end As Integer
Dim cnt_add_rows As Integer
Dim i As Integer
Dim temp_identifier As String

' initialize variables
sum_start = 0
sum_end = 0
cnt_add_rows = 0

Range("B10").Select
i = 0

While Not (IsEmpty(ActiveCell.Offset(i, 0).Value))
temp_identifier = ActiveCell.Offset(i, 0).Value
sum_start = i

While (ActiveCell.Offset(i, 0).Value = temp_identifier)
' Filter for same product identifier
If (IsEmpty(ActiveCell.Offset(i - 1, -1)) Or (ActiveCell.Offset(i, 0).Value <> ActiveCell.Offset(i - 1, 0).Value)) Then
ActiveCell.Offset(i, 11).Value = ActiveCell.Offset(i, 10).Value
Else
ActiveCell.Offset(i, 11).Value = 0
End If
i = i + 1
Wend

' insert 1 rows
ActiveCell.Offset(i + cnt_add_rows, 0).EntireRow.Insert

ActiveCell.Offset(i - 1, 11).Value = Application.WorksheetFunction.Sum(Range(ActiveCell.Offset(sum_start, 11), ActiveCell.Offset(i - 1, 11)))
With ActiveCell.Offset(i - 1, 11)
.Font.Bold = True
.NumberFormat = "$#,##0.00"
End With
ActiveCell.Offset(i, 0).EntireRow.Insert
i = i + 1
Wend

 

[TheWildAfrican]

Deal All:

I hope all is well with everyone. I'm happy to write that I finally got the code to work. I don't think the method I'm using is the most efficient way to write the code, but it works!! ...

I wanted to take this opportunity to thank everyone who view my thread. mrexcel.com ROCKS!!!!

Please see below for the new way I rewrote the code.

KONKRETE RESPEKT,

TheWildAfrican


Code:
Sub CouldNotGetHelp()
'Columns from C: Fees Remit= 12

Dim sum_start As Integer
Dim cnt_add_rows As Integer
Dim i As Integer
Dim temp_identifier As String

' initialize variables
sum_start = 0
cnt_add_rows = 0

Range("C2").Select
i = 0

While Not (IsEmpty(ActiveCell.Offset(i, 0).Value))
temp_identifier = ActiveCell.Offset(i, 0).Value
sum_start = i

While (ActiveCell.Offset(i, 0).Value = temp_identifier)
' Fees Payment calc
If (IsEmpty(ActiveCell.Offset(i - 1, -2)) Or (ActiveCell.Offset(i, 2).Value <> ActiveCell.Offset(i - 1, 2).Value)) Then
ActiveCell.Offset(i, 14).Value = ActiveCell.Offset(i, 12).Value
Else
ActiveCell.Offset(i, 14).Value = 0
End If
i = i + 1
Wend

' insert 2 rows
ActiveCell.Offset(i + cnt_add_rows, 0).EntireRow.Insert

i = i + 1

ActiveCell.Offset(i - 1, 12).Value = Application.WorksheetFunction.sum(Range(ActiveCell.Offset(sum_start, 12), ActiveCell.Offset(i - 1, 12)))

ActiveCell.Offset(i, 0).EntireRow.Insert
i = i + 1
Wend

End Sub