Create non-contiguius range "IF"

[CLR]

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

 

[Tom Ogilvy]

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.

 

[Bob Phillips]

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)

 

[Tom Ogilvy]

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

 

[CLR]

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

Thanks Tom,

Vaya con Dios,
Chuck, CABGx3

 

[CLR]

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