Auto Insert

[skipwith]

Hi,

I have a spreadsheet with multiple records Eg.

01/09/2006 JOB2 3,500
01/09/2006 JOB2 10000
01/09/2006 JOB2 100
01/09/2006 JOB3 10
01/09/2006 JOB3 100

Could you tell me how I use VBA to insert a line when the Job number changes and then total up the end column by Job?

Thanks

 

[dcnick]

Hi skipwith. Try this:

Sub GetSum()

Dim counter As Integer

Sheets("Sheet1").Range("B1").Select
Do Until ActiveCell.Value = ""

  counter = 1
  Do Until ActiveCell.Value <> ActiveCell.Offset(1, 0).Value
    Selection.Offset(1, 0).Select
    counter = counter + 1
  Loop

  Selection.Offset(1, 0).Select
  ActiveCell.EntireRow.Select
  Selection.Insert Shift:=xlDown
  ActiveCell.Offset(0, 2).Select
  ActiveCell.Formula = "=SUM(R[-" & counter & "]C:R[-1]C)"
  Selection.Offset(1, -1).Select

Loop

End Sub

Hope this helps.

 

[Smitty]

Welcome to the Board!

How about Data-->Subtotals-->At each change in-->Job Number-->Sum-->Revenue column.

I'll take a native solution any day.

HTH,

Smitty

 

[skipwith]

Gentlemen,

Thankyou for your replies!


Regards,


Nick.