Sum Columns with Specific Headings

[prkhan56]

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]

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]

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.

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]

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 thesetwo
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]

On Aug 4, 4:46 pm, Don Guillett Excel MVP <[email protected]>
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]

On Aug 4, 4:46 pm, Don Guillett Excel MVP <[email protected]>
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 lookup 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]

On Aug 5, 5:29 pm, Don Guillett Excel MVP <[email protected]>
wrote:

On Aug 4, 4:46 pm, Don Guillett Excel MVP <[email protected]>
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 upthese 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