Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting the Sum Total of a variable Range
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?k...UVjosHuUTsXuJw |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting the Sum Total of a variable Range
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 "wutzke" wrote: 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?k...UVjosHuUTsXuJw |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting the Sum Total of a variable Range
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/sh...ad.php?t=87701 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting the Sum Total of a variable Range
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" wrote in message ... 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?k...UVjosHuUTsXuJw |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting the Sum Total of a variable Range
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" wrote in message ... 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" wrote in message ... 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?k...UVjosHuUTsXuJw |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting the Sum Total of a variable Range
can I create a variable in the UDF that holds the sum?
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting the Sum Total of a variable Range
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. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting the Sum Total of a variable Range
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" wrote in message ... can I create a variable in the UDF that holds the sum? |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting the Sum Total of a variable Range
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" 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" wrote in message ... can I create a variable in the UDF that holds the sum? |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting the Sum Total of a variable Range
"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" wrote in message ... 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" 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" wrote in message ... can I create a variable in the UDF that holds the sum? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Subtotals with variable total/sum location | Excel Programming | |||
Subtotals with variable total/sum location | Excel Programming | |||
Total a Variable Based on Another Variable Please | Excel Programming | |||
Total a Variable | Excel Programming | |||
variable counter/total | Excel Programming |