inserting conditional rows
 

Hi,
Am pretty new to VB programming and am faced with quiet a complex
problem.
Any help, pointers would be much appreciated.

I've a list of records sorted by one field.

I want to do the following.

1. Insert a row after change in value of that field
2. Calculate the sum of two other fields in that row.
3. Keep a loop until it reaches the end.

Thanks a lot


--
funbuntyus
------------------------------------------------------------------------
funbuntyus's Profile: http://www.excelforum.com/member.php...o&userid=34392
View this thread: http://www.excelforum.com/showthread...hreadid=541716

inserting conditional rows
 

funbuntyus wrote:

Hi,
Am pretty new to VB programming and am faced with quiet a complex
problem.
Any help, pointers would be much appreciated.

I've a list of records sorted by one field.

I want to do the following.

1. Insert a row after change in value of that field
2. Calculate the sum of two other fields in that row.
3. Keep a loop until it reaches the end.

Thanks a lot


--
funbuntyus
------------------------------------------------------------------------
funbuntyus's Profile: http://www.excelforum.com/member.php...o&userid=34392
View this thread: http://www.excelforum.com/showthread...hreadid=541716


Hi

I hope this will give you a start

Private Sub Worksheet_Change(ByVal Target As Range)
Static oldentry
newentry = Range("This is the cell that changes").Value
Application.EnableEvents = False

If newentry < oldentry Then
ActiveCell.EntireRow.Insert (xlDown)
' will insert a row under that chaged cell asuming you press enter
q = Application.CountIf(Rows(1), "<") + 1
' this is the number of entries in that row ,(XXX) is the row number
'the next lines you have to modify to the cells you want to add up
' I asumed here to be row A you want to add the values from and
calculate them in row B
For i = 1 To q
Cells(2, i) = Cells(1, i + 2) + Cells(1, i + 3)
Next i
End If
Application.EnableEvents = True
newentry = Range("This is the cell that changes").Value
oldentry = Range("This is the cell that changes").Value

End Sub

Cheers Christian

inserting conditional rows
 

Thanks Christian!
You got me started pretty well, it worked.

-adi


--
funbuntyus
------------------------------------------------------------------------
funbuntyus's Profile: http://www.excelforum.com/member.php...o&userid=34392
View this thread: http://www.excelforum.com/showthread...hreadid=541716