Getting the Sum Total of a variable Range

[wutzke]

If I have a a series of values in worksheet across several rows and
columns, is there a way programically find the total if the exact
number of columns and rows to be summed up is not known?

Say that on the 1st sheet b2:g4 have values in them. Row 5 & Column H
are blank, but h4 would be the place to put the sum total - being it
is the end of the range. In the 2nd sheet the values are b2:L7, with
Row 8 & Column M blank. The total would be placed in M7Values are
either Numeric or True/False.

A user function would be written named "SumScale"

example spreadsheet is at
http://spreadsheets.google.com/ccc?key=p5XvDvb4SUVjosHuUTsXuJw

[Jacob Skaria]

Try and feedback

Sub SumScale()
lngLastRow = ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row
lngLastCol = ActiveSheet.Cells(2, Columns.Count).End(xlToLeft).Column
ActiveSheet.Cells(lngLastRow, lngLastCol + 1).Formula = _
WorksheetFunction.Sum(Range(Cells(2, 2), Cells(lngLastRow, lngLastCol)))
End Sub

If this post helps click Yes
---------------
Jacob Skaria

[Rick Rothstein]

Try this UDF (just put it in the cell next to the last cell in your rangeyou
want the sum to be in; for example, put it in H4 if your range is B2:G4)...

Function SumScale() As Double
Dim FirstRow As Long
Dim FirstCol As Long
With ActiveCell
FirstRow = .Offset(, -1).End(xlUp).Row
FirstCol = .Offset(, -1).End(xlToLeft).Column
SumScale = WorksheetFunction.Sum(Range(Cells( _
FirstRow, FirstCol), .Offset(, -1)))
End With
End Function

--
Rick (MVP - Excel)

"wutzke" <michael...@gmail.com> wrote in message
news:0b81653e-374e-400f...@q33g2000pra.googlegroups.com...

[wutzke]

can I create a variable in the UDF that holds the sum?

[gmorris]

Hello,

I'm new here but not new to Excel. Are you saying that you want to sum
a block range, and always put the total in the cell to the right of the
range but on the last row that contains data, and do it
programmatically?

It would depend on how many of these you had on one page. If there will
only be one block range per page like those, the process would be
easier.

In order to come up with a good solution, it would also be handy to
know how this data is getting there and when you want to sum it (or
check it if it is automated somehow). There are numerous ways to do
this, it's just finding the most efficient and easy one! You might
benefit from naming the ranges if that's possible for example. If you
could give a little more information I'd try to help more.

--
gmorris
------------------------------------------------------------------------
gmorris's Profile: http://www.thecodecage.com/forumz/member.php?userid=245
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=87701

[Rick Rothstein]

There is a problem with the UDF that I just posted... if won't update if you
change values inside the range it applies to; and, if I modify it to try and
do that, then it won't work correctly. Try this UDF instead (but make sure
to read the note at the end of this message)...

Function SumScale(Cell As Range) As Double
Dim C As Range

Dim FirstRow As Long
Dim FirstCol As Long

Application.Volatile
Set C = Cell.Offset(, -1)
FirstRow = C.End(xlUp).Row
FirstCol = C.End(xlToLeft).Column
SumScale = WorksheetFunction.Sum(Range(Cells(FirstRow, FirstCol), C))
End Function

Note: This UDF takes an argument and that argument is the cell address that
you place it in. So, for your first example, assuming you have values in
B2:G4, you would put this formula in H4

=SumScale(H4)

--
Rick (MVP - Excel)

"Rick Rothstein" <rick.new...@NO.SPAMverizon.net> wrote in message
news:uKDxH0Pw...@TK2MSFTNGP02.phx.gbl...

[wutzke]

I might have a worksheet with several sheets with differing blocks of
values. Some may be as small as 2 rows by 5 columns, others may by as
large as 6 rows by 14 columns. I can control the starting cell, B2 in
these examples.

So naming each range doesn't seem a solution, as I don't know with
each set what the cells will be.

I would like to capture said range within the macro, for each sheet
and reference it again.

[Rick Rothstein]

I'm not sure what you mean... a UDF is a function that returns a value and
then closes down... can you explain in more detail what you are trying to do
and why you think you need to "hold the sum" in a variable? I would also ask
you... are you definitely looking for a UDF (as you asked for in your
initial post) or are you looking for a macro (you mentioned "macro" in your
response to gmorris)? Perhaps if you tell us exactly what you are trying to
do, what you need will become clearer.

--
Rick (MVP - Excel)

"wutzke" <michael...@gmail.com> wrote in message

news:a38c6beb-6843-47f5...@d19g2000prh.googlegroups.com...

[littleredhairedgirl]

Good discussion. To close to flaming.
Seems pretty clear. Jacob's solution works. Rick's not so much. If you
didn't know where the last cell was, how could you put a formula in
it.

On Apr 19, 12:11 pm, "Rick Rothstein"

<rick.newsNO.S...@NO.SPAMverizon.net> wrote:
> I'm not sure what you mean... a UDF is a function that returns a value and
> then closes down... can you explain in more detail what you are trying to do
> and why you think you need to "hold the sum" in a variable? I would also ask
> you... are you definitely looking for a UDF (as you asked for in your
> initial post) or are you looking for a macro (you mentioned "macro" in your
> response to gmorris)? Perhaps if you tell us exactly what you are trying to
> do, what you need will become clearer.
>
> --
> Rick (MVP - Excel)
>

> "wutzke" <michael.wut...@gmail.com> wrote in message

[Rick Rothstein]

"Close to flaming"? Are you kidding? First off, the ORIGINAL question asked
for a User Defined Function (UDF)... note the word "function"... Jacob
posted a macro, which is not a function, it is a subroutine... UDF's work
automatically, macros need to be called individually. Second, the sample
workbook the OP posted showed a worksheet that had three sets of ranges that
needed to be summed up... Jacob's macro, as written, will not work on that
worksheet. Third, Jacob's macro does not "hold the sum" in a variable as the
OP later asked me; so, for the last two reasons, I'm not completely sure
Jacob's solution "works" in the way the OP wanted. Fourth, as for knowing
where to place the formula... I figured since the OP asked for a UDF, the
formula was to be placed "by eye" and, as data was added (assuming data
would be added), the formula could be moved easily enough (click/drag the
cell's border) to the new location. Truthfully, in total, I was confused as
to what the OP actually wants (maybe needs is a better word), so I asked.
Why do you consider that "close to flaming"?

--
Rick (MVP - Excel)

"littleredhairedgirl" <littlered...@gmail.com> wrote in message
news:e21d1a2d-e54e-4655...@b7g2000pre.googlegroups.com...