How to SUM all the values in a column by MATCHING a specific column header using VBA?

[kchuphone]

For work, I'm trying to set up a macro to sum all the values in a column based on a specific column header using VBA. (The location of the columns can change.)

For example, I'm trying find the column with header "Blueberry" and sum all the values in the column and return value in E2 (see sample xls below).

I tried both methods below, but failed. What am I missing? Learning VBA and need some help for work! Any insights and pointers would be appreciated!

Version 1

Sub sumRange()

Dim colHeaders As Range
Dim colID As Long

Set colHeaders = Range("1:1")
   
    colID = Application.Match("Blueberry", colHeaders, 0)
 
    If Not IsError(colID) Then
        Range("E2") = Application.WorksheetFunction.Sum(Range("colID":"colID"))
     End If

End Sub

Version 2

Sub sumRange()
 
      
    Dim rngS As Range

     Set rngS = Range(Cells(1, 1), Cells(1, _
        Cells(1, Columns.Count).End(xlToLeft).Column)).Find("Blueberry")

    If Not rngS Is Nothing Then
    Range("E2") = Application.WorksheetFunction.Sum(rngS)
     
     End If
    
End Sub

Apple	Orange	Banana	Blueberry	Total Banana
123	342	234	4
23123	3423	234	4
3423	234	234	32
423432	24	24	4342

 

[kweaver]

This is a bit confusing. Why would you want the total of the column headed with "Blueberry" to be in the next column that's headed "Total Banana" ?

Why wouldn't you want to the total for each column UNDER the column?

 

[kchuphone]

This is a bit confusing. Why would you want the total of the column headed with "Blueberry" to be in the next column that's headed "Total Banana" ?

Why wouldn't you want to the total for each column UNDER the column?

Hi,

Sorry, that was a typo. It should be "Total Blueberry" . I'm using the spreadsheet to mask some client sensitive data.

My intention is to eventually return the value in a separate spreadsheet ( or in SharePoint). For simplicity, I just put it in E2 in the sample xls sheet above.

Thanks

 

[kweaver]

Well, this looks at the header in the Total column and pulls the name and then totals that column.
You might adjust from this.

Sub SumRange()
Dim c As Long, tot As Long, w As String
Dim rngHdr As Range
w = Right(Cells(1, 5), Len(Cells(1, 5)) - InStr(1, Cells(1, 5), " "))
Cells(1, "E") = "Total " & w
Set rngHdr = Range("1:1") 'Looks in entire first row
c = WorksheetFunction.Match(w, rngHdr, 0)
tot = WorksheetFunction.Sum(Columns(c))
Cells(2, 5) = tot
End Sub[code]

 

[kchuphone]

Well, this looks at the header in the Total column and pulls the name and then totals that column.
You might adjust from this.

Sub SumRange()
Dim c As Long, tot As Long, w As String
Dim rngHdr As Range
w = Right(Cells(1, 5), Len(Cells(1, 5)) - InStr(1, Cells(1, 5), " "))
Cells(1, "E") = "Total " & w
Set rngHdr = Range("1:1") 'Looks in entire first row
c = WorksheetFunction.Match(w, rngHdr, 0)
tot = WorksheetFunction.Sum(Columns(c))
Cells(2, 5) = tot
End Sub[code]

Thank you! This was helpful, I was able to modify this for my use case!