VBA Express : Excel - Running Totals and SubTotals of Offset Columns

Excel

Running Totals and SubTotals of Offset Columns

Ease of Use

Easy

Version tested with

2000

Submitted by:

mdmackillop

Description:

Inserts the formula to total an offset column, either from the top of the page or from the previous SubTotal.

Discussion:

This code provides a subtotal at any point from a column of figures in the adjacent cell so it doesn't upset other calculations. Useful for checking page totals from other applications. Use with autofilter to get a condensed list of totals etc. The macros are easily adjusted to work with columns offset any distance to either side. The Sub-Total is interrupted by text or formula above the active cell in the same column.

Code:

instructions for use

			Sub SubTotl() 
    Dim Top As String, Bot As String, Fx As String 
    Dim OSet As Integer 
     
    OSet = -1 '-1 Add column to left of activecell
     'Oset = range("A1").value
     
    Bot = ActiveCell.Offset(0, OSet).Address 
     'Check if formula in Row 1
    If Left(ActiveCell.End(xlUp).Formula, 1) <> "=" Then 
        Top = ActiveCell.End(xlUp).Offset(0, OSet).Address 
    Else 
        Top = ActiveCell.End(xlUp).Offset(1, OSet).Address 
    End If 
    Fx = "=Sum(" & Top & ":" & Bot & " ) " 
    ActiveCell.Formula = Fx 
    Selection.Font.Bold = False 'Option to distinguish total type
End Sub 
 
Sub RunTotl() 
    Dim Top As String, Bot As String, Fx As String 
    Dim OSet As Integer 
     
    OSet = -1 '-1 Add column to left of activecell
     'Oset = range("A1").value
     
    Bot = ActiveCell.Offset(0, OSet).Address 
    Top = Cells(1, ActiveCell.Column() + OSet).Address 
    Fx = "=Sum(" & Top & ":" & Bot & " ) " 
    ActiveCell.Formula = Fx 
    Selection.Font.Bold = True 'Option to distinguish total type
End Sub

How to use:

Open your workbook.
Hit Alt + F11 to open the Visual Basic Editor.
From the menu, choose Insert -Module.
Paste the code into the window at right.
Close VBE (Alt + Q or press the X in the top right hand corner).

Test the code:

Create a column of figures.
In the cell immediately to the right of a figure, click the button for the Sub-Total or Running Total macro.
Total the Sub-Totals by selecting a cell in the next column and so on.

Sample File:

TotalOptions.zip 8.81KB

Approved by mdmackillop

This entry has been viewed 382 times.