我想要一个VBA代码来查找列中的数字总和。 [英] I want a VBA code to find the sum of numbers in a column.

问题描述

我有一个从G开始的大约60列的工作表。我想根据特定单元格中的给定值在每列中找到单元格/行（从G3开始）中的值的总和。 （即，在单元格G1中给出值10，我想要从单元格G3到G13的值的总和，并将其显示在G2中）。对于H，I，J等列也需要做同样的工作。



我尝试了什么：



我试过以下代码。它只给了我所有数字的总和。

Sub dural（）

Dim N As Long

N = Cells（Rows.Count， G）。结束（xlUp）。行

单元格（N + 1，G）。公式== SUM（B5：G& N&）

End Sub

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

推荐答案

SUM函数采用start形式表示的值范围：'end'（含）。

如果您将多列范围传递给它，它会将多列相加！



将B5位替换为G3以提供单列范围。

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.

查看Richard的评论和OriginalGriff的解决方案。



以下解决方案明确了解您的其余需求 - 即从G开始检查所有填充的列，从中获取总和的行数每列的单元格3对每列进行求和

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

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

我使用了Microsoft论坛上发布的 ConvertToLetter 函数：

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

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

）（ 1 ）
结束 功能

信用：Chris Newman Thespreadsheetguru.com [ ^ ]

Credit: Chris Newman at Thespreadsheetguru.com[^]

这篇关于我想要一个VBA代码来查找列中的数字总和。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！