Excel Auto Line Inserts

  osben 10:43 05 Mar 04
Locked

Hi

Excel XP2002 - I want to be able to put figures into cells going from top to bottom and have a "totals" cell at the bottom of these. When I reach the last cell to insert a figure I want another line to be automatically inserted
and the formula in the "totals" cell to take account of that line insertion.

Is it possible an if so how. Any Ideas

Regards

  Cook2 11:37 05 Mar 04

Enter your numbers into A1, A2, A3 ........etc

If the number of entries is, say 12, then in A13 type =B14 (assuming all entries are of equal length)

In A14 type =sum(A1:A13) Which will automatically adjust the total when you have completed column B.

It's much easier Done that Said.

  rogertjj 11:59 05 Mar 04

To do this, you need to use some VBA code.

Presuming that you are going to enter figures only, on Sheet1, type "Total" in Cell A3. Then, press ALT + F11 to enter the VBA editor. On the left hand side, double click "Sheet1(Sheet1)" and paste the following code


Private Sub Worksheet_SelectionChange(ByVal Target As Range)


rng = ActiveCell.Address


If ActiveCell.Offset(1, 0) = "Total" Then


Rows(ActiveCell.Row + 1).EntireRow.Insert


ActiveCell.Offset(2, 1) = "=SUM(A1:A" & ActiveCell.Row & ")"


ActiveCell.Offset(1, 1) = ""


End If


End Sub


Select cell A1 on sheet1, and start typing numbers, and the Total should be automatically be calculated. This is a bit rough, and VOG may well be able to provide a better answer.

  rogertjj 12:01 05 Mar 04

you don't need the rng = Activecell.Address line. Just me being stupid !

  Eric10 13:09 05 Mar 04

I'm not an Excel expert but I came up with this while playing with your problem and offer it as a possible basis for further development. It's not exactly what you asked for but may serve your purpose.

If you want to enter your numbers in column A and are prepared to accept your total in Column B level with the last entry, then this formula

=IF(AND(NOT(ISBLANK(A1)),ISBLANK(A2)),SUM($A$1:A1),"")

typed into B1 and copied down to the end of your sheet will give a running total in column B adjacent to your last entry in column A with other cells in column B remaining blank.

  VoG II 22:21 05 Mar 04

The following macro will automatically produce row and column totals with an extra row and column inserted to separate them. Simply click anywhere in the data table and run the macro. Adapt to suit!

Sub TotalThem()

Dim rRange As Range, rTotal As Range, cTotal As Range

Set rRange = ActiveCell.CurrentRegion

Set cTotal = rRange.Offset(0, rRange.Columns.Count).Columns(2)

Set rTotal = rRange.Offset(rRange.Rows.Count).Rows(2)

rTotal.Font.Bold = True

cTotal.Font.Bold = True

Cells(rTotal.Row, cTotal.Column).Font.Bold = True

Range(Cells(rTotal.Row, rTotal.Column), Cells(rTotal.Row, rTotal.Column + rTotal.Columns.Count + 1)).BorderAround Weight:=xlMedium

Range(Cells(cTotal.Row, cTotal.Column), Cells(cTotal.Row + cTotal.Rows.Count + 1, cTotal.Column)).BorderAround Weight:=xlMedium

Cells(rTotal.Row, cTotal.Column).BorderAround Weight:=xlThick

rTotal.Formula = "=Sum(" & rRange.Columns(1).Address(False, False) & ")"

cTotal.Formula = "=Sum(" & rRange.Rows(1).Address(False, False) & ")"

Cells(rTotal.Row, cTotal.Column).Formula = "=Sum(" & cTotal.Address & ")"

End Sub

  Megatyte 10:45 06 Mar 04

Why not have your Totals at the top of the column and freeze it? All you need to do then is total the entire column, apart from your totals cell.

AH

  osben 10:15 08 Mar 04

Thanks for all your suggestions.

They work!!

Regards

  Megatyte 17:42 08 Mar 04

Which one did you use?

AH

This thread is now locked and can not be replied to.

Best phone camera 2016/2017: Galaxy S7 vs iPhone 7 vs Google Pixel vs HTC 10 Evo vs OnePlus 3T vs…

1995-2015: How technology has changed the world in 20 years

Best Christmas Agency Projects of 2016

Super Mario Run preview | Hands-on first impressions of Super Mario Run: Mario's iPhone & iPad…