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

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.

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.

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.

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.

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.