VBA - Find a column with a specific header and find sum of all the rows in that column

Question

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)"

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 & ")"

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.

Answer 1

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

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