Find?

[wailingrecluse]

Hi all,

I hope someone can help with this....

I have a column of numbers, 1's and 0's... I would like to search through the column until i come to the first 0 in the sequence. I would then like to check if the next number is a 0. If the next number is NOT a 0, then I would like to move on to the next 0. If the number following the 0 IS a 0, then I would like to return the number of the row containing the non 0

Does this make sense? and can anyone help?

Thanks,

WR

 

[HaHoBe]

Hi, WR,

try this:

Sub wailingrecluse_Find()
'Find is trestriucted to Columns("A:A") of the active sheet
'Change this for your needs as well as the strSearch

Dim rngFound As Range
Dim strSearch As String
Dim strAddress As String

Range("A1").Select

strSearch = 0
Set rngFound = ActiveSheet.Columns("A:A").Find( _
    what:=strSearch, _
    LookIn:=xlValues, _
    lookat:=xlWhole, _
    searchorder:=xlByRows)
If rngFound Is Nothing Then
    Beep
    MsgBox prompt:="Couldn`t find a match for " & strSearch
    GoTo get_out
End If
strAddress = rngFound.Address
If rngFound.Offset(1, 0) = strSearch Then
     MsgBox "First encounter at row " & rngFound.Row
     Exit Sub
End If
While ActiveCell.Address <> strAddress
    Set rngFound = Cells.FindNext(After:=rngFound)
    If rngFound.Address = strAddress Then Exit Sub
    If rngFound.Offset(1, 0) = strSearch Then
         MsgBox "First encounter at row " & rngFound.Row
         GoTo get_out
    End If
Wend

get_out:
Set rngFound = Nothing

End Sub

Ciao,
Holger

 

[wailingrecluse]

Hi, WR,

try this:

Sub wailingrecluse_Find()
'Find is trestriucted to Columns("A:A") of the active sheet
'Change this for your needs as well as the strSearch

Dim rngFound As Range
Dim strSearch As String
Dim strAddress As String

Range("A1").Select

strSearch = 0
Set rngFound = ActiveSheet.Columns("A:A").Find( _
    what:=strSearch, _
    LookIn:=xlValues, _
    lookat:=xlWhole, _
    searchorder:=xlByRows)
If rngFound Is Nothing Then
    Beep
    MsgBox prompt:="Couldn`t find a match for " & strSearch
    GoTo get_out
End If
strAddress = rngFound.Address
If rngFound.Offset(1, 0) = strSearch Then
     MsgBox "First encounter at row " & rngFound.Row
     Exit Sub
End If
While ActiveCell.Address <> strAddress
    Set rngFound = Cells.FindNext(After:=rngFound)
    If rngFound.Address = strAddress Then Exit Sub
    If rngFound.Offset(1, 0) = strSearch Then
         MsgBox "First encounter at row " & rngFound.Row
         GoTo get_out
    End If
Wend

get_out:
Set rngFound = Nothing

End Sub

Ciao,
Holger

Hi Holger

Thanks for posting, but i figured out a pretty crude way of doing it using just formulas