I want a VBA code to find the sum of numbers in a column.

Question

1.00/5 (1 vote)

See more:

I have a worksheet with around 60 columns starting from G. I want to find the sum of values in the cells/rows (starting from G3) in each column based on a given value in a particular cell. (i.e, a value say 10 is given in cell G1, I want the total of values from cell G3 to G13 and display it in G2). The same needs to be done for columns H,I,J etc.

What I have tried:

I have tried the below code. It just gives me sum of all numbers.
Sub dural()
Dim N As Long
N = Cells(Rows.Count, "G").End(xlUp).Row
Cells(N + 1, "G").Formula = "=SUM(B5:G" & N & ")"
End Sub

Posted 6-Jun-18 2:27am

Member 13861149

Updated 13-Jun-18 23:03pm

Add a Solution

Comments

Richard MacCutchan 6-Jun-18 8:45am

SUM(B5:G" & N & ")"
Why are you starting at B5?

You can generate the required code quite easily by using the Record Macro feature of Excel. Then adjust it to your requirements.

3 solutions

Solution 1

The SUM function takes a range of values expressed in the form 'start':'end' inclusive.
If you pass it a multicolumn range, it sums multiple columns!

Replace the "B5" bit with "G3" to give you a single column range.

Posted 6-Jun-18 2:47am

OriginalGriff

Solution 3

Reffering to CHill60[^] answer, i'd avoid of ActiveSheet, ActiveCell, etc. usage, because this might provide several issues!
Imagine, you've activated Sheet2, where there's a lot of data, but your code should insert SUM formulas in Sheet1. What happens, when you use "non-contextual" code?

You'll lose data in Sheet2!

Take a look at below code:

VB

Option Explicit

Sub CalculateSUM()
    Dim NoOfRows As Integer, NoOfCols As Integer, i As Integer
    Dim wsh As Worksheet, SumRow As Range
    

    'context!!!
    Set wsh = ThisWorkbook.Worksheets("Sheet1")
    'get number of rows to sum it up
    NoOfRows = wsh.Range("G1")
    'set first row with sum
    Set SumRow = wsh.Range("G2")
    'set no of columns to insert sum formula (60-1), because we use Offset(ColumnOffset:=xxx) method
    NoOfCols = 59
    For i = 0 To NoOfCols
        SumRow.Offset(ColumnOffset:=i).Formula = "=SUM(" & _
                SumRow.Offset(RowOffset:=1, ColumnOffset:=i).Address & ":" & _
                SumRow.Offset(RowOffset:=NoOfRows, ColumnOffset:=i).Address & ")"
    Next

End Sub

For further details, please see:
Range.Offset Property (Excel)[^]

Good luck!

Posted 13-Jun-18 23:05pm

Maciej Los

Updated 13-Jun-18 23:08pm

v2

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Accepted Answer · 2018-06-06T03:51:00

Solution 2

See the comment from Richard and the solution from OriginalGriff.

The following solution explicitly picks up on the rest of your requirement - i.e. checks for all of the populated columns from G onwards, picks up the number of rows to sum from Cell 3 of each column, does the summation for each column

VB

Sub dural()
    Dim lastCol As Integer
    lastCol = ActiveSheet.UsedRange.Column - 1 + ActiveSheet.UsedRange.Columns.Count

    Dim col As Integer
    For col = 7 To lastCol  'From G until finished
        Dim numRows As Long
        numRows = ActiveSheet.Cells(1, col).Value2
        
        Dim rng As String
        If numRows > 0 Then
            rng = ConvertToLetter(col) & "3:" & ConvertToLetter(col) & CStr(numRows + 3)
            
            'Get the appropriate total
            ActiveSheet.Cells(2, col).Formula = "=SUM(" & rng & ")"
        End If
    Next

End Sub

I used the ConvertToLetter function published on the Microsoft forums:
VB

Function ConvertToLetter(iCol As Integer) As String 'https://support.microsoft.com/en-gb/help/833402/how-to-convert-excel-column-numbers-into-alphabetical-characters Dim iAlpha As Integer Dim iRemainder As Integer iAlpha = Int(iCol / 27) iRemainder = iCol - (iAlpha * 26) If iAlpha > 0 Then ConvertToLetter = Chr(iAlpha + 64) End If If iRemainder > 0 Then ConvertToLetter = ConvertToLetter & Chr(iRemainder + 64) End If End Function
That didn't work, but this rather elegant solution seems to be fine

VB

Function ConvertToLetter(icol As Integer) As String
    ConvertToLetter = Split(Cells(1, icol).Address, "$")(1)
End Function

Credit: Chris Newman at Thespreadsheetguru.com[^]

Posted 6-Jun-18 3:51am

CHill60

Updated 13-Jun-18 21:47pm

v3

Comments

Member 13861149 7-Jun-18 5:56am

Thank you for your reply....
I am getting the sum. But sum of one row is added extra(i.e, i am getting the sum of 11 rows(from G3 to G13, but it should be G3 to G12) if i enter 10 in cell G1).

CHill60 7-Jun-18 7:43am

Oops sorry, I didn't spot that ... change the setting up of rng to the following

rng = ConvertToLetter(col) & "3:" & ConvertToLetter(col) & CStr(numRows + 2)

Member 13861149 12-Jun-18 22:00pm

Thank you.... It is working.... I modified the code in order to get the sum from D6, E6 etc in the D3,E3,F3 and so on... While running the macro I am getting Error 1004 and it is pointing to the ActiveSheet.Cells(3, col).Formula = "=SUM(" & rng & ")" line. Please find the code below and help me to resolve this.

Dim lastCol As Integer
lastCol = ActiveSheet.UsedRange.Column - 1 + ActiveSheet.UsedRange.Columns.Count

Dim col As Integer
For col = 4 To lastCol 'From G until finished
Dim numRows As Long
numRows = ActiveSheet.Cells(1, col).Value2

Dim rng As String
If numRows > 0 Then
rng = ConvertToLetter(col) & "6:" & ConvertToLetter(col) & CStr(numRows + 2)

'Get the appropriate total
ActiveSheet.Cells(3, col).Formula = "=SUM(" & rng & ")"
End If
Next

CHill60 13-Jun-18 4:09am

I don't get any error reported. Put a breakpoint on that line and check the values of rng.
You could also try putting the code in a module rather than on the Worksheet itself.
The ActiveSheet may be the problem so you could name the sheet explicitly e.g.

Sheets(1).Cells(1,col).Value2

or

Sheets("Sheet1").Cells(1, col).Value2

Member 13861149 13-Jun-18 4:47am

rng is having values A6 to A10 which is wrong. But all the other variables are holding the values as expected.

Tried the two given code lines also. But still showing the same error.
All these works fine for a single sheet workbook.

CHill60 13-Jun-18 5:13am

If it works fine for a single sheet workbook then try combining the explict naming of the sheet (replace all instances of ActiveSheet, not just the example I gave) with putting the code into a Module - remember to delete the one on the Worksheet

Member 13861149 13-Jun-18 5:19am

I checked the values each variable is holding. The code works smoothly till column AZ. But when col=53 (i.e BA), rng should be BA6:BA7 but i am getting the value "A[6:A[7" which follows the run time error

CHill60 13-Jun-18 7:07am

Ah! That code that I "stole" from the Microsoft Forums must have a problem. I'll have a look at it. Thanks for working out where the problem lies - I actually use this code myself so it was a problem just waiting to happen!

CHill60 13-Jun-18 11:31am

I've fixed that function now (or rather I've stolen the code from somewhere else!)

Member 13861149 13-Jun-18 23:25pm

The new code of ConvertTo() is not returning the exact value. rng currently holds only the range like 6:10 but not taking the characters such as D,E,F etc.(i.e, Previously the value in range was D6:D10,E6:E10 etc but now its 6:10). So the macro entirely goes wrong.

CHill60 14-Jun-18 3:51am

I've fixed the typing error in my solution, but really, you need to put some effort into this as well. You obviously don't have

Option Explicit

at the top of your module - you should always add this. In fact, if you go to Tools, Options, Editor tab and tick the option "Require Variable Declaration" it will be inserted automatically on any new code. You should also make sure that the option "Auto Syntax Check" is also checked.

Member 13861149 14-Jun-18 4:59am

Thank you. Its working now without any errors...

Maciej Los 14-Jun-18 5:14am

Caroline, this time i have to vote 3, because of... Please, see my answer.

CHill60 14-Jun-18 11:23am

I agree - see my comment above about naming the sheet explicitly. A 3 is more than fair and I must remember to stop being lazy :-)