CODEWITHSUNDEEP
excelvba
SuperMan
SuperMan

Reputation: 19

Sum a range for a specific column

I want to build a dynamic reporting, and for that, if the header is equal to a specific text then sum the entire column below the header. This is the code that I have.

Sub FindIfSumColumn()
    Dim LastRow As Long
    Dim rgFound As Range
    Dim mFound As Range
    Dim bd As Worksheet: Set bd = Sheets("BDD")
    Dim dt As Worksheet: Set dt = Sheets("DICT")

    LastCol = bd.Cells(1, Columns.Count).End(xlToLeft).Column

    Set mFound = dt.Range("B2")

    Set rgFound = bd.Range("A1:XFD" & LastCol).Find(What:=mFound, _
    LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByColumns)

    If rgFound Is Nothing Then
        MsgBox "Nothing"
    Else
        LastRow = rgFound.Cells(Rows.Count, 1).End(xlUp).Row
        dt.Range("B4") = Application.WorksheetFunction.Sum(LastRow)
    End If
End Sub

Upvotes: 1

Views: 41

Answers (2)

FaneDuru
FaneDuru

Reputation: 42236

Replace, please, your code row

dt.Range("B4") = Application.WorksheetFunction.Sum(LastRow)

with

dt.Range("B4") = Application.WorksheetFunction.Sum(bd.Range(rgFound.Offset(1, 0), rgFound.Offset(lastRow, 0)))

Upvotes: 0

Siddharth Rout
Siddharth Rout

Reputation: 149277

Logic:

  1. Find the header
  2. Get the row below the header
  3. Get the last row under that header
  4. Create your range to sum
  5. Find sum

TIP: It will be easier if you give meaningful names to your variables. That way it will be easier to understand what they are for

Is this what you are trying?

Option Explicit

Sub FindIfSumColumn()
    Dim StartRow As Long, LastRow As Long
    Dim FoundColumn As String
    Dim StringToFind As String
    Dim ResultRange As Range
    Dim sumRng As Range

    Dim bd As Worksheet: Set bd = Sheets("BDD")
    Dim dt As Worksheet: Set dt = Sheets("DICT")

    StringToFind = dt.Range("B2").Value

    Set ResultRange = bd.Cells.Find(What:=StringToFind, LookIn:=xlValues, _
                                    LookAt:=xlWhole, SearchOrder:=xlByColumns)

    If ResultRange Is Nothing Then
        MsgBox "Nothing"
    Else
        '~~> Get the row after the header
        StartRow = ResultRange.Row + 1
        '~~> Column of the header
        FoundColumn = Split(Cells(, ResultRange.Column).Address, "$")(1)
        '~~> Last row under that header
        LastRow = bd.Range(FoundColumn & bd.Rows.Count).End(xlUp).Row

        '~~> The range that we need to sum
        Set sumRng = bd.Range(FoundColumn & StartRow & ":" & FoundColumn & LastRow)

        '~~> Output
        dt.Range("B4") = Application.WorksheetFunction.Sum(sumRng)
    End If
End Sub

Upvotes: 1

Related Questions