VBA-查找具有特定标题的列并查找该列中所有行的总和 [英] VBA - Find a column with a specific header and find sum of all the rows in that column

问题描述

我有一张大纸.我必须在该工作表中将多个过滤器设置为动态位置的列标题.设置好过滤器后，我必须在工作表中找到具有列标题"Nov"的特定列，然后获取该列中值的总和并将该特定总和导入到不同的工作表中. 我已经编写了代码，直到可以将过滤器设置为多列的那一部分为止，但是我发现很难找到列标题并添加该列.下面是我到目前为止编写的代码.

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.

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$8:$DD$9999").AutoFilter Field:=colName, Criteria1:="ST Test",     Operator:=xlOr, Criteria2:=""
ActiveSheet.Range("$A$8:$DD$9999").AutoFilter Field:=colName1, Criteria1:="Variance", Operator:=xlOr, Criteria2:="(Blanks)"
ActiveSheet.Range("$A$8:$DD$9999").AutoFilter Field:=colName2, Criteria1:="9S", Operator:=xlOr, Criteria2:="(Blanks)"

列标题始终从第8行开始.上面的行中显示了一些无用的信息.所以我想要的是，假设列"Nov"在H行中.应该从H9到最后一行的末尾计算总和.当列位于"H"列中时，我就使用了此公式.

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.

Cells(lastrow + 1, colName3).Formula = "=SUBTOTAL(9,H9:H" & lastrow & ")"

但是'Nov'列并不总是出现在'H'行中，因此我无法弄清楚如何更改代码以动态地选择该列.

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.

推荐答案

确保完全限定对象的资格，并检查.Find是否返回值.这是一个例子.

Ensure that you fully qualify your objects and also put in a check whether .Find returns something or not. Here is an example.

假设您的工作表如下

现在尝试此代码

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

输出

这篇关于VBA-查找具有特定标题的列并查找该列中所有行的总和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！