Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Defining a range as a subset of cells in another range
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Defining a range as a subset of cells in another range
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Defining a range as a subset of cells in another range
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" wrote in message ... 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Defining a range as a subset of cells in another range
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" wrote in message ... 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. . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Defining a range as a subset of cells in another range
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" wrote in message ... 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. . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Defining a range as a subset of cells in another range
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" wrote in message ... 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. . |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Defining a range as a subset of cells in another range
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" wrote in message ... 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. . |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Defining a range as a subset of cells in another range
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" wrote in message ... 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. . |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Defining a range as a subset of cells in another range
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" wrote in message ... 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" wrote in message ... 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. . |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Defining a range as a subset of cells in another range
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" wrote in message ... 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" wrote in message ... 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. . |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Defining a range as a subset of cells in another range
The AdvancedFilter is a method... it does not (as far as I know) return a
Range, it performs an action on the worksheet... I don't do much with filtering myself, but my guess would be that you would then need to do things (such as ShowAllData) to that worksheet that was filtered. -- Rick (MVP - Excel) "Ryan H" wrote in message ... 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" wrote in message ... 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. . |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Defining a range as a subset of cells in another range
Rick,
Thanks for this answer and the previous one as well. Both makes sense, and you are correct in your other response to me that the cells are not contiguous. I've come up with another way of accessing the cells in R1 and R2 using R1.Areas. But won't this cause problems if any of the cells in R1 or R2 ARE contiguous? Do you know of a way to access the cells in the range, rather than the column? How do I access the second cell in R1 for example? "Rick Rothstein" wrote: 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" wrote in message ... 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" wrote in message ... 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. . . |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Defining a range as a subset of cells in another range
The Areas property is a collection of ranges of contiguous cells; so
Areas(1) references the first area of contiguous cells, Areas(2) references the second area of contiguous cells and so on. You can iterate through the Areas one at a time in a loop and then in a sub-loop iterate through the cells in each Area. How you handle this depends on what you want your code to do. One caveat with Areas... the areas within Areas iterate in the order each area was added. So, if you, for example, select A1:C4 and E5:H9 in that order, then Areas(1) would correspond too A1:C4 and Areas(2) would correspond to E5:H9. HOWEVER, if you select these same cells in the order E5:H9 and then A1:C4, then Areas(1) would correspond to E5:H9 and Areas(2) would correspond to A1:C4. Because of this, there is no real way to answer your last question... "How do I access the second cell in R1 for example?"... the second cell, whatever that might mean (see my next comments) would be dependent on the order the areas were created in. HOWEVER, there is no real meaning to a "second cell" within a non-contiguous range. Consider this range... C1:E4,A6:C10 (select it on the worksheet so you can better see the arrangement)... which would you consider to be the second cell in the range... D1, C2, B6 or A7? -- Rick (MVP - Excel) "Jay" wrote in message ... Rick, Thanks for this answer and the previous one as well. Both makes sense, and you are correct in your other response to me that the cells are not contiguous. I've come up with another way of accessing the cells in R1 and R2 using R1.Areas. But won't this cause problems if any of the cells in R1 or R2 ARE contiguous? Do you know of a way to access the cells in the range, rather than the column? How do I access the second cell in R1 for example? "Rick Rothstein" wrote: 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" wrote in message ... 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" wrote in message ... 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. . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Defining a Range with Cells(r,c) | Excel Programming | |||
Trying to set a pivot field to a subset of values listed in range of cells | Excel Programming | |||
Trying to set a pivot field to a subset of values listed in range of cells | Excel Programming | |||
Defining a range by the contents of cells? | Excel Programming | |||
Defining Range using Cells | Excel Programming |