Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to find a cell's location/index within a range?
I'm trying to determine the location of a cell within a range. I know
that you can access cells in a range sequentially using range(_).cells(1), .cells(2), etc. What I'd like to be able to do is determine the 'i' for which range(_).cells(i) is the given cell. I can write a function easily enough to do this, but for economy I was just wondering if there was a trick I'd missed that would do it without. The range is contiguous, but not necessarily rectangular, so I can't simply multiple the row of the range that the cell is in by the column. Anyone know if such an inverse for .cells(i) exists? Thanks very much, Chris |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to find a cell's location/index within a range?
I don't understand the question. cells() takes 2 arguments, not 1
consider the sub Sub enumerate(R As Range) Dim i As Long For i = 1 To R.Cells.Count R.Cells(i).Value = i Next End Sub if you try it with a rectangular range: Sub test() enumerate Range("A1:C5") End Sub it *does* enumerate the range - though the fact that it does so seems to be undocumented. On the other hand, if you try Sub test() enumerate Range("A1:C5,B6:C10") End Sub Then you see that the non-contiguous range is *not* enumerated (A1:C8, A9 is instead). So - what "i" are you looking for? In general, if you want to access the cells sequentially, then use the construct for each cl in R.Cells It should be possible to reverse-engineer the actual order in which that enumeration is done - to find out when a given cell occurs it looks like you would count the number of cells in the range strictly above the given cell and add to it the number of cells in the range in the row to the left of (and including) the given cell in that row. The Intersect function would enter in (but handling a cell in row A would require special care since there are no cells above it) HTH -John Coleman On Jan 25, 3:43 am, wrote: I'm trying to determine the location of a cell within a range. I know that you can access cells in a range sequentially using range(_).cells(1), .cells(2), etc. What I'd like to be able to do is determine the 'i' for which range(_).cells(i) is the given cell. I can write a function easily enough to do this, but for economy I was just wondering if there was a trick I'd missed that would do it without. The range is contiguous, but not necessarily rectangular, so I can't simply multiple the row of the range that the cell is in by the column. Anyone know if such an inverse for .cells(i) exists? Thanks very much, Chris |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to find a cell's location/index within a range?
Hi John,
Thanks for this. I do have a similar sort of procedure which loops through the range and checks to see whether cell(i) matches the chosen cell. This seems to work, but thanks to your example I can see I'm going to run into problems with non-rectangular ranges. I'll bear that in mind. Looping through cell objects, as you suggest, may work better. Thanks for your help, Chris PS. The reason I knew about the single-argument usage of cells() was through this introductory site: http://www.anthony-vba.kefra.com/vba/vbabasic2.htm On 25 Jan, 13:28, "John Coleman" wrote: I don't understand the question. cells() takes 2 arguments, not 1 consider the sub Sub enumerate(R As Range) Dim i As Long For i = 1 To R.Cells.Count R.Cells(i).Value = i Next End Sub if you try it with a rectangular range: Sub test() enumerate Range("A1:C5") End Sub it *does* enumerate the range - though the fact that it does so seems to be undocumented. On the other hand, if you try Sub test() enumerate Range("A1:C5,B6:C10") End Sub Then you see that the non-contiguous range is *not* enumerated (A1:C8, A9 is instead). So - what "i" are you looking for? In general, if you want to access the cells sequentially, then use the construct for each cl in R.Cells It should be possible to reverse-engineer the actual order in which that enumeration is done - to find out when a given cell occurs it looks like you would count the number of cells in the range strictly above the given cell and add to it the number of cells in the range in the row to the left of (and including) the given cell in that row. The Intersect function would enter in (but handling a cell in row A would require special care since there are no cells above it) HTH -John Coleman On Jan 25, 3:43 am, wrote: I'm trying to determine the location of a cell within a range. I know that you can access cells in a range sequentially using range(_).cells(1), .cells(2), etc. What I'd like to be able to do is determine the 'i' for which range(_).cells(i) is the given cell. I can write a function easily enough to do this, but for economy I was just wondering if there was a trick I'd missed that would do it without. The range is contiguous, but not necessarily rectangular, so I can't simply multiple the row of the range that the cell is in by the column. Anyone know if such an inverse for .cells(i) exists? Thanks very much, Chris- Hide quoted text -- Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using cell's contents as an index to a row ...? | Excel Worksheet Functions | |||
Using cell's contents as an index to a row ... ? | Excel Worksheet Functions | |||
Using cell's contents as an index to a row ... ? | Excel Worksheet Functions | |||
how do i find and replace a portion of a cell's formula? | Excel Discussion (Misc queries) | |||
how to find last row/col index within a selected range | Excel Programming |