VBA - 查找具有特定标题的列并查找该列中所有行的总和
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20122393/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
VBA - Find a column with a specific header and find sum of all the rows in that column
提问by Rahul Baskaran
I have a large sheet. I have to set multiple filters in that sheet to columns headers in dynamic positions. Once the filters are set, I have to find the particular column in the sheet having the column header "Nov" and then obtain the sum of values in that column and import that particular sum value into a different worksheet. I've written the code up until the part where i can set the filters to multiple columns, but I'm finding it difficult to find the column header and add that column. Below is the code I've written so far.
我有一张大床单。我必须在该工作表中将多个过滤器设置为动态位置的列标题。设置过滤器后，我必须在工作表中找到列标题为“Nov”的特定列，然后获取该列中值的总和并将该特定总和值导入到不同的工作表中。我已经编写了代码，直到可以将过滤器设置为多列的部分，但我发现很难找到列标题并添加该列。下面是我到目前为止编写的代码。
Sub Button2_Click()
Dim colName As Long
Dim colName1 As Long
Dim colName2 As Long
Dim r As Long
SearchV = Range("A8:DD8").Find(What:="Nov", LookIn:=xlValues, LookAt:=xlWhole, _
MatchCase:=False, SearchFormat:=False).Column
lastrow = Cells(Rows.Count, SearchV).End(xlUp).Row
colName = Range("A8:DD8").Find(What:="Teams", LookIn:=xlValues, LookAt:=xlWhole, _
MatchCase:=False, SearchFormat:=False).Column
colName1 = Range("A8:DD8").Find(What:="Items", LookIn:=xlValues, LookAt:=xlWhole, _
MatchCase:=False, SearchFormat:=False).Column
colName2 = Range("A8:DD8").Find(What:="Domain", LookIn:=xlValues, LookAt:=xlWhole, _
MatchCase:=False, SearchFormat:=False).Column
ActiveSheet.Range("$A:$DD99").AutoFilter Field:=colName, Criteria1:="ST Test", Operator:=xlOr, Criteria2:=""
ActiveSheet.Range("$A:$DD99").AutoFilter Field:=colName1, Criteria1:="Variance", Operator:=xlOr, Criteria2:="(Blanks)"
ActiveSheet.Range("$A:$DD99").AutoFilter Field:=colName2, Criteria1:="9S", Operator:=xlOr, Criteria2:="(Blanks)"
The column headers always start from the 8th row. Some uesless information is present in the rows above. So what I want is, suppose the column 'Nov' is in H row. The sum should be calculated from H9 to the end of the last row. I had used this formula when the column was in 'H' column.
列标题总是从第 8 行开始。上面的行中存在一些无用的信息。所以我想要的是，假设“Nov”列在 H 行。总和应从 H9 计算到最后一行的末尾。当列在“H”列中时，我使用了这个公式。
Cells(lastrow + 1, colName3).Formula = "=SUBTOTAL(9,H9:H" & lastrow & ")"
But the column 'Nov' won't always be present in row 'H', so i'm not able to figure out how to change my code to pick the column dynamically.
但是“Nov”列并不总是出现在“H”行中，因此我无法弄清楚如何更改我的代码以动态选择该列。
回答by Siddharth Rout
Ensure that you fully qualify your objects and also put in a check whether .Findreturns something or not. Here is an example.
确保您完全限定您的对象，并检查是否.Find返回某些内容。这是一个例子。
Let's say your worksheet looks like this
假设您的工作表如下所示
Now try this code
现在试试这个代码
Option Explicit
Sub Sample()
Dim ws As Worksheet
Dim aCell As Range, Rng As Range
Dim col As Long, lRow As Long
Dim colName As String
'~~> Change this to the relevant sheet
Set ws = ThisWorkbook.Sheets("Sheet1")
With ws
Set aCell = .Range("A8:DD8").Find(What:="Nov", LookIn:=xlValues, LookAt:=xlWhole, _
MatchCase:=False, SearchFormat:=False)
'~~> If Found
If Not aCell Is Nothing Then
col = aCell.Column
colName = Split(.Cells(, col).Address, "$")(1)
lRow = .Range(colName & .Rows.Count).End(xlUp).Row
'~~> This is your range
Set Rng = .Range(colName & "8:" & colName & lRow)
Debug.Print Rng.Address
'~~> If not found
Else
MsgBox "Nov Not Found"
End If
End With
End Sub
Output
输出
