find first cell not equal to current cell
 

What is the best way to find the next cell down in a column that does
not equal the current cell? Do I have to use a while loop?

while cells(i,2).value = oldCellValue
i = i+1


--
TommySzalapski
------------------------------------------------------------------------
TommySzalapski's Profile: http://www.excelforum.com/member.php...o&userid=25561
View this thread: http://www.excelforum.com/showthread...hreadid=392944

find first cell not equal to current cell
 

Yes, sounds good.
---------------------------------------------------------------
Dim i As Long

On Error GoTo exit_Sub

Do While True
i = i + 1
If ActiveCell.Offset(i, 0).Value < ActiveCell.Value Then
MsgBox "Next Value Cell: " & ActiveCell.Offset(i, 0).Address
Exit Do
End If
Loop

exit_Sub:
---------------------------------------------------------------


HTH,
--
Gary Brown

If this post was helpful, please click the ''''Yes'''' button next to
''''Was this Post Helpfull to you?".


"TommySzalapski" wrote:


What is the best way to find the next cell down in a column that does
not equal the current cell? Do I have to use a while loop?

while cells(i,2).value = oldCellValue
i = i+1


--
TommySzalapski
------------------------------------------------------------------------
TommySzalapski's Profile: http://www.excelforum.com/member.php...o&userid=25561
View this thread: http://www.excelforum.com/showthread...hreadid=392944

find first cell not equal to current cell
 

what about ColumnDifferences method:

Function NextValueCell(Cell As Range) As Range
Const ROWSIZE = 1000 'MAX:16384
Dim c As Range, r As Range
Dim i As Long, j As Long, n As Long
On Error GoTo ErrorHandler
Set c = Cell.Cells(1)
'If c.Worksheet.ProtectContents And _
' Not c.Worksheet.ProtectionMode Then Exit Function
n = c.Worksheet.Rows.Count - c.Row + 1
For i = 1 To n Step ROWSIZE
If i + ROWSIZE < n Then j = ROWSIZE + 1 Else j = n - i + 1
On Error Resume Next
Set r = c(i, 1).Resize(j).ColumnDifferences(c(i, 1))(1)
On Error GoTo ErrorHandler
If Not r Is Nothing Then Exit For
Next
Set NextValueCell = r
Exit Function
ErrorHandler:
Exit Function
End Function

Sub Test()
On Error Resume Next
NextValueCell(ActiveCell).Activate
End Sub

--
HTH,

okaizawa


TommySzalapski wrote:
What is the best way to find the next cell down in a column that does
not equal the current cell? Do I have to use a while loop?

while cells(i,2).value = oldCellValue
i = i+1