Use cell address, not its value, as a function argument

[markjkubicki]

(sorry about the massive subject title - accidentally hit entry before proofing)

________________________
it's been forever since i programmed in excel; and, of all things, i'm stuck at XL101

the formula starts
Function Sum_till_blank(rTarget As Range, vOffset As Integer)

where rtarget ought to be a cell ADDRESS, and vOffset is a number
for a in-cell formula entry "= Sum_till_blank(B29,3)"
the arguments received are
rTarget = "the value of cell B29", NOT the cell address (which is what i want)

it's such a simple issue, continual web searching is making me insane


with much thanks in advance,
mark

[xladept]

Probably:

Please Login or Register  to view this content.

[markjkubicki]

nope, (sorry)
just for ref, here's the whole snippet:

Please Login or Register  to view this content.

[xladept]

What are the arguments that you're entering for the function?

BTW: Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Click on Edit to open your thread, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

[markjkubicki]

apologies for the lack of

Please Login or Register  to view this content.

; i did look for them, as an auto format; never dawned on me to just type them in .

(if I understand your ? correctly)
the in-cell formula entry is: = Sum_till_blank(B29,3)

...and my hunch is that's where the problem is: "b29" being a reference to a cell, of course the value passed is such and not the address "B29"
I tried changing that to: = Sum_till_blank("B29",3) - i knew that wasn't going to work (AH! I was right !)

Please Login or Register  to view this content.

[protonLeah]

That code has several problems. Please explain what it is supposed to do (explain the logic).

[xladept]

This combination might be what you want

Please Login or Register  to view this content.

[markjkubicki]

the spread sheet has consecutive entries in # number of rows (and that number can vary significantly -usually between 1 and 15-ish without rhyme nor reason), followed by at least one blank row, then again by entries in # rows and another blank row, and on again

i want to sum of all of the values in one of the columns for each range of entires


i'm checking the value of a cell in the first row (let's say it's in column (ex: D), that column will always have entries),
then adding the value of an offset column in that row to the total (that offset can also vary... let's say it is an offset of 3, so the value would be in column: G)

then looping the code to check the next row (row +1)
if the column D cell of that row has an entry, then the value of column G is added to the sum (column G may or may not have an entry, and this is OK)
...continue checking to the next row below, and so forth
if the column D cell is empty (= ""), then you've arrive at the end of that block of data entry, and the total will be the formula result

----------------
the usual method has been to use sum() for that range; continually adjusting where the range is. it may be the sum of 2 rows, or of 14; this approach requires continual updating (at each range / checking / correcting...) and is frequently WRONG.

[protonLeah]

Please Login or Register  to view this content.

* no workbook to test with

[markjkubicki]

it sort of worked, but then in noticed that block bottoms were not always correct;
but when i put it in the attached (which is similar except that the values to be summed are formulas - which would most typically be the case...)

attached is a simplified test workbook (but only if your OK with having a look)
...never kick a gift horse in the mouth by mom would say!

[markjkubicki]

but this (and it may not be the most eloquent of code...) WORKED !!!

Please Login or Register  to view this content.