Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with FindNext Method
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with FindNext Method
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" wrote in message oups.com... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Findnext | Excel Discussion (Misc queries) | |||
Can .FindNext return Nothing?? | Excel Programming | |||
FindNext | Excel Programming | |||
FindNext | Excel Programming | |||
findnext issues | Excel Programming |