Create non-contiguius range "IF"

C

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
 
T

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.
 
B

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)
 
C

CLR

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

Thanks Tom,

Vaya con Dios,
Chuck, CABGx3
 
C

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top