Excel vba loop

[ianmac]

Does anybody know how I'd get this to work?

I'd like to sort my spreadsheet so that I have duplicates one above the other (there will be two or more) I'd like to get it to do a calculation on a field where a cell has th sme account no. Something like

if ActiveSheet.Cells(1,4).Value = ActiveSheet.CElls(1,7).Value Then
ActiveSheet.Cells(1,7).Value = ((ActiveSheet.Cells(1,5).Value)-(ActiveSheet.Cells(2,5).Value ))
ActiveSheetRows(x).Delete End If

So if there are two rows with the same account no it will calc the contents. Id theres three it wil calc the three etc. Then it will delete all but the row with the answer in. Then move on to the next rows in the sheet and carry on from there.

Anyone have an inkling?

I can get it to look at a single rows values and set a format then delete all rows without the format but this isn't what I need:
'Write formula to first cell

Sheets(UserInput).Select
Worksheets(UserInput).Range("L1").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-2]>=RC[-1],0)"
Worksheets(UserInput).Range("L1").Select

'Copy the formula onto all cells
Selection.AutoFill Destination:=Worksheets(UserInput).Range("L1:L4000"), Type:=xlFillDefault
Range("L1:L4000").Select
Worksheets(UserInput).Range("L1").Select

'Find all rows where 0 has been set and delete them
num = 0
Do
Worksheets(UserInput).Columns("L:L").Select
On Error Resume Next
Selection.Find(What:="0", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False) _
.Activate
If Err.Number = 91 Then
End
Else
RowNo = ActiveCell.Row
Worksheets(UserInput).Rows(RowNo).Delete
End If

num = num + 1
Loop Until num = 3000

Please help

[gzazJim]

Hey ianmac,

I have done something similar in the past... Let me try and find my MS Office VBA book, and I'll see what I can come up with!

Thanks for your patience!

Jim