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

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