Making one Range out of two
 

Hi,

I have a function that, given a Range of cells, performs some
calculation

Say it can be called like this: =Compute(Rng)

However, I sometimes need that the Range "Rng" be made out of two
simpler ones:

Say I call the function with:

Compute(SomeFunc(A1:A10,C1:C10))

But that I want Compute to behave as if there was only a single Range.

Using some Excel built-in functions (union ranges?) puts me in an
ambiguous situation, since I never know if the union is made
horizontally wise, getting a range that is 10X2 (rows X columns) or
else vertically wise, getting a range that is 20X1.

Does anybody have some ideas as to how could I build SomeFunc"?

Thank you.

Vicente Soler

Making one Range out of two
 

"vsoler" wrote:
Say it can be called like this: =Compute(Rng)

However, I sometimes need that the Range "Rng" be
made out of two simpler ones

Does this solve your problem?

=doit((A1:A10,C1:C10))

Function doit(rng As Range)
Dim cell As Range
For Each cell In rng
Debug.Print cell.Address
Next cell
End Function

Output:

$A$1
$A$2
.....
$A$9
$A$10
$C$1
$C$2
.....
$C$9
$C$10


Using some Excel built-in functions (union ranges?)
puts me in an ambiguous situation, since I never know
if the union is made horizontally wise, getting a
range that is 10X2 (rows X columns) or else vertically
wise, getting a range that is 20X1.

Looks to me like it traverses horizontally first, then vertical.

For example, for:

=doit((A1:B10,C1:Z2))

the output is:

$A$1
$B$1
.....
$A$10
$B$10
$C$1
$D$1
.....
$Y$1
$Z$1
$C$2
$D$2
.....
$Y$2
$Z$2


----- original message -----

"vsoler" wrote in message
...
Hi,

I have a function that, given a Range of cells, performs some
calculation

Say it can be called like this: =Compute(Rng)

However, I sometimes need that the Range "Rng" be made out of two
simpler ones:

Say I call the function with:

Compute(SomeFunc(A1:A10,C1:C10))

But that I want Compute to behave as if there was only a single Range.

Using some Excel built-in functions (union ranges?) puts me in an
ambiguous situation, since I never know if the union is made
horizontally wise, getting a range that is 10X2 (rows X columns) or
else vertically wise, getting a range that is 20X1.

Does anybody have some ideas as to how could I build SomeFunc"?

Thank you.

Vicente Soler

Making one Range out of two
 

In order to get the best answer for your question, I think you will need to
tell us what it is that your function is doing with the ranges passed into
it. If your code is not too long, post that would help as well.

--
Rick (MVP - Excel)


"vsoler" wrote in message
...
Hi,

I have a function that, given a Range of cells, performs some
calculation

Say it can be called like this: =Compute(Rng)

However, I sometimes need that the Range "Rng" be made out of two
simpler ones:

Say I call the function with:

Compute(SomeFunc(A1:A10,C1:C10))

But that I want Compute to behave as if there was only a single Range.

Using some Excel built-in functions (union ranges?) puts me in an
ambiguous situation, since I never know if the union is made
horizontally wise, getting a range that is 10X2 (rows X columns) or
else vertically wise, getting a range that is 20X1.

Does anybody have some ideas as to how could I build SomeFunc"?

Thank you.

Vicente Soler