Hi All.......
If someone would be so kind, I need help to create a non-contiguious range,
based on a similar principal to SUMIF........instead of summing the values
from the cells in column B adjacent to my selections in column A, I would
like to assemble all of the individual cells in column B into a
non-contiguious range and give it a RangeName. Sort of like....
=MAKERANGEIF(A:A,Choice,B:B)

Any guidance would be much appreciated..........

TIA
Vaya con Dios,
Chuck, CABGx3

The general approach would be:

Sub MAKERANGEIF()
Dim rng as Range, rng1 as Range
Dim choice as Variant, cell as Range
Choice = "ABCD"
set rng = Range(Cells(1,1),Cells(rows.count,1).End(xlup))
for each cell in rng
if cell.Value = Choice then
if rng1 is nothing then
set rng1 = cell.offset(0,1)
else
set rng1 = Union(rng1, cell.offset(0,1))
end if
end if
Next
if not rng1 is nothing then
rng1.Name = "MyName"
end if
End Sub

If you really want a UDF, post back.

--
Regards,
Tom Ogilvy


"CLR" wrote in message
...
Hi All.......
If someone would be so kind, I need help to create a non-contiguious
range,
based on a similar principal to SUMIF........instead of summing the values
from the cells in column B adjacent to my selections in column A, I would
like to assemble all of the individual cells in column B into a
non-contiguious range and give it a RangeName. Sort of like....
=MAKERANGEIF(A:A,Choice,B:B)

Any guidance would be much appreciated..........

TIA
Vaya con Dios,
Chuck, CABGx3

Hi Chuck,

Here is an example of one way, not name driven, but it works

=SUMPRODUCT(SUMIF(INDIRECT({"B1","B4","B6","B8"}), "Choice",INDIRECT({"A1","A
4","A6","A8"})))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"CLR" wrote in message
...
Hi All.......
If someone would be so kind, I need help to create a non-contiguious
range,
based on a similar principal to SUMIF........instead of summing the values
from the cells in column B adjacent to my selections in column A, I would
like to assemble all of the individual cells in column B into a
non-contiguious range and give it a RangeName. Sort of like....
=MAKERANGEIF(A:A,Choice,B:B)

Any guidance would be much appreciated..........

TIA
Vaya con Dios,
Chuck, CABGx3

Just for interest,
Sum works as well - doesn't require Sumproduct

--
Regards,
Tom Ogilvy


"Bob Phillips" wrote in message
...
Hi Chuck,

Here is an example of one way, not name driven, but it works


=SUMPRODUCT(SUMIF(INDIRECT({"B1","B4","B6","B8"}), "Choice",INDIRECT({"A1","A
4","A6","A8"})))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"CLR" wrote in message
...
Hi All.......
If someone would be so kind, I need help to create a non-contiguious
range,
based on a similar principal to SUMIF........instead of summing the
values
from the cells in column B adjacent to my selections in column A, I
would
like to assemble all of the individual cells in column B into a
non-contiguious range and give it a RangeName. Sort of like....
=MAKERANGEIF(A:A,Choice,B:B)

Any guidance would be much appreciated..........

TIA
Vaya con Dios,
Chuck, CABGx3

As always.....your code is "Finer than frog hair"..........

Thanks Tom,

Vaya con Dios,
Chuck, CABGx3



"Tom Ogilvy" wrote:

The general approach would be:

Sub MAKERANGEIF()
Dim rng as Range, rng1 as Range
Dim choice as Variant, cell as Range
Choice = "ABCD"
set rng = Range(Cells(1,1),Cells(rows.count,1).End(xlup))
for each cell in rng
if cell.Value = Choice then
if rng1 is nothing then
set rng1 = cell.offset(0,1)
else
set rng1 = Union(rng1, cell.offset(0,1))
end if
end if
Next
if not rng1 is nothing then
rng1.Name = "MyName"
end if
End Sub

If you really want a UDF, post back.

--
Regards,
Tom Ogilvy


"CLR" wrote in message
...
Hi All.......
If someone would be so kind, I need help to create a non-contiguious
range,
based on a similar principal to SUMIF........instead of summing the values
from the cells in column B adjacent to my selections in column A, I would
like to assemble all of the individual cells in column B into a
non-contiguious range and give it a RangeName. Sort of like....
=MAKERANGEIF(A:A,Choice,B:B)

Any guidance would be much appreciated..........

TIA
Vaya con Dios,
Chuck, CABGx3

Thanks Bob, that's very interesting, and I'll for sure put it in my secret
stash for someday, but for this immediate reqirement, Tom hit the nail right
on my head......

Vaya con Dios,
Chuck, CABGx3



"Bob Phillips" wrote:

Hi Chuck,

Here is an example of one way, not name driven, but it works

=SUMPRODUCT(SUMIF(INDIRECT({"B1","B4","B6","B8"}), "Choice",INDIRECT({"A1","A
4","A6","A8"})))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"CLR" wrote in message
...
Hi All.......
If someone would be so kind, I need help to create a non-contiguious
range,
based on a similar principal to SUMIF........instead of summing the values
from the cells in column B adjacent to my selections in column A, I would
like to assemble all of the individual cells in column B into a
non-contiguious range and give it a RangeName. Sort of like....
=MAKERANGEIF(A:A,Choice,B:B)

Any guidance would be much appreciated..........

TIA
Vaya con Dios,
Chuck, CABGx3