Trying to add a formula

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

dave - 01 Oct 2007 16:31 GMT

As part of my macro i have this block of data where i need to insert 3
lines whenever the contents of column B changes. I have tried various
formats of inserting a calculation, all to no avail, where i have the
string of question marks. Basically i want to insert a formula that
sums up that column from the first row to the last row that has the
same value in column B. the variable firstrow will have the first row
number and the vairlabe i will have last riow number

LastRow = Range(Cells(1, 1), Cells(Rows.Count, 1)).End(xlDown).Row
FirstRow = 4
For i = 4 To LastRow
If Cells(i, 2) <> Cells(i + 1, 2) Then
Cells(i + 1, 2).EntireRow.Insert
Cells(i + 1, 2).EntireRow.Insert
Cells(i + 1, 2).EntireRow.Insert
Cells(i + 1, 11).Value = "Total"

Cells(i + 1,12) ???????????????

Cells(i + 1, 11).Select
Selection.Font.Bold = True
Cells(i + 2, 11).Value = "AUV Verify"
Cells(i + 2, 11).Select
Selection.Font.Bold = True
Cells(i + 3, 11).Value = "Difference"
Cells(i + 3, 11).Select
Selection.Font.Bold = True
LastRow = LastRow + 3
i = i + 3
FirstRow = i + 1
End If
Next i

Reply to this Message

Bob Phillips - 01 Oct 2007 17:17 GMT

LastRow = Cells(Rows.Count, 1).End(xlUp).Row
AddTo = LastRow
For i = LastRow - 1 To 4 Step -1
If Cells(i, 2).Value <> Cells(i + 1, 2).Value Then
Rows(AddTo + 1).Resize(3).Insert
With Cells(AddTo + 1, 11)
.Value = "Total"
.Font.Bold = True
End With
Cells(AddTo + 1, 12).Formula = "=SUM(A" & i + 1 & ":A" & AddTo &
")"

With Cells(AddTo + 2, 11)
.Value = "AUV Verify"
.Font.Bold = True
End With

With Cells(AddTo + 3, 11)
.Value = "Difference"
.Font.Bold = True
End With
AddTo = i
End If
Next i

Signature

HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

> As part of my macro i have this block of data where i need to insert 3
> lines whenever the contents of column B changes. I have tried various
[quoted text clipped - 28 lines]
> End If
> Next i

Reply to this Message