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

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

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

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

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

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