Effective Method to Detemine the Beginning and End of An Array?
 

A user-defined function inputs several variable-length arrays.

It's task is to sum positive changes between all cells in some of its input
arrays;
while also summing negative changes in other input arrays.

Example one, sum positive changes only in cells A1:An (offset by n cells).
Ignore negative changes. And sum negative changes in cells B1:Bn (offset by
n cells). Ignore positive changes.

Example two, sum positive changes only in cells A50:A-n (offset by -n
cells). Ignore negative changes. And sum negative changes in cells B70:B-n
(offset by -n cells). Ignore positive changes. For clarity, this example
resolves to A50:A20 and B70:B10.

How would one best specify to the function that the second example requires
a start at A50 and B70 and descending; while the first example requires a
start at A1 and B1 and ascending?

Effective Method to Detemine the Beginning and End of An Array?
 

Why would you not just pass the range to the function

Function myUDF(rng As Range)

For Each cell in rng
'do something
Next cell

End Function

--

HTH

RP
(remove nothere from the email address if mailing direct)


"silver23" wrote in message
...
A user-defined function inputs several variable-length arrays.

It's task is to sum positive changes between all cells in some of its
input
arrays;
while also summing negative changes in other input arrays.

Example one, sum positive changes only in cells A1:An (offset by n cells).
Ignore negative changes. And sum negative changes in cells B1:Bn (offset
by
n cells). Ignore positive changes.

Example two, sum positive changes only in cells A50:A-n (offset by -n
cells). Ignore negative changes. And sum negative changes in cells
B70:B-n
(offset by -n cells). Ignore positive changes. For clarity, this example
resolves to A50:A20 and B70:B10.

How would one best specify to the function that the second example
requires
a start at A50 and B70 and descending; while the first example requires a
start at A1 and B1 and ascending?

Effective Method to Detemine the Beginning and End of An Array
 

Passing range A1:A20 works because the udf is coded to identify positive
changes between cells A1:A2 and A2:A3. If I attempt to pass A20:A1, however,
the udf receives A1:A20. In turn, it interprets what are actually negative
changes between cells as positive changes; defeating the purpose of the udf.
How does one pass A20:A1 to a macro/udf? Or is it necessary to pass a second
token, i.e.

Function myUDF(rng As Range, num as Double)

to identify the starting position?

"Bob Phillips" wrote:

Why would you not just pass the range to the function

Function myUDF(rng As Range)

For Each cell in rng
'do something
Next cell

End Function

Effective Method to Detemine the Beginning and End of An Array
 

The latter is the simpler way IMO.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"silver23" wrote in message
...
Passing range A1:A20 works because the udf is coded to identify positive
changes between cells A1:A2 and A2:A3. If I attempt to pass A20:A1,
however,
the udf receives A1:A20. In turn, it interprets what are actually
negative
changes between cells as positive changes; defeating the purpose of the
udf.
How does one pass A20:A1 to a macro/udf? Or is it necessary to pass a
second
token, i.e.

Function myUDF(rng As Range, num as Double)

to identify the starting position?

"Bob Phillips" wrote:

Why would you not just pass the range to the function

Function myUDF(rng As Range)

For Each cell in rng
'do something
Next cell

End Function