VBA Macro to add columns

Question

Trying to create a VBA Macro, that adds the sum on a Column, then when the button is pressed again, proceeds to add the sum of the next column.

I have the following Code below. Currently It will add the first column, but then seems to lose the Integer values and just overwrites itself.

Also I am having issues figuring out how I would save the Column Letter instead of the Number for the column. Any help would be great!

Sub Button1_Click()
Dim vStartRow As Integer
Dim vEndRow As Integer
Dim vStartCol As Integer
Dim vEndCol As Integer

Range("A2").Select
ActiveCell.End(xlDown).Select
MsgBox vStartCol
vStartRow = 2 'Row 2
vStartCol = ActiveCell.Column

ActiveCell.End(xlToRight).Select
vEndRow = ActiveCell.Row 'Row 7

MsgBox vStartCol
Cells(vEndRow + 1, vStartCol + 1).Formula = "=SUM(" & vStartCol & vStartRow & ":" & vStartCol & vEndRow & ")"
Cells(vEndRow, vStartCol + 1).Select
vStartCol = vStartCol + 1
MsgBox vStartCol

End Sub

The data table looks like:

      Year 1    Year 2  Year 3
Chris   1         4       6
Jimmy   1         2       7
John    1         2       1
Frank   1         5       3
Fred    1         4       7
Jodie   1         2       6

Why not just add a total row and use formulae to populate it? — jsheeran
– jsheeran, Commented Nov 16, 2016 at 11:25

JNevill · Accepted Answer · 2016-11-16 12:06:02Z

What you have here is a really good start.

Something like the following, which is very close to what you've started with, should do the trick:

Sub Button1_Click()
    Dim vStartRow As Integer
    Dim vEndRow As Integer
    Dim rngCell As Range

    'get start and end rows for your sum
    vStartRow = 2 'Row 2
    vEndRow = Range("A2").End(xlDown).Row()

    'determine which column already has a sum by iterating through the cells in your vEndRow
    For Each rngCell In Rows(vEndRow + 1).Cells
        'Does the rngCell (the cell in the first empty row) have a value already (assuming it's not column 1, which we can't sum anyway)
        If rngCell.Value = "" And rngCell.Column <> 1 Then 'we have an empty cell and it's not column 1.
            'Since the value of rngCell has been determined we can exit the loop and it will stay set to this cell
            Exit For
        End If
    Next rngCell

    'finally set the formula for our empty column's total row
    rngCell.Formula = "=SUM(" & Cells(vStartRow, rngCell.Column()).Address & ":" & Cells(vEndRow, rngCell.Column()).Address & ")"

End Sub

A couple of things happening here:

First: The For Each loop. In VBA we have objects that can be Ranges, Worksheets, Workbooks, etc.. Many Objects have collections of other objects. For instance, Ranges have a collection of Rows, A workbook has a collection of sheets. We can iterate through Each of the items in a collection with the For Each loop.

An example of looping through each each sheet in a workbook:

Sub test()
    Dim sht as Worksheet

    For Each sht in ThisWorkbook.Sheets
        msgbox(sht.name)
    Next sht
End Sub

Second: Instead of selecting a cell, then getting a property of that cell (like it's row) you can just go straight to the property.

So instead of:

Cells(1,2).Select
myVar = Selection.Row

You can do:

myVar = Cells(1,2).Row

If you find your self using .Select or .Activate then you are probably doing something wrong. Humans .Select and .Activate, but the computer doesn't need to. It's wasteful and will lead to some strange behavior in your code. Although... sometimes it makes sense. If your requirement was:

"I want to sum up all of the numbers in the row that the user has selected when the push the button"

Then a selectedCell = Selection would make a lot of sense.

Or, conversely if you requirement was:

"When the subroutine is finished, I want cell A1 to be selected"

Then Sheet1.Range("A1").Select is the way to go. But, you can see in both instances it was due to our need to interact with the user that we utilized the Selection property and the Select() method of the Range object.

Third: You can get the address of a range object by accessing it's .Address property. So no need to monkey with column letters. Just hit the .Address and it will figure it out.

Fourth: I'm adding this after submitting, but it's important and confusing. Cells, Ranges, Rows, and Columns all are synonyms of a Range. So.. you can iterate through each of the cells in a row, or each of the rows in a column or each of the columns in a range. In this solution we iterate through the cells in a row where the cell is just a range() that is... a single a cell. Generally when you use a For Each loop on a range, you'll find yourself iterating a range's rows or a row's cells.

Collectives™ on Stack Overflow

VBA Macro to add columns

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related