ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Sum Columns with Specific Headings (https://www.excelbanter.com/excel-programming/443448-sum-columns-specific-headings.html)

prkhan56[_2_]
Sum Columns with Specific Headings
 
Hello All

I am using XP/Office2003

I am trying to sum columns (including blanks) with specific Headings.

My headings are as follows starting in Column B, Row 4
Heading1, Heading2, Heading3,……..Heading11.

Heading numbers will increase/decrease for various reports.

In current report, I have recorded the following macro which sums
Column Heading9 and 10

Sub Macro3 ()
Range("J45").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-40]C:R[-1]C)"
ActiveWindow.SmallScroll Down:=0
Range("K45").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-40]C:R[-1]C)"
ActiveWindow.SmallScroll Down:=12
End Sub

The Heading names will change if there are fewer columns…but the text
will be same always.

I wish to have a macro which should look for the text eg “aaaa” and
“bbbb” and sum these columns.

Number of Rows will also vary. The macro should therefore look up for
the text “aaaa” and “bbbb” in Row 4 headings and sum up these two
columns (including blanks)

Any help would be appreciated.

TIA

Rashid Khan

Don Guillett Excel MVP
Sum Columns with Specific Headings
 
On Aug 4, 6:33*am, prkhan56 wrote:
Hello All

I am using XP/Office2003

I am trying to sum columns (including blanks) with specific Headings.

My headings are as follows starting in Column B, Row 4
Heading1, Heading2, Heading3,……..Heading11.

Heading numbers will increase/decrease for various reports.

In current report, I have recorded the following macro which sums
Column Heading9 and 10

Sub Macro3 ()
Range("J45").Select
* * ActiveCell.FormulaR1C1 = "=SUM(R[-40]C:R[-1]C)"
* * ActiveWindow.SmallScroll Down:=0
* * Range("K45").Select
* * ActiveCell.FormulaR1C1 = "=SUM(R[-40]C:R[-1]C)"
* * ActiveWindow.SmallScroll Down:=12
End Sub

The Heading names will change if there are fewer columns…but the text
will be same always.

I wish to have a macro which should look for the text eg “aaaa” and
“bbbb” and sum these columns.

Number of Rows will also vary. *The macro should therefore look up for
the text “aaaa” and “bbbb” in Row 4 headings and sum up these two
columns (including blanks)

Any help would be appreciated.

TIA

Rashid Khan

Sub subspecificcolumnsSAS() 'sum colums with specific headers
ma = Array("aaaa", "bbbb")
For Each t In ma
'MsgBox t
mc = Rows(1).Find(What:=t, LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, MatchCase _
:=False, SearchFormat:=False).Column
'MsgBox mc
clr = Cells(Rows.Count, mc).End(xlUp).Row
'MsgBox clr
'MsgBox Application.Sum(Range(Cells(4, mc), Cells(clr, mc)))
Cells(45, mc).Value = Application.Sum(Range(Cells(1, mc), Cells(clr,
mc)))
Next t
End Sub

prkhan56[_2_]
Sum Columns with Specific Headings
 
On Aug 4, 4:46*pm, Don Guillett Excel MVP
wrote:
On Aug 4, 6:33*am, prkhan56 wrote:





Hello All

I am using XP/Office2003

I am trying to sum columns (including blanks) with specific Headings.

My headings are as follows starting in Column B, Row 4
Heading1, Heading2, Heading3,……..Heading11.

Heading numbers will increase/decrease for various reports.

In current report, I have recorded the following macro which sums
Column Heading9 and 10

Sub Macro3 ()
Range("J45").Select
* * ActiveCell.FormulaR1C1 = "=SUM(R[-40]C:R[-1]C)"
* * ActiveWindow.SmallScroll Down:=0
* * Range("K45").Select
* * ActiveCell.FormulaR1C1 = "=SUM(R[-40]C:R[-1]C)"
* * ActiveWindow.SmallScroll Down:=12
End Sub

The Heading names will change if there are fewer columns…but the text
will be same always.

I wish to have a macro which should look for the text eg “aaaa” and
“bbbb” and sum these columns.

Number of Rows will also vary. *The macro should therefore look up for
the text “aaaa” and “bbbb” in Row 4 headings and sum up these two
columns (including blanks)

Any help would be appreciated.

TIA

Rashid Khan

Sub subspecificcolumnsSAS() 'sum colums with specific headers
ma = Array("aaaa", "bbbb")
For Each t In ma
*'MsgBox t
mc = Rows(1).Find(What:=t, LookIn:=xlValues, _
*LookAt:=xlWhole, SearchOrder:=xlByColumns, _
*SearchDirection:=xlNext, MatchCase _
*:=False, SearchFormat:=False).Column
*'MsgBox mc
clr = Cells(Rows.Count, mc).End(xlUp).Row
*'MsgBox clr
*'MsgBox Application.Sum(Range(Cells(4, mc), Cells(clr, mc)))
Cells(45, mc).Value = Application.Sum(Range(Cells(1, mc), Cells(clr,
mc)))
Next t
End Sub- Hide quoted text -

- Show quoted text -

Hello Don,
Thanks for your help. I have a small hitch.
What I wanted is a Sum(Range) type of solution. When any values are
added to the Blank Cells the Sum function would get updated.

Also it should clear any previous values in the columns before
inserting the Sum function.

Hope I am clear

Don Guillett Excel MVP
Sum Columns with Specific Headings
 
On Aug 4, 11:08*am, prkhan56 wrote:
On Aug 4, 4:46*pm, Don Guillett Excel MVP
wrote:





On Aug 4, 6:33*am, prkhan56 wrote:

Hello All

I am using XP/Office2003

I am trying to sum columns (including blanks) with specific Headings.

My headings are as follows starting in Column B, Row 4
Heading1, Heading2, Heading3,……..Heading11.

Heading numbers will increase/decrease for various reports.

In current report, I have recorded the following macro which sums
Column Heading9 and 10

Sub Macro3 ()
Range("J45").Select
* * ActiveCell.FormulaR1C1 = "=SUM(R[-40]C:R[-1]C)"
* * ActiveWindow.SmallScroll Down:=0
* * Range("K45").Select
* * ActiveCell.FormulaR1C1 = "=SUM(R[-40]C:R[-1]C)"
* * ActiveWindow.SmallScroll Down:=12
End Sub

The Heading names will change if there are fewer columns…but the text
will be same always.

I wish to have a macro which should look for the text eg “aaaa” and
“bbbb” and sum these columns.

Number of Rows will also vary. *The macro should therefore look up for
the text “aaaa” and “bbbb” in Row 4 headings and sum up these two
columns (including blanks)

Any help would be appreciated.

TIA

Rashid Khan

Sub subspecificcolumnsSAS() 'sum colums with specific headers
ma = Array("aaaa", "bbbb")
For Each t In ma
*'MsgBox t
mc = Rows(1).Find(What:=t, LookIn:=xlValues, _
*LookAt:=xlWhole, SearchOrder:=xlByColumns, _
*SearchDirection:=xlNext, MatchCase _
*:=False, SearchFormat:=False).Column
*'MsgBox mc
clr = Cells(Rows.Count, mc).End(xlUp).Row
*'MsgBox clr
*'MsgBox Application.Sum(Range(Cells(4, mc), Cells(clr, mc)))
Cells(45, mc).Value = Application.Sum(Range(Cells(1, mc), Cells(clr,
mc)))
Next t
End Sub- Hide quoted text -

- Show quoted text -

Hello Don,
Thanks for your help. *I have a small hitch.
What I wanted is a Sum(Range) type of solution. *When any values are
added to the Blank Cells the Sum function would get updated.

Also it should clear any previous values in the columns before
inserting the Sum function.

Hope I am clear- Hide quoted text -

- Show quoted text -

This will put the formula on row 45 to sum from row 5 for the column.
Why did you ask the question again in another group. That is a NO NO
netique

Sub subspecificcolumnsSAS() 'sum colums with specific headers
Dim ma
Dim mc As Long
Dim clr As Long
Dim t
ma = Array("aaaa", "bbbb")
For Each t In ma
mc = Rows(4).Find(What:=t, LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, MatchCase _
:=False, SearchFormat:=False).Column
'MsgBox mc
clr = Cells(Rows.Count, mc).End(xlUp).Row
'MsgBox clr
'MsgBox Application.Sum(Range(Cells(4, mc), Cells(clr, mc)))
Cells(45, mc).FormulaR1C1 = "=SUM(R[-40]C:R[-1]C)"
Next t
End Sub

prkhan56[_2_]
Sum Columns with Specific Headings
 
On Aug 5, 5:29*pm, Don Guillett Excel MVP
wrote:
On Aug 4, 11:08*am, prkhan56 wrote:





On Aug 4, 4:46*pm, Don Guillett Excel MVP
wrote:

On Aug 4, 6:33*am, prkhan56 wrote:

Hello All

I am using XP/Office2003

I am trying to sum columns (including blanks) with specific Headings.

My headings are as follows starting in Column B, Row 4
Heading1, Heading2, Heading3,……..Heading11.

Heading numbers will increase/decrease for various reports.

In current report, I have recorded the following macro which sums
Column Heading9 and 10

Sub Macro3 ()
Range("J45").Select
* * ActiveCell.FormulaR1C1 = "=SUM(R[-40]C:R[-1]C)"
* * ActiveWindow.SmallScroll Down:=0
* * Range("K45").Select
* * ActiveCell.FormulaR1C1 = "=SUM(R[-40]C:R[-1]C)"
* * ActiveWindow.SmallScroll Down:=12
End Sub

The Heading names will change if there are fewer columns…but the text
will be same always.

I wish to have a macro which should look for the text eg “aaaa” and
“bbbb” and sum these columns.

Number of Rows will also vary. *The macro should therefore look up for
the text “aaaa” and “bbbb” in Row 4 headings and sum up these two
columns (including blanks)

Any help would be appreciated.

TIA

Rashid Khan

Sub subspecificcolumnsSAS() 'sum colums with specific headers
ma = Array("aaaa", "bbbb")
For Each t In ma
*'MsgBox t
mc = Rows(1).Find(What:=t, LookIn:=xlValues, _
*LookAt:=xlWhole, SearchOrder:=xlByColumns, _
*SearchDirection:=xlNext, MatchCase _
*:=False, SearchFormat:=False).Column
*'MsgBox mc
clr = Cells(Rows.Count, mc).End(xlUp).Row
*'MsgBox clr
*'MsgBox Application.Sum(Range(Cells(4, mc), Cells(clr, mc)))
Cells(45, mc).Value = Application.Sum(Range(Cells(1, mc), Cells(clr,
mc)))
Next t
End Sub- Hide quoted text -

- Show quoted text -

Hello Don,
Thanks for your help. *I have a small hitch.
What I wanted is a Sum(Range) type of solution. *When any values are
added to the Blank Cells the Sum function would get updated.

Also it should clear any previous values in the columns before
inserting the Sum function.

Hope I am clear- Hide quoted text -

- Show quoted text -

This will put the formula on row 45 to sum from row 5 for the column.
Why did you ask the question again in another group. That is a NO NO
netique

Sub subspecificcolumnsSAS() 'sum colums with specific headers
Dim ma
Dim mc As Long
Dim clr As Long
Dim t
ma = Array("aaaa", "bbbb")
For Each t In ma
*mc = Rows(4).Find(What:=t, LookIn:=xlValues, _
*LookAt:=xlWhole, SearchOrder:=xlByColumns, _
*SearchDirection:=xlNext, MatchCase _
*:=False, SearchFormat:=False).Column
*'MsgBox mc
clr = Cells(Rows.Count, mc).End(xlUp).Row
*'MsgBox clr
*'MsgBox Application.Sum(Range(Cells(4, mc), Cells(clr, mc)))
Cells(45, mc).FormulaR1C1 = "=SUM(R[-40]C:R[-1]C)"
Next t
End Sub- Hide quoted text -

- Show quoted text -

Hi Don,
Sorry for the post by one of our 'eager' lady collegue...she does not
know the norms of the groups...and it was her first posting...
I also apologize on her behalf.

I always follow the norms of the groups since many years now.
Thanks for the code. It works fine. Only thing if you would do it for
me please is to modify it to look for the last row used...as the rows
will keep on increasing or decreasing.

Sincere apologies

Rashid Khan

Don Guillett Excel MVP
Sum Columns with Specific Headings
 
On Aug 5, 10:44*am, prkhan56 wrote:
On Aug 5, 5:29*pm, Don Guillett Excel MVP
wrote:





On Aug 4, 11:08*am, prkhan56 wrote:

On Aug 4, 4:46*pm, Don Guillett Excel MVP
wrote:

On Aug 4, 6:33*am, prkhan56 wrote:

Hello All

I am using XP/Office2003

I am trying to sum columns (including blanks) with specific Headings.

My headings are as follows starting in Column B, Row 4
Heading1, Heading2, Heading3,……..Heading11.

Heading numbers will increase/decrease for various reports.

In current report, I have recorded the following macro which sums
Column Heading9 and 10

Sub Macro3 ()
Range("J45").Select
* * ActiveCell.FormulaR1C1 = "=SUM(R[-40]C:R[-1]C)"
* * ActiveWindow.SmallScroll Down:=0
* * Range("K45").Select
* * ActiveCell.FormulaR1C1 = "=SUM(R[-40]C:R[-1]C)"
* * ActiveWindow.SmallScroll Down:=12
End Sub

The Heading names will change if there are fewer columns…but the text
will be same always.

I wish to have a macro which should look for the text eg “aaaa” and
“bbbb” and sum these columns.

Number of Rows will also vary. *The macro should therefore look up for
the text “aaaa” and “bbbb” in Row 4 headings and sum up these two
columns (including blanks)

Any help would be appreciated.

TIA

Rashid Khan

Sub subspecificcolumnsSAS() 'sum colums with specific headers
ma = Array("aaaa", "bbbb")
For Each t In ma
*'MsgBox t
mc = Rows(1).Find(What:=t, LookIn:=xlValues, _
*LookAt:=xlWhole, SearchOrder:=xlByColumns, _
*SearchDirection:=xlNext, MatchCase _
*:=False, SearchFormat:=False).Column
*'MsgBox mc
clr = Cells(Rows.Count, mc).End(xlUp).Row
*'MsgBox clr
*'MsgBox Application.Sum(Range(Cells(4, mc), Cells(clr, mc)))
Cells(45, mc).Value = Application.Sum(Range(Cells(1, mc), Cells(clr,
mc)))
Next t
End Sub- Hide quoted text -

- Show quoted text -

Hello Don,
Thanks for your help. *I have a small hitch.
What I wanted is a Sum(Range) type of solution. *When any values are
added to the Blank Cells the Sum function would get updated.

Also it should clear any previous values in the columns before
inserting the Sum function.

Hope I am clear- Hide quoted text -

- Show quoted text -

This will put the formula on row 45 to sum from row 5 for the column.
Why did you ask the question again in another group. That is a NO NO
netique

Sub subspecificcolumnsSAS() 'sum colums with specific headers
Dim ma
Dim mc As Long
Dim clr As Long
Dim t
ma = Array("aaaa", "bbbb")
For Each t In ma
*mc = Rows(4).Find(What:=t, LookIn:=xlValues, _
*LookAt:=xlWhole, SearchOrder:=xlByColumns, _
*SearchDirection:=xlNext, MatchCase _
*:=False, SearchFormat:=False).Column
*'MsgBox mc
clr = Cells(Rows.Count, mc).End(xlUp).Row
*'MsgBox clr
*'MsgBox Application.Sum(Range(Cells(4, mc), Cells(clr, mc)))
Cells(45, mc).FormulaR1C1 = "=SUM(R[-40]C:R[-1]C)"
Next t
End Sub- Hide quoted text -

- Show quoted text -

Hi Don,
Sorry for the post by one of our 'eager' lady collegue...she does not
know the norms of the groups...and it was her first posting...
I also apologize on her behalf.

I always follow the norms of the groups since many years now.
Thanks for the code. It works fine. Only thing if you would do it for
me please is to modify it to look for the last row used...as the rows
will keep on increasing or decreasing.

Sincere apologies

Rashid Khan- Hide quoted text -

- Show quoted text -

Contact me at dguillett@ gmail.com and send your file so I can see
what you REALLY want.

prkhan56[_2_]
Sum Columns with Specific Headings
 
On Aug 5, 10:55*pm, Don Guillett Excel MVP
wrote:
On Aug 5, 10:44*am, prkhan56 wrote:





On Aug 5, 5:29*pm, Don Guillett Excel MVP
wrote:

On Aug 4, 11:08*am, prkhan56 wrote:

On Aug 4, 4:46*pm, Don Guillett Excel MVP
wrote:

On Aug 4, 6:33*am, prkhan56 wrote:

Hello All

I am using XP/Office2003

I am trying to sum columns (including blanks) with specific Headings.

My headings are as follows starting in Column B, Row 4
Heading1, Heading2, Heading3,……..Heading11.

Heading numbers will increase/decrease for various reports.

In current report, I have recorded the following macro which sums
Column Heading9 and 10

Sub Macro3 ()
Range("J45").Select
* * ActiveCell.FormulaR1C1 = "=SUM(R[-40]C:R[-1]C)"
* * ActiveWindow.SmallScroll Down:=0
* * Range("K45").Select
* * ActiveCell.FormulaR1C1 = "=SUM(R[-40]C:R[-1]C)"
* * ActiveWindow.SmallScroll Down:=12
End Sub

The Heading names will change if there are fewer columns…but the text
will be same always.

I wish to have a macro which should look for the text eg “aaaa” and
“bbbb” and sum these columns.

Number of Rows will also vary. *The macro should therefore look up for
the text “aaaa” and “bbbb” in Row 4 headings and sum up these two
columns (including blanks)

Any help would be appreciated.

TIA

Rashid Khan

Sub subspecificcolumnsSAS() 'sum colums with specific headers
ma = Array("aaaa", "bbbb")
For Each t In ma
*'MsgBox t
mc = Rows(1).Find(What:=t, LookIn:=xlValues, _
*LookAt:=xlWhole, SearchOrder:=xlByColumns, _
*SearchDirection:=xlNext, MatchCase _
*:=False, SearchFormat:=False).Column
*'MsgBox mc
clr = Cells(Rows.Count, mc).End(xlUp).Row
*'MsgBox clr
*'MsgBox Application.Sum(Range(Cells(4, mc), Cells(clr, mc)))
Cells(45, mc).Value = Application.Sum(Range(Cells(1, mc), Cells(clr,
mc)))
Next t
End Sub- Hide quoted text -

- Show quoted text -

Hello Don,
Thanks for your help. *I have a small hitch.
What I wanted is a Sum(Range) type of solution. *When any values are
added to the Blank Cells the Sum function would get updated.

Also it should clear any previous values in the columns before
inserting the Sum function.

Hope I am clear- Hide quoted text -

- Show quoted text -

This will put the formula on row 45 to sum from row 5 for the column.
Why did you ask the question again in another group. That is a NO NO
netique

Sub subspecificcolumnsSAS() 'sum colums with specific headers
Dim ma
Dim mc As Long
Dim clr As Long
Dim t
ma = Array("aaaa", "bbbb")
For Each t In ma
*mc = Rows(4).Find(What:=t, LookIn:=xlValues, _
*LookAt:=xlWhole, SearchOrder:=xlByColumns, _
*SearchDirection:=xlNext, MatchCase _
*:=False, SearchFormat:=False).Column
*'MsgBox mc
clr = Cells(Rows.Count, mc).End(xlUp).Row
*'MsgBox clr
*'MsgBox Application.Sum(Range(Cells(4, mc), Cells(clr, mc)))
Cells(45, mc).FormulaR1C1 = "=SUM(R[-40]C:R[-1]C)"
Next t
End Sub- Hide quoted text -

- Show quoted text -

Hi Don,
Sorry for the post by one of our 'eager' lady collegue...she does not
know the norms of the groups...and it was her first posting...
I also apologize on her behalf.

I always follow the norms of the groups since many years now.
Thanks for the code. It works fine. Only thing if you would do it for
me please is to modify it to look for the last row used...as the rows
will keep on increasing or decreasing.

Sincere apologies

Rashid Khan- Hide quoted text -

- Show quoted text -

Contact me at dguillett@ gmail.com and send your file so I can see
what you REALLY want.- Hide quoted text -

- Show quoted text -

Hi Don
Sent the file couple of days back to your gmail address.
Thanks once again


All times are GMT +1. The time now is 11:44 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com