我想要一个VBA代码来查找列中的数字总和。

本文介绍了我想要一个VBA代码来查找列中的数字总和。的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有一个从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 'support.microsoft/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 [ ^ ]

Credit: Chris Newman at Thespreadsheetguru[^]