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

 

[Fluff]

Hi & welcome to MrExcel.
How about

Excel 2013/2016
	A	B	C	D	E	F	G
1	Apple	Ball	Cat	Dog	Elephant		Ball
2	123	432	34324	544	366		7776
3	123	432	34324	544	366
4	123	432	34324	544	366
5	123	432	34324	544	366
6	123	432	34324	544	366
7	123	432	34324	544	366
8	123	432	34324	544	366
9	123	432	34324	544	366
10	123	432	34324	544	366
11	123	432	34324	544	366
12	123	432	34324	544	366
13	123	432	34324	544	366
14	123	432	34324	544	366
15	123	432	34324	544	366
16	123	432	34324	544	366
17	123	432	34324	544	366
18	123	432	34324	544	366
19	123	432	34324	544	366
BG2

Cell Formulas
Range	Formula
G2	=SUM(INDEX(A2:E19,0,MATCH(G1,A1:E1,0)))

 

[GirishDhruva]

Thank you for your reply, but i need in VBA code

 

[Fluff]

In that case

Sub GirishDhruva()
   Range("G3").Value = [=SUM(INDEX(A2:E19,0,MATCH(G1,A1:E1,0)))]
End Sub

 

[GirishDhruva]

Thanks again sir but i think i have not asked question clearly,My question is "first i need to find the column with the name 'Ball' then it should add with that column values and give me the sum"

 

[Fluff]

If you want to hard-code the column header try

Sub GirishDhruva()
   Range("G3").Value = [=SUM(INDEX(A2:E19,0,MATCH("Ball",A1:E1,0)))]
End Sub

 

[GirishDhruva]

Thank you ...............It worked

 

[GirishDhruva]

If you want to hard-code the column header try

Sub GirishDhruva()
   Range("G3").Value = [=SUM(INDEX(A2:E19,0,MATCH("Ball",A1:E1,0)))]
End Sub

Thanks again , how to copy the column by finding the column name

 

[Fluff]

Try

Sub chk()
   Dim Fnd As Range

   Set Fnd = Range("1:1").Find("Ball", , , xlWhole, , , False, , False)
   If Not Fnd Is Nothing Then Fnd.EntireColumn.Copy
End Sub

 

[GirishDhruva]

Thank you so much for your reply