Sub code to add columns in a passed range

[JenniferMurphy]

Could someone post the code needed to add up the columns in an array passed as an argument to a Sub and put the sums in the row just below it?

I got this far and I put it in a code module in the calling workbook.

Sub ColumnSum(pTable As Range)

Dim Sums() As Double

Dim row As Integer
Dim col As Integer
Dim rows As Integer
Dim cols As Integer
rows = UBound(pTable, 1)
cols = UBound(pTable, 2)
ReDim Sums(cols)

For row = 0 To rows
  For col = 0 To cols
    Sums(col) = Sum(cols) + pTable(row, col)
  Next col
Next row

'How to store the results in row+1?


End Sub

But I can't test it because the name does not come up when I open up the Macros dialog in the Developer tab.

 

[Marcelo Branco]

Try something like this (adjust sheet name and range)

Option Explicit

Sub Main()
    aTest Sheets("Sheet1").Range("C3:E9") '<--adjust
End Sub

Sub aTest(pTable As Range)
    Dim lRows As Long
    Dim rCol As Range
    
    lRows = pTable.rows.Count
    For Each rCol In pTable.Columns
        With rCol.Cells(lRows + 1)
            .Value = Application.Sum(rCol.Cells)
            'optional
            .Font.Bold = True
        End With
    Next rCol
End Sub

Hope this helps

M.

 

[JenniferMurphy]

Try something like this (adjust sheet name and range)

Option Explicit

Sub Main()
    aTest Sheets("Sheet1").Range("C3:E9") '<--adjust
End Sub

Sub aTest(pTable As Range)
    Dim lRows As Long
    Dim rCol As Range
   
    lRows = pTable.rows.Count
    For Each rCol In pTable.Columns
        With rCol.Cells(lRows + 1)
            .Value = Application.Sum(rCol.Cells)
            'optional
            .Font.Bold = True
        End With
    Next rCol
End Sub

Hope this helps

M.

Looks good. I'll give that a try. Thanks.

Is there any way that I can pass the range to Main or aTest?

 

[Marcelo Branco]

Is there any way that I can pass the range to Main or aTest?

In my example Sub Main passes the range to Sub aTest

M.

 

[JenniferMurphy]

In my example Sub Main passes the range to Sub aTest

M.

Try something like this (adjust sheet name and range)

Option Explicit

Sub Main()
    aTest Sheets("Sheet1").Range("C3:E9") '<--adjust
End Sub

Sub aTest(pTable As Range)
    Dim lRows As Long
    Dim rCol As Range
   
    lRows = pTable.rows.Count
    For Each rCol In pTable.Columns
        With rCol.Cells(lRows + 1)
            .Value = Application.Sum(rCol.Cells)
            'optional
            .Font.Bold = True
        End With
    Next rCol
End Sub

Hope this helps

M.

A couple of questions:

1. Why do you have 2 subs? What purpose does Main serve? Couldn't you just combine that code in aTest?

2. I want to assign this code to a Button control on a sheet so I can just click on the Button to run the code. I tried putting the code (aTest) in my personal add-in module and also in a code module in the workbook. But when I right-click the Button control to assign the code, it can't find it. How do I get this code assigned to the Button control?

Thanks

 

[Marcelo Branco]

The idea of passing an argument, in this case a range, to a Sub is yours, not mine - you asked that in the opening message. So, I assumed there was a main routine that would call another routine to add some contiguous columns.

If you want to assign the Sub to a button, you should select some contiguous columns to be added and press the button.

The Sub aTest, assigned to the button, would have no arguments - it would use Selection

Something like this

Sub aTest()
   
    With Selection
        With .Columns(1).Cells(.Rows.Count + 1).Resize(1, .Columns.Count)
            .Formula = "=SUM(" & Selection.Columns(1).Address(1, 0) & ")"
            .Value = .Value
            .Font.Bold = True
        End With
    End With
   
End Sub

M.

 

[JenniferMurphy]

The idea of passing an argument, in this case a range, to a Sub is yours, not mine - you asked that in the opening message. So, I assumed there was a main routine that would call another routine to add some contiguous columns.

If you want to assign the Sub to a button, you should select some contiguous columns to be added and press the button.

The Sub aTest, assigned to the button, would have no arguments - it would use Selection

Something like this

Sub aTest()
  
    With Selection
        With .Columns(1).Cells(.Rows.Count + 1).Resize(1, .Columns.Count)
            .Formula = "=SUM(" & Selection.Columns(1).Address(1, 0) & ")"
            .Value = .Value
            .Font.Bold = True
        End With
    End With
  
End Sub

M.

OK. I'll play with that a bit.

Thanks