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

[silver23]

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?

[Bob Phillips]

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" <[email protected]> wrote in message
news:[email protected]...
> 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?

[silver23]

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

[Bob Phillips]

The latter is the simpler way IMO.

--

HTH

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


"silver23" <[email protected]> wrote in message
news:[email protected]...
> 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