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

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
Fluff said:
Try
Code: 
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
Click to expand...

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

ecO6i.png


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
 
Upvote 0
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

[TABLE="width: 456"]
<colgroup><col span="5"><col></colgroup><tbody>[TR]
[TD]Basic[/TD]
[TD]HRA[/TD]
[TD]PF[/TD]
[TD]PT[/TD]
[TD]ESI[/TD]
[TD]Sum of Basic+HRA+PF[/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]
[TD="align: right"]34879[/TD]
[/TR]
[TR]
[TD="align: right"]123[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]34324[/TD]
[TD="align: right"]544[/TD]
[TD="align: right"]366[/TD]
[TD="align: right"]34947[/TD]
[/TR]
[TR]
[TD="align: right"]123[/TD]
[TD="align: right"]234[/TD]
[TD="align: right"]34324[/TD]
[TD="align: right"]544[/TD]
[TD="align: right"]366[/TD]
[TD="align: right"]34681[/TD]
[/TR]
[TR]
[TD="align: right"]123[/TD]
[TD="align: right"]5643[/TD]
[TD="align: right"]34324[/TD]
[TD="align: right"]544[/TD]
[TD="align: right"]366[/TD]
[TD="align: right"]40090[/TD]
[/TR]
[TR]
[TD="align: right"]123[/TD]
[TD="align: right"]54[/TD]
[TD="align: right"]34324[/TD]
[TD="align: right"]544[/TD]
[TD="align: right"]366[/TD]
[TD="align: right"]34501[/TD]
[/TR]
[TR]
[TD="align: right"]123[/TD]
[TD="align: right"]345[/TD]
[TD="align: right"]34324[/TD]
[TD="align: right"]544[/TD]
[TD="align: right"]366[/TD]
[TD="align: right"]34792[/TD]
[/TR]
[TR]
[TD="align: right"]123[/TD]
[TD="align: right"]213[/TD]
[TD="align: right"]34324[/TD]
[TD="align: right"]544[/TD]
[TD="align: right"]366[/TD]
[TD="align: right"]34660[/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]
[TD="align: right"]34879[/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]
[TD="align: right"]34879[/TD]
[/TR]
[TR]
[TD="align: right"]123[/TD]
[TD="align: right"]345[/TD]
[TD="align: right"]34324[/TD]
[TD="align: right"]544[/TD]
[TD="align: right"]366[/TD]
[TD="align: right"]34792[/TD]
[/TR]
[TR]
[TD="align: right"]123[/TD]
[TD="align: right"]546[/TD]
[TD="align: right"]34324[/TD]
[TD="align: right"]544[/TD]
[TD="align: right"]366[/TD]
[TD="align: right"]34993[/TD]
[/TR]
[TR]
[TD="align: right"]123[/TD]
[TD="align: right"]5467[/TD]
[TD="align: right"]34324[/TD]
[TD="align: right"]544[/TD]
[TD="align: right"]366[/TD]
[TD="align: right"]39914[/TD]
[/TR]
[TR]
[TD="align: right"]123[/TD]
[TD="align: right"]65[/TD]
[TD="align: right"]34324[/TD]
[TD="align: right"]544[/TD]
[TD="align: right"]366[/TD]
[TD="align: right"]34512[/TD]
[/TR]
[TR]
[TD="align: right"]123[/TD]
[TD="align: right"]87[/TD]
[TD="align: right"]34324[/TD]
[TD="align: right"]544[/TD]
[TD="align: right"]366[/TD]
[TD="align: right"]34534[/TD]
[/TR]
[TR]
[TD="align: right"]123[/TD]
[TD="align: right"]8768[/TD]
[TD="align: right"]34324[/TD]
[TD="align: right"]544[/TD]
[TD="align: right"]366[/TD]
[TD="align: right"]43215[/TD]
[/TR]
[TR]
[TD="align: right"]123[/TD]
[TD="align: right"]769[/TD]
[TD="align: right"]34324[/TD]
[TD="align: right"]544[/TD]
[TD="align: right"]366[/TD]
[TD="align: right"]35216[/TD]
[/TR]
[TR]
[TD="align: right"]123[/TD]
[TD="align: right"]879[/TD]
[TD="align: right"]34324[/TD]
[TD="align: right"]544[/TD]
[TD="align: right"]366[/TD]
[TD="align: right"]35326[/TD]
[/TR]
[TR]
[TD="align: right"]123[/TD]
[TD="align: right"]9880[/TD]
[TD="align: right"]34324[/TD]
[TD="align: right"]544[/TD]
[TD="align: right"]366[/TD]
[TD="align: right"]44327
[/TD]
[/TR]
</tbody>[/TABLE]

Like this it should give me as output
Thanks in Prior
 
Last edited:
Upvote 0
How about
Code: 
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
 
Upvote 0
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
 
Upvote 0
Fluff said:
How about
Code: 
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
Click to expand...
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
 
Upvote 0
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
 
Last edited:
Upvote 0
How about
Code: 
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
 
Upvote 0
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Question Question
Count Distinct across two columns
Replies
6
Views
576
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
493
Fluff
Fluff
P
  • Question Question
Create formula below specific text
Replies
7
Views
680
Tim_Excel_
Tim_Excel_
adrienne0914
  • Question Question
Average Calculated Field on Pivot Table
Replies
0
Views
563
adrienne0914
adrienne0914

Forum statistics

Threads
1,224,823
Messages
6,181,183
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

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