Find the column by name and to sum that column

G

GirishDhruva

Active Member
Joined
Mar 26, 2019
Messages
308
Hi everyone i am posting for 1st time in MrExcel, Help me to solve this.
Here is the table.
[TABLE="width: 299"]
<colgroup><col><col><col span="3"></colgroup><tbody>[TR]
[TD]Apple[/TD]
[TD]Ball[/TD]
[TD]Cat[/TD]
[TD]Dog[/TD]
[TD]Elephant[/TD]
[/TR]
[TR]
[TD="align: right"]123[/TD]
[TD="align: right"]432[/TD]
[TD="align: right"]34324[/TD]
[TD="align: right"]544[/TD]
[TD="align: right"]366[/TD]
[/TR]
[TR]
[TD="align: right"]123[/TD]
[TD="align: right"]432[/TD]
[TD="align: right"]34324[/TD]
[TD="align: right"]544[/TD]
[TD="align: right"]366[/TD]
[/TR]
[TR]
[TD="align: right"]123[/TD]
[TD="align: right"]432[/TD]
[TD="align: right"]34324[/TD]
[TD="align: right"]544[/TD]
[TD="align: right"]366[/TD]
[/TR]
[TR]
[TD="align: right"]123[/TD]
[TD="align: right"]432[/TD]
[TD="align: right"]34324[/TD]
[TD="align: right"]544[/TD]
[TD="align: right"]366[/TD]
[/TR]
[TR]
[TD="align: right"]123[/TD]
[TD="align: right"]432[/TD]
[TD="align: right"]34324[/TD]
[TD="align: right"]544[/TD]
[TD="align: right"]366[/TD]
[/TR]
[TR]
[TD="align: right"]123[/TD]
[TD="align: right"]432[/TD]
[TD="align: right"]34324[/TD]
[TD="align: right"]544[/TD]
[TD="align: right"]366[/TD]
[/TR]
[TR]
[TD="align: right"]123[/TD]
[TD="align: right"]432[/TD]
[TD="align: right"]34324[/TD]
[TD="align: right"]544[/TD]
[TD="align: right"]366[/TD]
[/TR]
[TR]
[TD="align: right"]123[/TD]
[TD="align: right"]432[/TD]
[TD="align: right"]34324[/TD]
[TD="align: right"]544[/TD]
[TD="align: right"]366[/TD]
[/TR]
[TR]
[TD="align: right"]123[/TD]
[TD="align: right"]432[/TD]
[TD="align: right"]34324[/TD]
[TD="align: right"]544[/TD]
[TD="align: right"]366[/TD]
[/TR]
[TR]
[TD="align: right"]123[/TD]
[TD="align: right"]432[/TD]
[TD="align: right"]34324[/TD]
[TD="align: right"]544[/TD]
[TD="align: right"]366[/TD]
[/TR]
[TR]
[TD="align: right"]123[/TD]
[TD="align: right"]432[/TD]
[TD="align: right"]34324[/TD]
[TD="align: right"]544[/TD]
[TD="align: right"]366[/TD]
[/TR]
[TR]
[TD="align: right"]123[/TD]
[TD="align: right"]432[/TD]
[TD="align: right"]34324[/TD]
[TD="align: right"]544[/TD]
[TD="align: right"]366[/TD]
[/TR]
[TR]
[TD="align: right"]123[/TD]
[TD="align: right"]432[/TD]
[TD="align: right"]34324[/TD]
[TD="align: right"]544[/TD]
[TD="align: right"]366[/TD]
[/TR]
[TR]
[TD="align: right"]123[/TD]
[TD="align: right"]432[/TD]
[TD="align: right"]34324[/TD]
[TD="align: right"]544[/TD]
[TD="align: right"]366[/TD]
[/TR]
[TR]
[TD="align: right"]123[/TD]
[TD="align: right"]432[/TD]
[TD="align: right"]34324[/TD]
[TD="align: right"]544[/TD]
[TD="align: right"]366[/TD]
[/TR]
[TR]
[TD="align: right"]123[/TD]
[TD="align: right"]432[/TD]
[TD="align: right"]34324[/TD]
[TD="align: right"]544[/TD]
[TD="align: right"]366[/TD]
[/TR]
[TR]
[TD="align: right"]123[/TD]
[TD="align: right"]432[/TD]
[TD="align: right"]34324[/TD]
[TD="align: right"]544[/TD]
[TD="align: right"]366[/TD]
[/TR]
[TR]
[TD="align: right"]123[/TD]
[TD="align: right"]432[/TD]
[TD="align: right"]34324[/TD]
[TD="align: right"]544[/TD]
[TD="align: right"]366[/TD]
[/TR]
</tbody>[/TABLE]
From the above table i need to find "Ball" and sum that column and that should be shown in a particular cell in VBA
 
Sort by date Sort by votes
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.
 
Upvote 0

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
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:

Code: 
    With ActiveSheet.ListObjects("Table1")
        .ShowTotals = True
        .ListColumns("Field3").TotalsCalculation = xlTotalsCalculationSum
    End With
 
Last edited:
Upvote 0
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
 
Last edited:
Upvote 0
I don't know what you mean by that.
 
Upvote 0
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
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
 
Upvote 0
Is there a column that will always have data for each row? If so, you should use that to find the LR value:

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

for example. A Table is easier though.
 
Upvote 0
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
Code: 
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
 
Last edited:
Upvote 0
That's not going to work, since fnd is one cell, which is why I suggested what I did.
 
Upvote 0
Hi RoryA,
I found the solution and one one my friend suggested, by making the find as string.
Code: 
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
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Question Question
Count Distinct across two columns
Replies
6
Views
573
lljones
L
M
  • Question Question
unpivotting a table with macro
Replies
2
Views
6K
Mechixx
M
G
  • Locked
  • Question Question
How to find the sum of particular cells
Replies
3
Views
490
Fluff
Fluff
P
  • Question Question
Create formula below specific text
Replies
7
Views
669
Tim_Excel_
Tim_Excel_
adrienne0914
  • Question Question
Average Calculated Field on Pivot Table
Replies
0
Views
561
adrienne0914
adrienne0914

Forum statistics

Threads
1,223,911
Messages
6,175,333
Members
452,636
Latest member
laura12345

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top