Help with FindNext Method

[Dewey]

I have the following code which is searching through a coloumn of data
looking for a number when it finds it, it crosses checks it with a date
if its the one i'm looking for I store it and continue searching for
the next entry. but it doesn't appear to find any values, when I step
through the code rFound = 'Nothing' and I can't work out why. any help
would be great.

Thanks

Dwight


'Find the all occurances of Notional
Dim rFound As Range
Dim sFirstAddress As String

'Set search range to column

Set rFound = ActiveSheet.Columns(Not_Col).Find(Notional)
'If Notional was found
If Not rFound Is Nothing Then
'Store the address in a variable
sFirstAddress = rFound.Address
'Start a loop
Do
row_temp = rFound.Row
Cust_M_Dt = ActiveSheet.Cells(row_temp, M_Dat_Col).Value

If M_Date = Cust_M_Date Then
If flag = 0 Then
Swp_Rw_1 = row_temp
flag = 1
End If

If flag = 1 Then
Swp_Rw_2 = row_temp
flag = 2
End If
End If

'Find the next cell with Notional
Set rFound = ActiveSheet.Columns(Not_Col).FindNext(rFound)
'Stop when Find loops back to the first cell found
Loop Until rFound.Address = sFirstAddress
End If

[Bernie Deitrick]

Dewey,

A lot was missing from your code, but the example below worked for me, to find the number 4 in
column B where the date in column C is the current day's date. I wasn't sure what you were doing
with the flag, so I commented it out....

HTH,
Bernie
MS Excel MVP

Sub TryNow()
'Find the all occurances of Notional (4) in column B
'that have today's date in column C
Dim rFound As Range
Dim sFirstAddress As String
Dim Notional As Integer
Dim M_Dat_Col As Integer
Dim Not_Col As Integer
Dim row_temp As Long
Dim Cust_M_Date As Date
Dim M_Date As Date

Notional = 4
Not_Col = 2
M_Dat_Col = 3
M_Date = Int(Now)

'Set search range to column

Set rFound = ActiveSheet.Columns(Not_Col).Find(Notional)
'If Notional was found
If Not rFound Is Nothing Then
'Store the address in a variable
sFirstAddress = rFound.Address
'Start a loop
Do
row_temp = rFound.Row
Cust_M_Date = ActiveSheet.Cells(row_temp, M_Dat_Col).Value

If M_Date = Cust_M_Date Then
' If flag = 0 Then
' Swp_Rw_1 = row_temp
' flag = 1
' End If
'
' If flag = 1 Then
' Swp_Rw_2 = row_temp
' flag = 2
' End If

MsgBox "I found a match in row " & row_temp
End If

'Find the next cell with Notional
Set rFound = ActiveSheet.Columns(Not_Col).FindNext(rFound)
'Stop when Find loops back to the first cell found
Loop Until rFound.Address = sFirstAddress
End If

End Sub




"Dewey" <[email protected]> wrote in message
news:[email protected]...
>I have the following code which is searching through a coloumn of data
> looking for a number when it finds it, it crosses checks it with a date
> if its the one i'm looking for I store it and continue searching for
> the next entry. but it doesn't appear to find any values, when I step
> through the code rFound = 'Nothing' and I can't work out why. any help
> would be great.
>
> Thanks
>
> Dwight
>
>
> 'Find the all occurances of Notional
> Dim rFound As Range
> Dim sFirstAddress As String
>
> 'Set search range to column
>
> Set rFound = ActiveSheet.Columns(Not_Col).Find(Notional)
> 'If Notional was found
> If Not rFound Is Nothing Then
> 'Store the address in a variable
> sFirstAddress = rFound.Address
> 'Start a loop
> Do
> row_temp = rFound.Row
> Cust_M_Dt = ActiveSheet.Cells(row_temp, M_Dat_Col).Value
>
> If M_Date = Cust_M_Date Then
> If flag = 0 Then
> Swp_Rw_1 = row_temp
> flag = 1
> End If
>
> If flag = 1 Then
> Swp_Rw_2 = row_temp
> flag = 2
> End If
> End If
>
> 'Find the next cell with Notional
> Set rFound = ActiveSheet.Columns(Not_Col).FindNext(rFound)
> 'Stop when Find loops back to the first cell found
> Loop Until rFound.Address = sFirstAddress
> End If
>