Column Summation Excel VBA

Question

This question is kind of complicated (I feel), so I will do my best to explain the problem.

Essentially what I want to do is move down each column in the range, adding each cell value up (getting the sum of the column) and then adding it to an array. However, when testing the values held in the array, it is always 0. Is there a better way to do this?

Here is my current code:

Dim sumHoldings(1 To 36) As Double
k = 1

For Each rep In repNames

    If rep <> vbNullString Then
    Worksheets(rep).Activate
    Dim i As Integer
    Dim sumHolder As Double

    For i = 3 To 6

        Columns(i).Select

        For Each rangeCell In Selection

            If rangeCell <> vbNullString Then

                sumHolder = rangeCell.Value + sumHolder

                Else:
                    sumHoldings(k) = sumHolder  'this current method will keep overwriting itself
                    k = k + 1
                    Exit For

            End If

        Next rangeCell
    Next i
    End If
Next rep

Heres a visual representation of what I am trying to do:

Any help is greatly appreciated, thank you!

score 2 · Accepted Answer · answered Jul 27 '17 at 15:34

2

This is what you need to do.

Option Explicit

Public Sub TestMe()

    Dim myRng       As Range
    Dim myCell      As Range
    Dim myCol       As Range
    Dim arrResults  As Variant
    Dim dblSum      As Double
    Dim lngCounter  As Long


    Set myRng = Range("R17:T25")
    ReDim arrResults(myRng.Columns.Count -1)

    For Each myCol In myRng.Columns
        dblSum = 0
        For Each myCell In myCol.Cells
            dblSum = dblSum + myCell
        Next myCell
        arrResults(lngCounter) = dblSum
        lngCounter = lngCounter + 1
    Next myCol

End Sub

The array arrResults would get the sum of each column. Make sure to edit Set myRng = Range("R17:T25") to something meaningful for you.

The code works exactly as you have described it - it takes each column in the range, using myRng.Columns and it iterates over it. Then it takes each cell in myCol.Cells and it iterates again. Thus, the complexity is O2.

answered Jul 27 '17 at 15:34

Vityata

42,633
8
55
100

`dblSum = dblSum + myCell.Value` – Rafael Matos Jul 27 '17 at 15:39
@RafaelMatos - `.Value` is not needed, it works w/o it as well. – Vityata Jul 27 '17 at 15:41
I didn't test the code, but if you say so, it's ok. – Rafael Matos Jul 27 '17 at 15:42
2

@RafaelMatos - in general, `.Value` is the default of the range. Thus, it can be skipped. – Vityata Jul 27 '17 at 15:43
1

Is there a particular reason you used a long for a counter? wouldn't integer work too, and take up less memory? – ThatOneGuy Jul 27 '17 at 15:44
1

@ThatOneGuy - forget that `Integer` exists in VBA and never use them - https://stackoverflow.com/questions/26409117/why-use-integer-instead-of-long – Vityata Jul 27 '17 at 15:44

score 0 · Answer 2 · answered Jul 27 '17 at 15:37

The reason you are getting zeros is because your if statement is letting them store null cells into your array when there is nothing in them. just move the storing section to after all cells are summed up:

For Each rangeCell In Selection

If rangeCell <> vbNullString Then

 sumHolder = rangeCell.Value + sumHolder

 End If

Next rangeCell
   sumHoldings(k) = sumHolder  
    k = k + 1
Next i
End If

Column Summation Excel VBA

2 Answers2