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

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

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 -