VBA - 查找具有特定标题的列并查找该列中所有行的总和

Question

我有一张大床单。我必须在该工作表中将多个过滤器设置为动态位置的列标题。设置过滤器后，我必须在工作表中找到列标题为“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$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”列时，我使用了这个公式。

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

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

Answer 1

确保您完全限定对象，并检查 .Find 是否返回某些内容。这是一个例子。

假设您的工作表如下所示

现在试试这个代码

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

输出