You have a very large Excel sheet. At the end of rows and columns are SUM formulas.

Cell AO42 contain the total sum.

Whenever you change any cell within range, the value in AO42 will change. To see the value in AP42, you should each time to scroll right and down to see the value.

You change the cell value in H8, but you would like to know immediately how much is the grand total in AO42. One option is to scroll down right to AO42. If you need to constantly fine tune numbers, it is time consuming to scroll right and down after each change.

However, if you have a macro to bring the value in message box.

Like this. When you assign a shortcut key to macro, you can check the grand total by pressing shortcut key.

VBA:

Sub z_tot()

Dim tul

On Error GoTo Fehler

tul = Round(Range(“ao42”).Value, 2)

MsgBox “The total sum is: ” & tul & “.”, _

vbOKOnly, “Own macro”

Exit Sub

Fehler:

    MsgBox “Error somewhere.”, vbOKOnly, ” Own macro”

    Exit Sub

End Sub

On error statement tracks whether the cell does not contain any reasonable value. If the function in AO42 does not return any value, the ROUND cannot round and VBA hits an error. With on error, the macro just brings a message box.

In case you want to see the row or column total instead of the grand total, you should scroll downwards or to right to see the row or the column total.

The macros below return the row and column totals based on the active cell. The active cell means the cell where the cursor is. If you have changed a cell and you would like to track the column or row total, do not select another cell, but keep the cursor in the cell where you made the change.

Sub z_col_tot()

Dim tul

Dim zr

Dim zv

Dim zs

zs = ActiveCell.Address

zr = ActiveCell.Row

ActiveCell.Offset((42 – zr), 0).Select

zv = ActiveCell.Address

Range(zs).Select

On Error GoTo Fehler

tul = Round(Range(zv).Value, 2)

MsgBox “The total sum for column is: ” & tul & “.”, _

vbOKOnly, “Own macro”

Exit Sub

Fehler:

    MsgBox “Error somewhere.”, vbOKOnly, ” Own macro”

    Exit Sub

End Sub

To see the row total

Sub z_row_tot()

Dim tul

Dim zc

Dim zv

Dim zs

zs = ActiveCell.Address

zc = ActiveCell.Column

ActiveCell.Offset(0, (41 – zc)).Select

zv = ActiveCell.Address

Range(zs).Select

On Error GoTo Fehler

tul = Round(Range(zv).Value, 2)

MsgBox “The total sum for row is: ” & tul & “.”, _

vbOKOnly, “Own macro”

Exit Sub

Fehler:

    MsgBox “Error somewhere.”, vbOKOnly, ” Own macro”

    Exit Sub

End Sub