| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Issue with Find
I am using the following statements
Sheets("WAN").select If (Range("K2:K974").Find(What:="RTR23so-5/0/3", LookAt:=xlWhole).Select) = True Then Sheets("LAHORE_1 P Router (RTR23)").Range("D9").Value = ActiveCell.Offset(0, -8).Value Else Sheets("LAHORE_1 P Router (RTR23)").Range("D9").Value = "Not found" End If Everything is okay as long as the search string is found. If there is no match, then an error is returned. I tried using "On Error resume Next" but the resumption took place at the line immediately following the If statement. I do not want this, rather I want the code to continue with the Else statement. Please help. Thanks |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Issue with Find
Hi Farooq
Try the below.. Sub Macro() Dim ws As Worksheet, rngFound As Range Set ws = Sheets("WAN") Set rngFound = ws.Range("K2:K974").Find(What:="RTR23so-5/0/3", LookAt:=xlWhole) If Not rngFound Is Nothing Then Sheets("LAHORE_1 P Router (RTR23)").Range("D9") = rngFound.Offset(0, -8).Value Else Sheets("LAHORE_1 P Router (RTR23)").Range("D9") = "Not found" End If End Sub If this post helps click Yes --------------- Jacob Skaria "Farooq Sheri" wrote: I am using the following statements Sheets("WAN").select If (Range("K2:K974").Find(What:="RTR23so-5/0/3", LookAt:=xlWhole).Select) = True Then Sheets("LAHORE_1 P Router (RTR23)").Range("D9").Value = ActiveCell.Offset(0, -8).Value Else Sheets("LAHORE_1 P Router (RTR23)").Range("D9").Value = "Not found" End If Everything is okay as long as the search string is found. If there is no match, then an error is returned. I tried using "On Error resume Next" but the resumption took place at the line immediately following the If statement. I do not want this, rather I want the code to continue with the Else statement. Please help. Thanks |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Issue with Find
Try this:
Sheets("WAN").Select ismatch = False On Error Resume Next ismatch = Range("K2:K974").Find(What:="RTR23so-5/0/3", LookAt:=xlWhole).Select On Error GoTo 0 If ismatch Then Sheets("LAHORE_1 P Router (RTR23)").Range("D9").Value = ActiveCell.Offset(0, -8).Value Else Sheets("LAHORE_1 P Router (RTR23)").Range("D9").Value = "Not found" End If Regards, Stefi €˛Farooq Sheri€¯ ezt Ć*rta: I am using the following statements Sheets("WAN").select If (Range("K2:K974").Find(What:="RTR23so-5/0/3", LookAt:=xlWhole).Select) = True Then Sheets("LAHORE_1 P Router (RTR23)").Range("D9").Value = ActiveCell.Offset(0, -8).Value Else Sheets("LAHORE_1 P Router (RTR23)").Range("D9").Value = "Not found" End If Everything is okay as long as the search string is found. If there is no match, then an error is returned. I tried using "On Error resume Next" but the resumption took place at the line immediately following the If statement. I do not want this, rather I want the code to continue with the Else statement. Please help. Thanks |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Issue with Find
Thanks for your speedy response. Your method would definitely work but since
I am finally going to have about 1000 similar sequence of "If" statements, this would require extensive changes to my code. Just after posting this thread I changed the order of the statements to the following and it worked. If (Range("K3:K246").Find(What:="RTR01ge-1/0/0", lookat:=xlWhole).Select) = False Then Sheets("LAHORE_1 PE Routers").Range("E6").Value = "Not found" Else Sheets("LAHORE_1 PE Routers").Range("E6").Value = ActiveCell.Offset(0, -8).Value End If Thanks again. I am also rating your reply. "Jacob Skaria" wrote: Hi Farooq Try the below.. Sub Macro() Dim ws As Worksheet, rngFound As Range Set ws = Sheets("WAN") Set rngFound = ws.Range("K2:K974").Find(What:="RTR23so-5/0/3", LookAt:=xlWhole) If Not rngFound Is Nothing Then Sheets("LAHORE_1 P Router (RTR23)").Range("D9") = rngFound.Offset(0, -8).Value Else Sheets("LAHORE_1 P Router (RTR23)").Range("D9") = "Not found" End If End Sub If this post helps click Yes --------------- Jacob Skaria "Farooq Sheri" wrote: I am using the following statements Sheets("WAN").select If (Range("K2:K974").Find(What:="RTR23so-5/0/3", LookAt:=xlWhole).Select) = True Then Sheets("LAHORE_1 P Router (RTR23)").Range("D9").Value = ActiveCell.Offset(0, -8).Value Else Sheets("LAHORE_1 P Router (RTR23)").Range("D9").Value = "Not found" End If Everything is okay as long as the search string is found. If there is no match, then an error is returned. I tried using "On Error resume Next" but the resumption took place at the line immediately following the If statement. I do not want this, rather I want the code to continue with the Else statement. Please help. Thanks |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Issue with Find
Thanks. It sure works but just after posting the thread I changed the order
of the statements within "If" If (Range("K3:K246").Find(What:="RTR01ge-1/0/0", lookat:=xlWhole).Select) = False Then Sheets("LAHORE_1 PE Routers").Range("E6").Value = "Not found" Else Sheets("LAHORE_1 PE Routers").Range("E6").Value = ActiveCell.Offset(0, -8).Value End If Thanks again. I am rating your mail in any case because it is a correct solution. "Stefi" wrote: Try this: Sheets("WAN").Select ismatch = False On Error Resume Next ismatch = Range("K2:K974").Find(What:="RTR23so-5/0/3", LookAt:=xlWhole).Select On Error GoTo 0 If ismatch Then Sheets("LAHORE_1 P Router (RTR23)").Range("D9").Value = ActiveCell.Offset(0, -8).Value Else Sheets("LAHORE_1 P Router (RTR23)").Range("D9").Value = "Not found" End If Regards, Stefi €˛Farooq Sheri€¯ ezt Ć*rta: I am using the following statements Sheets("WAN").select If (Range("K2:K974").Find(What:="RTR23so-5/0/3", LookAt:=xlWhole).Select) = True Then Sheets("LAHORE_1 P Router (RTR23)").Range("D9").Value = ActiveCell.Offset(0, -8).Value Else Sheets("LAHORE_1 P Router (RTR23)").Range("D9").Value = "Not found" End If Everything is okay as long as the search string is found. If there is no match, then an error is returned. I tried using "On Error resume Next" but the resumption took place at the line immediately following the If statement. I do not want this, rather I want the code to continue with the Else statement. Please help. Thanks |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Issue with Find
You are welcome! Thanks for the feedback!
Stefi €˛Farooq Sheri€¯ ezt Ć*rta: Thanks. It sure works but just after posting the thread I changed the order of the statements within "If" If (Range("K3:K246").Find(What:="RTR01ge-1/0/0", lookat:=xlWhole).Select) = False Then Sheets("LAHORE_1 PE Routers").Range("E6").Value = "Not found" Else Sheets("LAHORE_1 PE Routers").Range("E6").Value = ActiveCell.Offset(0, -8).Value End If Thanks again. I am rating your mail in any case because it is a correct solution. "Stefi" wrote: Try this: Sheets("WAN").Select ismatch = False On Error Resume Next ismatch = Range("K2:K974").Find(What:="RTR23so-5/0/3", LookAt:=xlWhole).Select On Error GoTo 0 If ismatch Then Sheets("LAHORE_1 P Router (RTR23)").Range("D9").Value = ActiveCell.Offset(0, -8).Value Else Sheets("LAHORE_1 P Router (RTR23)").Range("D9").Value = "Not found" End If Regards, Stefi €˛Farooq Sheri€¯ ezt Ć*rta: I am using the following statements Sheets("WAN").select If (Range("K2:K974").Find(What:="RTR23so-5/0/3", LookAt:=xlWhole).Select) = True Then Sheets("LAHORE_1 P Router (RTR23)").Range("D9").Value = ActiveCell.Offset(0, -8).Value Else Sheets("LAHORE_1 P Router (RTR23)").Range("D9").Value = "Not found" End If Everything is okay as long as the search string is found. If there is no match, then an error is returned. I tried using "On Error resume Next" but the resumption took place at the line immediately following the If statement. I do not want this, rather I want the code to continue with the Else statement. Please help. Thanks |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| FIND & REPLACE ISSUE | Excel Discussion (Misc queries) | |||
| find and replace issue in macro | New Users to Excel | |||
| Find Issue (Menu Find) | Excel Discussion (Misc queries) | |||
| Excel find and replace issue | Excel Discussion (Misc queries) | |||
| Excel find and replace issue | Excel Discussion (Misc queries) | |||
Linear Mode
