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]

You're welcome

 

[GirishDhruva]

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

Dim Fnd As Range
Set Fnd = Range("1:1").Find("Sum of Other Earning Non Taxable", , , wlwhole, , , False, False)
If Not Fnd Is Nothing Then Fnd.EntireColumn.Copy

and sir i don't need to copy the entire column , only i need the values that are their in that particular column

Thank you in prior

 

[GirishDhruva]

Thanks Fluff for your effort on trying this and making my work easier.
I need one more help that how i find the sum of particular column ..
1.I need to find the column by it's names
2.Then i need to do sum of that columns

(Like if i have Basic,HRA,PF,PT and some more other columns also, but i need to find the sum of only Basic,HRA and PF) and all that sum should be present in last column

Basic	HRA	PF	PT	ESI	Sum of Basic+HRA+PF
123	432	34324	544	366	34879
123	500	34324	544	366	34947
123	234	34324	544	366	34681
123	5643	34324	544	366	40090
123	54	34324	544	366	34501
123	345	34324	544	366	34792
123	213	34324	544	366	34660
123	432	34324	544	366	34879
123	432	34324	544	366	34879
123	345	34324	544	366	34792
123	546	34324	544	366	34993
123	5467	34324	544	366	39914
123	65	34324	544	366	34512
123	87	34324	544	366	34534
123	8768	34324	544	366	43215
123	769	34324	544	366	35216
123	879	34324	544	366	35326
123	9880	34324	544	366	44327

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

Like this it should give me as output
Thanks in Prior

 

[Fluff]

How about

Sub GirishDhruva()
Range("F2", Range("E" & Rows.Count).End(xlUp).Offset(, 1)).Formula = "=SUMPRODUCT(($A$1:$E$1=""Basic"")+($A$1:$E$1=""HRA"")+($A$1:$E$1=""PF""),(A2:E2))"
End Sub

 

[GirishDhruva]

Range("F2", Range("E" & Rows.Count).End(xlUp).Offset(, 1)).Formula

Instead of this can we perform that in ActiveCell, if we can means let me know how to perform that ASAP

Thank You

 

[GirishDhruva]

How about

Sub GirishDhruva()
Range("F2", Range("E" & Rows.Count).End(xlUp).Offset(, 1)).Formula = "=SUMPRODUCT(($A$1:$E$1=""Basic"")+($A$1:$E$1=""HRA"")+($A$1:$E$1=""PF""),(A2:E2))"
End Sub

Hi Sir,
I didn't understood your concept, but i need to find the sum of particular cells like, if i need to find the sum of basic,hra,pf and that sum should be available in "LastCell" , as i wouldn't be knowing were my basic,hra,pf would be available , but it should search for that columns and then it should sum-up all those values in last column

Thanks in prior

 

[Fluff]

Which is the last column?

 

[GirishDhruva]

Sir,Even Last column i should search (were lastcolumn+1 , the total should printed).

Right now my last column is 'X' but extra column might be added in next coming months , hence i should find out last column and sum should be available at last column

 

[Fluff]

How about

Sub GirishDhruva()
   Dim Nxt As Long
   Dim Hdr As String
   Nxt = Cells(1, Columns.Count).End(xlToLeft).Offset(, 1).Column
   Hdr = Range(Cells(1, 1), Cells(1, Nxt - 1)).Address(, , xlR1C1)
   Range(Cells(2, Nxt), Cells(Rows.Count, Nxt - 1).End(xlUp).Offset(, 1)).FormulaR1C1 = "=SUMPRODUCT((" & Hdr & "=""Basic"")+(" & Hdr & "=""HRA"")+(" & Hdr & "=""PF""),(rc1:rc[-1]))"
End Sub

 

[GirishDhruva]

ya it worked exactly how i needed .
thanks a lot and can u please explain me how this runs, so that i can learn
thank you in proir