Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sum Cells based on attributes
Hello,
I have an excel spreadsheet that has sub-totals which are SUM;s on various bolded rows. Now, at the bottom of the spredsheet I'd like insert a formula which would, for a particular column, add the values of the bolded rows I can find the bolded row using by using: If Selection.Font.Bold = True Then...... and I can insert a statement for one row by using something like: cells(intRowVar, intColVar).Value = "=(R[" & -1 & "]C)" but I can't seem to find a way to concatinate all the cell references together and place them in the last row. Since they use relative referencing, it varies by the time I place it in the final cell. Thanks for your help. -- Programmer on Budget |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sum Cells based on attributes
Take a look he
http://mcgimpsey.com/excel/udfs/sumbold.html In article , Budget Programmer wrote: Hello, I have an excel spreadsheet that has sub-totals which are SUM;s on various bolded rows. Now, at the bottom of the spredsheet I'd like insert a formula which would, for a particular column, add the values of the bolded rows I can find the bolded row using by using: If Selection.Font.Bold = True Then...... and I can insert a statement for one row by using something like: cells(intRowVar, intColVar).Value = "=(R[" & -1 & "]C)" but I can't seem to find a way to concatinate all the cell references together and place them in the last row. Since they use relative referencing, it varies by the time I place it in the final cell. Thanks for your help. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sum Cells based on attributes
Thanks for answering. But I would like to put a SUM formula in a particular
cell, not just a sum of all the values. The formula I'm looking to insert would look something like (=K68+K74+K84). The column is constant, but only the rows are variable. Do you have a way to make the formula? -- Programmer on Budget "JE McGimpsey" wrote: Take a look he http://mcgimpsey.com/excel/udfs/sumbold.html In article , Budget Programmer wrote: Hello, I have an excel spreadsheet that has sub-totals which are SUM;s on various bolded rows. Now, at the bottom of the spredsheet I'd like insert a formula which would, for a particular column, add the values of the bolded rows I can find the bolded row using by using: If Selection.Font.Bold = True Then...... and I can insert a statement for one row by using something like: cells(intRowVar, intColVar).Value = "=(R[" & -1 & "]C)" but I can't seem to find a way to concatinate all the cell references together and place them in the last row. Since they use relative referencing, it varies by the time I place it in the final cell. Thanks for your help. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sum Cells based on attributes
How are the rows variable? If only in that the cells are bold, then,
using my UDF: =SumBold(K1:K100) If they vary in other ways, I'm not sure what you're trying to accomplish. In article , Budget Programmer wrote: Thanks for answering. But I would like to put a SUM formula in a particular cell, not just a sum of all the values. The formula I'm looking to insert would look something like (=K68+K74+K84). The column is constant, but only the rows are variable. Do you have a way to make the formula? -- Programmer on Budget "JE McGimpsey" wrote: Take a look he http://mcgimpsey.com/excel/udfs/sumbold.html In article , Budget Programmer wrote: Hello, I have an excel spreadsheet that has sub-totals which are SUM;s on various bolded rows. Now, at the bottom of the spredsheet I'd like insert a formula which would, for a particular column, add the values of the bolded rows I can find the bolded row using by using: If Selection.Font.Bold = True Then...... and I can insert a statement for one row by using something like: cells(intRowVar, intColVar).Value = "=(R[" & -1 & "]C)" but I can't seem to find a way to concatinate all the cell references together and place them in the last row. Since they use relative referencing, it varies by the time I place it in the final cell. Thanks for your help. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sum Cells based on attributes
for i = 1 to intRowVar - 1
if cells(i,intColVar).Font.Bold then if rng is nothing then set rng = cells(i,intColVar) else set rng = union(rng,cells(i,intColVar) end if endif Next if not rng is nothing then cells(intRowVar, intColVar).Formula = "=Sum( & _ rng.Address(1,1,xlA1,False) & ")" End if I assume intRowVar and intColVar have values since you used them in your example. Change the 1 in 1 to intRowVar-1 to be the row of the first cell you want to check. -- Regards, Tom Ogilvy "Budget Programmer" wrote in message ... Thanks for answering. But I would like to put a SUM formula in a particular cell, not just a sum of all the values. The formula I'm looking to insert would look something like (=K68+K74+K84). The column is constant, but only the rows are variable. Do you have a way to make the formula? -- Programmer on Budget "JE McGimpsey" wrote: Take a look he http://mcgimpsey.com/excel/udfs/sumbold.html In article , Budget Programmer wrote: Hello, I have an excel spreadsheet that has sub-totals which are SUM;s on various bolded rows. Now, at the bottom of the spredsheet I'd like insert a formula which would, for a particular column, add the values of the bolded rows I can find the bolded row using by using: If Selection.Font.Bold = True Then...... and I can insert a statement for one row by using something like: cells(intRowVar, intColVar).Value = "=(R[" & -1 & "]C)" but I can't seem to find a way to concatinate all the cell references together and place them in the last row. Since they use relative referencing, it varies by the time I place it in the final cell. Thanks for your help. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sum Cells based on attributes
Tom,
That did it. That's exactly what I needed. Many Thanks. BTW, you were missing a double-quotes after: "=SUM( The working code looks like: "=SUM(" It was very easy to figure out. Thanks again for all your help. Awesome! -- Programmer on Budget "Tom Ogilvy" wrote: for i = 1 to intRowVar - 1 if cells(i,intColVar).Font.Bold then if rng is nothing then set rng = cells(i,intColVar) else set rng = union(rng,cells(i,intColVar) end if endif Next if not rng is nothing then cells(intRowVar, intColVar).Formula = "=Sum( & _ rng.Address(1,1,xlA1,False) & ")" End if I assume intRowVar and intColVar have values since you used them in your example. Change the 1 in 1 to intRowVar-1 to be the row of the first cell you want to check. -- Regards, Tom Ogilvy "Budget Programmer" wrote in message ... Thanks for answering. But I would like to put a SUM formula in a particular cell, not just a sum of all the values. The formula I'm looking to insert would look something like (=K68+K74+K84). The column is constant, but only the rows are variable. Do you have a way to make the formula? -- Programmer on Budget "JE McGimpsey" wrote: Take a look he http://mcgimpsey.com/excel/udfs/sumbold.html In article , Budget Programmer wrote: Hello, I have an excel spreadsheet that has sub-totals which are SUM;s on various bolded rows. Now, at the bottom of the spredsheet I'd like insert a formula which would, for a particular column, add the values of the bolded rows I can find the bolded row using by using: If Selection.Font.Bold = True Then...... and I can insert a statement for one row by using something like: cells(intRowVar, intColVar).Value = "=(R[" & -1 & "]C)" but I can't seem to find a way to concatinate all the cell references together and place them in the last row. Since they use relative referencing, it varies by the time I place it in the final cell. Thanks for your help. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional Formatting Based of Cells Based on Data Entry in anoth | Excel Discussion (Misc queries) | |||
ATTRIBUTES | Excel Discussion (Misc queries) | |||
Data value display attributes linked to table attributes | Charts and Charting in Excel | |||
Macro (attributes?) | Excel Programming | |||
Can I modify cell attributes (font,color etc.) based on function . | Excel Discussion (Misc queries) |