Find the column by name and to sum that column

[GirishDhruva]

Hi everyone i am posting for 1st time in MrExcel, Help me to solve this.
Here is the table.

Apple	Ball	Cat	Dog	Elephant
123	432	34324	544	366
123	432	34324	544	366
123	432	34324	544	366
123	432	34324	544	366
123	432	34324	544	366
123	432	34324	544	366
123	432	34324	544	366
123	432	34324	544	366
123	432	34324	544	366
123	432	34324	544	366
123	432	34324	544	366
123	432	34324	544	366
123	432	34324	544	366
123	432	34324	544	366
123	432	34324	544	366
123	432	34324	544	366
123	432	34324	544	366
123	432	34324	544	366

<colgroup><col><col><col span="3"></colgroup><tbody>
</tbody>

From the above table i need to find "Ball" and sum that column and that should be shown in a particular cell in VBA

 

[RoryA]

It will be much easier if you turn the source data into a table. You can then refer to columns directly just using their name.

 

[GirishDhruva]

It will be much easier if you turn the source data into a table. You can then refer to columns directly just using their name.

Sorry Sir/Madam,
I didn't get what you have told

 

[RoryA]

I mean that if you make the source data into a Table, you can refer to columns directly using the header, without having to try and find them. You can also just turn on the Totals row for the table and add summary formulas to it, like this:

    With ActiveSheet.ListObjects("Table1")
        .ShowTotals = True
        .ListColumns("Field3").TotalsCalculation = xlTotalsCalculationSum
    End With

 

[GirishDhruva]

Ya we can do that but i need to try in this way too, so i am trying this and could you help me with that

Thank you in prior

 

[RoryA]

I don't know what you mean by that.

 

[GirishDhruva]

I am not that much familiar with coding and i am just beginning to code and i am trying in my basic way(Like after completion this row it must go to this row , in that way i am trying ) , so can u solve me with this code

Sub sumcol()
    Dim sh As Worksheet
    Dim Fnd As Range
    Dim c As Long
    Dim lr As Long
    Set sh = Sheets("Sheet1")
    Set Fnd = sh.Rows(1).Find("Basic", , xlValues, xlWhole)
    lr = Fnd.Cells(Fnd.Rows.Count, 1).End(xlUp).Row
    If Not Fnd Is Nothing Then
        Fnd.Cells(lr + 1, 0).Formula = "=SUM(" & Fnd.Range(Fnd.Cells(2, 0), Fnd.Cells(lr, 0)).Address & ")"
    Else
        MsgBox "Search Item Not Found!"
        Exit Sub
    End If
End Sub

 

[RoryA]

Is there a column that will always have data for each row? If so, you should use that to find the LR value:

LR = sh.cells(sh.rows.count, "A").end(xlup).row

for example. A Table is easier though.

 

[GirishDhruva]

As of my assumption this might happen (i have mentioned that in quotes for code lines below), if i have gone wrong any were please correct me

Sub sumcol()
    Dim sh As Worksheet
    Dim Fnd As Range
    Dim c As Long
    Dim lr As Long
    Set sh = Sheets("Sheet1") ' sets sheet1 in sh
    Set Fnd = sh.Rows(1).Find("Basic", , xlValues, xlWhole)' finds 'Basic ' and stores in Fnd
    lr = Fnd.Cells(Fnd.Rows.Count, 1).End(xlUp).Row' To find the last row of Fnd
    If Not Fnd Is Nothing Then
        Fnd.Cells(lr + 1, 0).Formula = "=SUM(" & Fnd.Range(Fnd.Cells(2, 0), Fnd.Cells(lr, 0)).Address & ")" 'if Fnd is found then it should sum all the values that are their in Fnd row and should display that sum in last+1 row of Fnd
    Else
        MsgBox "Search Item Not Found!"
        Exit Sub
    End If
End Sub

[/QUOTE]
Thank you in advance

 

[RoryA]

That's not going to work, since fnd is one cell, which is why I suggested what I did.

 

[GirishDhruva]

Hi RoryA,
I found the solution and one one my friend suggested, by making the find as string.

Sub Test()


    Dim Fnd As Range, StrFind As String, ws As Worksheet, Col As Integer, LastRow As Long


    StrFind = "HRA"


    Set ws = ThisWorkbook.Sheets("Sheet1")


    With ws
        Col = 0
        On Error Resume Next 'Error handler
        Col = .Cells.Find(StrFind).Column 'Find the column
        On Error GoTo 0
        If Not Col = 0 Then 'If the item is found
            LastRow = .Cells(.Rows.Count, Col).End(xlUp).Row 'the last row of that column
            Set Fnd = .Range(.Cells(2, Col), .Cells(LastRow, Col)) 'Set the range
            .Cells(LastRow + 1, Col) = Application.Sum(Fnd) 'sum the range on the next available row
        Else
            MsgBox "Search Item Not Found!"
        End If
    End With


End Sub

I have one more doubt that how can i make that sum in negative and copy that value, is their any possible way for that.

Thank you in prior