Items in a discontiguous range

[gordon_comstock]

I would like to know what the next cell is in a discontiguous named
range but am having trouble with the item property of the range.

I pass this function the range and the starting cell


Function GetNextCellInRange(rngTarget As Range, rngTestCell As Range)
As Object

Dim iCount As Integer
Dim oCell As Object

iCount = 1

For Each oCell In rngTarget
If oCell.Row = rngTestCell.Row Then
If oCell.Column = rngTestCell.Column Then ' I've found the my
cell
Exit For
End If
End If
iCount = iCount + 1
Next

' get next cell
iCount = iCount + 1

' If no match is found or is past the end, set return cell as first in
range
If iCount > rngTarget.Count Then
iCount = 1
End If

Set GetNextCellInRange = rngTarget.Item(iCount)

End Function


I have a range that consists of two columns - it might consist of more
later.
If I pass the last cell in the first column, or any cell in the second
column, it is found ok but unfortunately rngTarget.Item(icount) refers
to a cell icount rows beneath the start of the first column not the
next cell in the range.

How do I get round this (areas perhaps?). I'd be v. grateful for any
help.

Cheers

Gordon

 

[Peter T]

Hi Gordon,

I would like to know what the next cell is in a discontiguous named
range but am having trouble with the item property of the range.

I haven't looked closely at your function but the normal way is something
like this

Dim rng as Range, rA as Range, rCell as range
For Each rA in rng.Areas
For Each rCell in rA
'code
Next
Next

Regards,
Peter T

 

[gordon_comstock]

GS - Thanks very much, I hadn't thought of letting the For Each...Next
loop go one further. Maybe it was because I was brought up not to use
flags ;-) (I blame that Michael Jackson - well not THAT Michael
Jackson)

Also I'd forgotten (if I'd ever known) about the intersect method -
Thanks.

I still find it curious that the range.item index doesn't work as you'd
expect. There must be an index somewhere as the For Each loop must use
it.

Thanks again

Cheers

Gordon