Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Headings of columns now have numbers, need to change them to lette | Excel Worksheet Functions | |||
I want to get rid of columns with numbers as headings, how do I? | Excel Discussion (Misc queries) | |||
Hidden Columns and Row Headings | Excel Discussion (Misc queries) | |||
Repeating Columns Headings | Excel Discussion (Misc queries) | |||
How do center headings across columns in excel | Excel Worksheet Functions |