Items in a discontiguous range
 

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

Items in a discontiguous range
 

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

wrote in message
ps.com...

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

Items in a discontiguous range
 

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


On Nov 30, 3:33 pm, Gary''s Student
wrote:
This is not an answer to your problem, but an alternate method

Function GetNextCellInRange(rngTarget As Range, rngTestCell As Range) As
String
Dim r As Range, b As Boolean
GetNextCellInRange = ""
b = False
For Each r In rngTarget
If b Then
GetNextCellInRange = r.Address
Exit Function
End If

If Intersect(r, rngTestCell) Is Nothing Then
Else
b = True
End If
Next
End Function

Here I made the function return a string because I tested it on the
worksheet. It makes no assumptions on the column or row structure of
rngTarget
--
Gary's Student

" wrote:

I would like to know what the next cell is in adiscontiguousnamed
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