Sum Cells based on attributes

[Budget Programmer]

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

[JE McGimpsey]

Take a look here:

http://mcgimpsey.com/excel/udfs/sumbold.html

In article <B6DA0FD1-759B-4241-ACF2-084861E15737@microsoft.com>,
Budget Programmer <BudgetProgrammer@discussions.microsoft.com> 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.

[Budget Programmer]

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 here:
>
> http://mcgimpsey.com/excel/udfs/sumbold.html
>
> In article <B6DA0FD1-759B-4241-ACF2-084861E15737@microsoft.com>,
> Budget Programmer <BudgetProgrammer@discussions.microsoft.com> 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.
>

[JE McGimpsey]

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 <9192682F-7B6A-4E9F-8A2B-1FD895334936@microsoft.com>,
Budget Programmer <BudgetProgrammer@discussions.microsoft.com> 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 here:
> >
> > http://mcgimpsey.com/excel/udfs/sumbold.html
> >
> > In article <B6DA0FD1-759B-4241-ACF2-084861E15737@microsoft.com>,
> > Budget Programmer <BudgetProgrammer@discussions.microsoft.com> 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.
> >

[Tom Ogilvy]

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" <BudgetProgrammer@discussions.microsoft.com> wrote in
message news:9192682F-7B6A-4E9F-8A2B-1FD895334936@microsoft.com...
> 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 here:
> >
> > http://mcgimpsey.com/excel/udfs/sumbold.html
> >
> > In article <B6DA0FD1-759B-4241-ACF2-084861E15737@microsoft.com>,
> > Budget Programmer <BudgetProgrammer@discussions.microsoft.com> 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.
> >

[Budget Programmer]

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" <BudgetProgrammer@discussions.microsoft.com> wrote in
> message news:9192682F-7B6A-4E9F-8A2B-1FD895334936@microsoft.com...
> > 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 here:
> > >
> > > http://mcgimpsey.com/excel/udfs/sumbold.html
> > >
> > > In article <B6DA0FD1-759B-4241-ACF2-084861E15737@microsoft.com>,
> > > Budget Programmer <BudgetProgrammer@discussions.microsoft.com> 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.
> > >
>
>
>