If I have the following range:

Row/Col A B
1 2 10
2 2 2
3 3 8
4 4 5
5 3 10
6 2 3
7 2 5
8 1 5
9 2 6
10 3 9

Suppose I have a function that is called by MyFunction(B1:B12). Is there
any way within the MyFunction routine to define a range of the numbers in
column B for which column A = 2. If I were doing a SumProduct, the idea
would by SumProduct(--(A1:A10=2),B1:B10). In this case, however, I just want
a range that is the equivilent of (--(A1:A10=2),B1:B10). The new range would
be include cells B1, B2, B6, B7 and B9. I was hoping to be able to do this
without any kind of For/Next loop.

Thanks for your help.

I think the only way you're going to be able to do this is with a For/Next
loop.

If you need assistance with that, come back.
--
HTH,

Barb Reinhardt



"Jay" wrote:

> If I have the following range:
>
> Row/Col A B
> 1 2 10
> 2 2 2
> 3 3 8
> 4 4 5
> 5 3 10
> 6 2 3
> 7 2 5
> 8 1 5
> 9 2 6
> 10 3 9
>
> Suppose I have a function that is called by MyFunction(B1:B12). Is there
> any way within the MyFunction routine to define a range of the numbers in
> column B for which column A = 2. If I were doing a SumProduct, the idea
> would by SumProduct(--(A1:A10=2),B1:B10). In this case, however, I just want
> a range that is the equivilent of (--(A1:A10=2),B1:B10). The new range would
> be include cells B1, B2, B6, B7 and B9. I was hoping to be able to do this
> without any kind of For/Next loop.
>
> Thanks for your help.

Give this macro a try...

Sub SelectBfromA()
Dim x As Long, LastRow As Long, R As Range
Const ValueToFind = 2
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
For x = 1 To LastRow
If Cells(x, "A").Value = ValueToFind Then
If R Is Nothing Then
Set R = Cells(x, "A")
Else
Set R = Union(R, Cells(x, "A"))
End If
End If
Next
R.Offset(0, 1).Select
End Sub

Just set the ValueToFind constant (in the Const statement) to the value you
want to find in Column A.

--
Rick (MVP - Excel)


"Jay" <(E-Mail Removed)> wrote in message
news:60FE5694-90E2-4206-A7B8-(E-Mail Removed)...
> If I have the following range:
>
> Row/Col A B
> 1 2 10
> 2 2 2
> 3 3 8
> 4 4 5
> 5 3 10
> 6 2 3
> 7 2 5
> 8 1 5
> 9 2 6
> 10 3 9
>
> Suppose I have a function that is called by MyFunction(B1:B12). Is there
> any way within the MyFunction routine to define a range of the numbers in
> column B for which column A = 2. If I were doing a SumProduct, the idea
> would by SumProduct(--(A1:A10=2),B1:B10). In this case, however, I just
> want
> a range that is the equivilent of (--(A1:A10=2),B1:B10). The new range
> would
> be include cells B1, B2, B6, B7 and B9. I was hoping to be able to do
> this
> without any kind of For/Next loop.
>
> Thanks for your help.

Rick,

Can he do it with AdvancedFilter(xlFilterInPlace)? If he filters it in
place can't he then take the visible rows? I tried to do it that way but I
kept getting an error saying "Object Required".

Sub FilterData()

Dim MainRange As Range
Dim lngLastRow As Long
Dim MyRange As Range

lngLastRow = Cells(Rows.Count, "B").End(xlUp).Row
Set MainRange = Range("B2:B" & lngLastRow)

Set MyRange = MainRange.AdvancedFilter(Action:=xlFilterInPlace, _
CriteriaRange:=ActiveCell)

MsgBox MyRange.Address

End Sub
--
Cheers,
Ryan


"Rick Rothstein" wrote:

> Give this macro a try...
>
> Sub SelectBfromA()
> Dim x As Long, LastRow As Long, R As Range
> Const ValueToFind = 2
> LastRow = Cells(Rows.Count, "A").End(xlUp).Row
> For x = 1 To LastRow
> If Cells(x, "A").Value = ValueToFind Then
> If R Is Nothing Then
> Set R = Cells(x, "A")
> Else
> Set R = Union(R, Cells(x, "A"))
> End If
> End If
> Next
> R.Offset(0, 1).Select
> End Sub
>
> Just set the ValueToFind constant (in the Const statement) to the value you
> want to find in Column A.
>
> --
> Rick (MVP - Excel)
>
>
> "Jay" <(E-Mail Removed)> wrote in message
> news:60FE5694-90E2-4206-A7B8-(E-Mail Removed)...
> > If I have the following range:
> >
> > Row/Col A B
> > 1 2 10
> > 2 2 2
> > 3 3 8
> > 4 4 5
> > 5 3 10
> > 6 2 3
> > 7 2 5
> > 8 1 5
> > 9 2 6
> > 10 3 9
> >
> > Suppose I have a function that is called by MyFunction(B1:B12). Is there
> > any way within the MyFunction routine to define a range of the numbers in
> > column B for which column A = 2. If I were doing a SumProduct, the idea
> > would by SumProduct(--(A1:A10=2),B1:B10). In this case, however, I just
> > want
> > a range that is the equivilent of (--(A1:A10=2),B1:B10). The new range
> > would
> > be include cells B1, B2, B6, B7 and B9. I was hoping to be able to do
> > this
> > without any kind of For/Next loop.
> >
> > Thanks for your help.
>
> .
>

I think the problem is the code...

MainRange.AdvancedFilter(Action:=xlFilterInPlace, _
CriteriaRange:=ActiveCell)


....is not returning a range object (or any object for that matter). Though,
the filter does get applied to MainRange when you look at the worksheet.
Perhaps this is the way to go if I can then create range variables that
access the filtered columns of MainRange. Thanks for your help, this is
definitely an interesting path to pursue.

Jay


"Ryan H" wrote:

> Rick,
>
> Can he do it with AdvancedFilter(xlFilterInPlace)? If he filters it in
> place can't he then take the visible rows? I tried to do it that way but I
> kept getting an error saying "Object Required".
>
> Sub FilterData()
>
> Dim MainRange As Range
> Dim lngLastRow As Long
> Dim MyRange As Range
>
> lngLastRow = Cells(Rows.Count, "B").End(xlUp).Row
> Set MainRange = Range("B2:B" & lngLastRow)
>
> Set MyRange = MainRange.AdvancedFilter(Action:=xlFilterInPlace, _
> CriteriaRange:=ActiveCell)
>
> MsgBox MyRange.Address
>
> End Sub
> --
> Cheers,
> Ryan
>
>
> "Rick Rothstein" wrote:
>
> > Give this macro a try...
> >
> > Sub SelectBfromA()
> > Dim x As Long, LastRow As Long, R As Range
> > Const ValueToFind = 2
> > LastRow = Cells(Rows.Count, "A").End(xlUp).Row
> > For x = 1 To LastRow
> > If Cells(x, "A").Value = ValueToFind Then
> > If R Is Nothing Then
> > Set R = Cells(x, "A")
> > Else
> > Set R = Union(R, Cells(x, "A"))
> > End If
> > End If
> > Next
> > R.Offset(0, 1).Select
> > End Sub
> >
> > Just set the ValueToFind constant (in the Const statement) to the value you
> > want to find in Column A.
> >
> > --
> > Rick (MVP - Excel)
> >
> >
> > "Jay" <(E-Mail Removed)> wrote in message
> > news:60FE5694-90E2-4206-A7B8-(E-Mail Removed)...
> > > If I have the following range:
> > >
> > > Row/Col A B
> > > 1 2 10
> > > 2 2 2
> > > 3 3 8
> > > 4 4 5
> > > 5 3 10
> > > 6 2 3
> > > 7 2 5
> > > 8 1 5
> > > 9 2 6
> > > 10 3 9
> > >
> > > Suppose I have a function that is called by MyFunction(B1:B12). Is there
> > > any way within the MyFunction routine to define a range of the numbers in
> > > column B for which column A = 2. If I were doing a SumProduct, the idea
> > > would by SumProduct(--(A1:A10=2),B1:B10). In this case, however, I just
> > > want
> > > a range that is the equivilent of (--(A1:A10=2),B1:B10). The new range
> > > would
> > > be include cells B1, B2, B6, B7 and B9. I was hoping to be able to do
> > > this
> > > without any kind of For/Next loop.
> > >
> > > Thanks for your help.
> >
> > .
> >

Rick,

Thank you for this, the Union function is what I was looking for in terms of
creating a range. I have a follow up question for you. Suppose I ran your
macro twice creating two Ranges "R1" and "R2" that have the same number of
cells in each. I'm having a lot of trouble utilizing those two ranges in any
functions. For example,
SumProduct(R1, R2) won't work. Do you have any thoughts about how I could
get any of the WorkSheet functions to work using these two ranges?


"Rick Rothstein" wrote:

> Give this macro a try...
>
> Sub SelectBfromA()
> Dim x As Long, LastRow As Long, R As Range
> Const ValueToFind = 2
> LastRow = Cells(Rows.Count, "A").End(xlUp).Row
> For x = 1 To LastRow
> If Cells(x, "A").Value = ValueToFind Then
> If R Is Nothing Then
> Set R = Cells(x, "A")
> Else
> Set R = Union(R, Cells(x, "A"))
> End If
> End If
> Next
> R.Offset(0, 1).Select
> End Sub
>
> Just set the ValueToFind constant (in the Const statement) to the value you
> want to find in Column A.
>
> --
> Rick (MVP - Excel)
>
>
> "Jay" <(E-Mail Removed)> wrote in message
> news:60FE5694-90E2-4206-A7B8-(E-Mail Removed)...
> > If I have the following range:
> >
> > Row/Col A B
> > 1 2 10
> > 2 2 2
> > 3 3 8
> > 4 4 5
> > 5 3 10
> > 6 2 3
> > 7 2 5
> > 8 1 5
> > 9 2 6
> > 10 3 9
> >
> > Suppose I have a function that is called by MyFunction(B1:B12). Is there
> > any way within the MyFunction routine to define a range of the numbers in
> > column B for which column A = 2. If I were doing a SumProduct, the idea
> > would by SumProduct(--(A1:A10=2),B1:B10). In this case, however, I just
> > want
> > a range that is the equivilent of (--(A1:A10=2),B1:B10). The new range
> > would
> > be include cells B1, B2, B6, B7 and B9. I was hoping to be able to do
> > this
> > without any kind of For/Next loop.
> >
> > Thanks for your help.
>
> .
>

I have some new logic in my code filtering the two ranges R1 and R2 such that
both ranges definitely have the same number of cells. I'm able to use R1 and
R2 separately in functions, for example
Application.WorksheetFunctions.Sum(R1) works fine and
Application.WorksheetFunctions.Sum(R2) works fine.
Application.WorksheetFunctions.SumProduct(R1, R2) crashes. Below is the code
I'm using below. "V" is the column of data being analyzed, the named range
"Code" contains the codes used for filtering the data. It has the same
number of rows as "V".

Sub SelectBfromA(V As Range)
Dim x As Long, LastRow As Long, R1 As Range
Dim y As Long, R2 As Range
Dim ValueToFind As Integer

ValueToFind = 35

For x = 1 To V.Rows.Count
If Worksheets("Data").Cells(x, Range("Code").Column).Value = ValueToFind
Then
If R1 Is Nothing Then
Set R1 = Worksheets("Data").Cells(x, V.Column)
Else
Set R1 = Union(R1, Worksheets("Data").Cells(x, V.Column))
End If
End If
Next


ValueToFind = 32

For y = 1 To V.Rows.Count
If Worksheets("Data").Cells(y, Range("Code").Column).Value = ValueToFind
Then
If R2 Is Nothing Then
Set R2 = Worksheets("Data").Cells(y, V.Column)
Else
Set R2 = Union(R2, Worksheets("Data").Cells(y, V.Column))
End If
End If
Next

'this confirm that the sum function works as expected on R1 and R2
MsgBox Application.WorksheetFunction.Sum(R1)
MsgBox Application.WorksheetFunction.Sum(R2)
'this confirm that R1 and R2 have the same number of cells
MsgBox R1.Cells.Count
MsgBox R2.Cells.Count
'this function crashes
MsgBox Application.WorksheetFunction.SumProduct(R1, R2)
End Sub


"Jay" wrote:

> Rick,
>
> Thank you for this, the Union function is what I was looking for in terms of
> creating a range. I have a follow up question for you. Suppose I ran your
> macro twice creating two Ranges "R1" and "R2" that have the same number of
> cells in each. I'm having a lot of trouble utilizing those two ranges in any
> functions. For example,
> SumProduct(R1, R2) won't work. Do you have any thoughts about how I could
> get any of the WorkSheet functions to work using these two ranges?
>
>
> "Rick Rothstein" wrote:
>
> > Give this macro a try...
> >
> > Sub SelectBfromA()
> > Dim x As Long, LastRow As Long, R As Range
> > Const ValueToFind = 2
> > LastRow = Cells(Rows.Count, "A").End(xlUp).Row
> > For x = 1 To LastRow
> > If Cells(x, "A").Value = ValueToFind Then
> > If R Is Nothing Then
> > Set R = Cells(x, "A")
> > Else
> > Set R = Union(R, Cells(x, "A"))
> > End If
> > End If
> > Next
> > R.Offset(0, 1).Select
> > End Sub
> >
> > Just set the ValueToFind constant (in the Const statement) to the value you
> > want to find in Column A.
> >
> > --
> > Rick (MVP - Excel)
> >
> >
> > "Jay" <(E-Mail Removed)> wrote in message
> > news:60FE5694-90E2-4206-A7B8-(E-Mail Removed)...
> > > If I have the following range:
> > >
> > > Row/Col A B
> > > 1 2 10
> > > 2 2 2
> > > 3 3 8
> > > 4 4 5
> > > 5 3 10
> > > 6 2 3
> > > 7 2 5
> > > 8 1 5
> > > 9 2 6
> > > 10 3 9
> > >
> > > Suppose I have a function that is called by MyFunction(B1:B12). Is there
> > > any way within the MyFunction routine to define a range of the numbers in
> > > column B for which column A = 2. If I were doing a SumProduct, the idea
> > > would by SumProduct(--(A1:A10=2),B1:B10). In this case, however, I just
> > > want
> > > a range that is the equivilent of (--(A1:A10=2),B1:B10). The new range
> > > would
> > > be include cells B1, B2, B6, B7 and B9. I was hoping to be able to do
> > > this
> > > without any kind of For/Next loop.
> > >
> > > Thanks for your help.
> >
> > .
> >

Some more complications...

In trying to use the ranges created from Union, I can't refer to the cells
properly.

If I try the following it works fine:

Dim c as range
For each c in R1.Cells
msgbox c.Address
Next

If I try the following the second iteration when I=2 gives me the address of
the row right below R1.Cells(1,1). It doesn't give me the address of the
second cell in R1:

Dim I As long
For I = 1 to R1.Cells.Count
msgbox R1.Cells(I, 1).Address
Next I





"Rick Rothstein" wrote:

> Give this macro a try...
>
> Sub SelectBfromA()
> Dim x As Long, LastRow As Long, R As Range
> Const ValueToFind = 2
> LastRow = Cells(Rows.Count, "A").End(xlUp).Row
> For x = 1 To LastRow
> If Cells(x, "A").Value = ValueToFind Then
> If R Is Nothing Then
> Set R = Cells(x, "A")
> Else
> Set R = Union(R, Cells(x, "A"))
> End If
> End If
> Next
> R.Offset(0, 1).Select
> End Sub
>
> Just set the ValueToFind constant (in the Const statement) to the value you
> want to find in Column A.
>
> --
> Rick (MVP - Excel)
>
>
> "Jay" <(E-Mail Removed)> wrote in message
> news:60FE5694-90E2-4206-A7B8-(E-Mail Removed)...
> > If I have the following range:
> >
> > Row/Col A B
> > 1 2 10
> > 2 2 2
> > 3 3 8
> > 4 4 5
> > 5 3 10
> > 6 2 3
> > 7 2 5
> > 8 1 5
> > 9 2 6
> > 10 3 9
> >
> > Suppose I have a function that is called by MyFunction(B1:B12). Is there
> > any way within the MyFunction routine to define a range of the numbers in
> > column B for which column A = 2. If I were doing a SumProduct, the idea
> > would by SumProduct(--(A1:A10=2),B1:B10). In this case, however, I just
> > want
> > a range that is the equivilent of (--(A1:A10=2),B1:B10). The new range
> > would
> > be include cells B1, B2, B6, B7 and B9. I was hoping to be able to do
> > this
> > without any kind of For/Next loop.
> >
> > Thanks for your help.
>
> .
>

The Cells property does not iterate cells in a range; rather, it references
the cell at the row and column specified.

--
Rick (MVP - Excel)


"Jay" <(E-Mail Removed)> wrote in message
news:040D7A7F-54DC-4751-846C-(E-Mail Removed)...
> Some more complications...
>
> In trying to use the ranges created from Union, I can't refer to the cells
> properly.
>
> If I try the following it works fine:
>
> Dim c as range
> For each c in R1.Cells
> msgbox c.Address
> Next
>
> If I try the following the second iteration when I=2 gives me the address
> of
> the row right below R1.Cells(1,1). It doesn't give me the address of the
> second cell in R1:
>
> Dim I As long
> For I = 1 to R1.Cells.Count
> msgbox R1.Cells(I, 1).Address
> Next I
>
>
>
>
>
> "Rick Rothstein" wrote:
>
>> Give this macro a try...
>>
>> Sub SelectBfromA()
>> Dim x As Long, LastRow As Long, R As Range
>> Const ValueToFind = 2
>> LastRow = Cells(Rows.Count, "A").End(xlUp).Row
>> For x = 1 To LastRow
>> If Cells(x, "A").Value = ValueToFind Then
>> If R Is Nothing Then
>> Set R = Cells(x, "A")
>> Else
>> Set R = Union(R, Cells(x, "A"))
>> End If
>> End If
>> Next
>> R.Offset(0, 1).Select
>> End Sub
>>
>> Just set the ValueToFind constant (in the Const statement) to the value
>> you
>> want to find in Column A.
>>
>> --
>> Rick (MVP - Excel)
>>
>>
>> "Jay" <(E-Mail Removed)> wrote in message
>> news:60FE5694-90E2-4206-A7B8-(E-Mail Removed)...
>> > If I have the following range:
>> >
>> > Row/Col A B
>> > 1 2 10
>> > 2 2 2
>> > 3 3 8
>> > 4 4 5
>> > 5 3 10
>> > 6 2 3
>> > 7 2 5
>> > 8 1 5
>> > 9 2 6
>> > 10 3 9
>> >
>> > Suppose I have a function that is called by MyFunction(B1:B12). Is
>> > there
>> > any way within the MyFunction routine to define a range of the numbers
>> > in
>> > column B for which column A = 2. If I were doing a SumProduct, the
>> > idea
>> > would by SumProduct(--(A1:A10=2),B1:B10). In this case, however, I
>> > just
>> > want
>> > a range that is the equivilent of (--(A1:A10=2),B1:B10). The new range
>> > would
>> > be include cells B1, B2, B6, B7 and B9. I was hoping to be able to do
>> > this
>> > without any kind of For/Next loop.
>> >
>> > Thanks for your help.
>>
>> .
>>

I think the SUMPRODUCT function requires contiguous ranges to iterate over
and I'm guessing that either R1 or R2 or both are non-contiguous.

--
Rick (MVP - Excel)


"Jay" <(E-Mail Removed)> wrote in message
news:623A941D-3C30-48AB-BC30-(E-Mail Removed)...
>I have some new logic in my code filtering the two ranges R1 and R2 such
>that
> both ranges definitely have the same number of cells. I'm able to use R1
> and
> R2 separately in functions, for example
> Application.WorksheetFunctions.Sum(R1) works fine and
> Application.WorksheetFunctions.Sum(R2) works fine.
> Application.WorksheetFunctions.SumProduct(R1, R2) crashes. Below is the
> code
> I'm using below. "V" is the column of data being analyzed, the named
> range
> "Code" contains the codes used for filtering the data. It has the same
> number of rows as "V".
>
> Sub SelectBfromA(V As Range)
> Dim x As Long, LastRow As Long, R1 As Range
> Dim y As Long, R2 As Range
> Dim ValueToFind As Integer
>
> ValueToFind = 35
>
> For x = 1 To V.Rows.Count
> If Worksheets("Data").Cells(x, Range("Code").Column).Value =
> ValueToFind
> Then
> If R1 Is Nothing Then
> Set R1 = Worksheets("Data").Cells(x, V.Column)
> Else
> Set R1 = Union(R1, Worksheets("Data").Cells(x, V.Column))
> End If
> End If
> Next
>
>
> ValueToFind = 32
>
> For y = 1 To V.Rows.Count
> If Worksheets("Data").Cells(y, Range("Code").Column).Value =
> ValueToFind
> Then
> If R2 Is Nothing Then
> Set R2 = Worksheets("Data").Cells(y, V.Column)
> Else
> Set R2 = Union(R2, Worksheets("Data").Cells(y, V.Column))
> End If
> End If
> Next
>
> 'this confirm that the sum function works as expected on R1 and R2
> MsgBox Application.WorksheetFunction.Sum(R1)
> MsgBox Application.WorksheetFunction.Sum(R2)
> 'this confirm that R1 and R2 have the same number of cells
> MsgBox R1.Cells.Count
> MsgBox R2.Cells.Count
> 'this function crashes
> MsgBox Application.WorksheetFunction.SumProduct(R1, R2)
> End Sub
>
>
> "Jay" wrote:
>
>> Rick,
>>
>> Thank you for this, the Union function is what I was looking for in terms
>> of
>> creating a range. I have a follow up question for you. Suppose I ran
>> your
>> macro twice creating two Ranges "R1" and "R2" that have the same number
>> of
>> cells in each. I'm having a lot of trouble utilizing those two ranges in
>> any
>> functions. For example,
>> SumProduct(R1, R2) won't work. Do you have any thoughts about how I
>> could
>> get any of the WorkSheet functions to work using these two ranges?
>>
>>
>> "Rick Rothstein" wrote:
>>
>> > Give this macro a try...
>> >
>> > Sub SelectBfromA()
>> > Dim x As Long, LastRow As Long, R As Range
>> > Const ValueToFind = 2
>> > LastRow = Cells(Rows.Count, "A").End(xlUp).Row
>> > For x = 1 To LastRow
>> > If Cells(x, "A").Value = ValueToFind Then
>> > If R Is Nothing Then
>> > Set R = Cells(x, "A")
>> > Else
>> > Set R = Union(R, Cells(x, "A"))
>> > End If
>> > End If
>> > Next
>> > R.Offset(0, 1).Select
>> > End Sub
>> >
>> > Just set the ValueToFind constant (in the Const statement) to the value
>> > you
>> > want to find in Column A.
>> >
>> > --
>> > Rick (MVP - Excel)
>> >
>> >
>> > "Jay" <(E-Mail Removed)> wrote in message
>> > news:60FE5694-90E2-4206-A7B8-(E-Mail Removed)...
>> > > If I have the following range:
>> > >
>> > > Row/Col A B
>> > > 1 2 10
>> > > 2 2 2
>> > > 3 3 8
>> > > 4 4 5
>> > > 5 3 10
>> > > 6 2 3
>> > > 7 2 5
>> > > 8 1 5
>> > > 9 2 6
>> > > 10 3 9
>> > >
>> > > Suppose I have a function that is called by MyFunction(B1:B12). Is
>> > > there
>> > > any way within the MyFunction routine to define a range of the
>> > > numbers in
>> > > column B for which column A = 2. If I were doing a SumProduct, the
>> > > idea
>> > > would by SumProduct(--(A1:A10=2),B1:B10). In this case, however, I
>> > > just
>> > > want
>> > > a range that is the equivilent of (--(A1:A10=2),B1:B10). The new
>> > > range
>> > > would
>> > > be include cells B1, B2, B6, B7 and B9. I was hoping to be able to
>> > > do
>> > > this
>> > > without any kind of For/Next loop.
>> > >
>> > > Thanks for your help.
>> >
>> > .
>> >