Trouble getting an IF THEN statement to work

[jimayers]

Hello, I am having difficulty adding an IF statement withou it causing problems. I need the statement because when the following code searches and finds nothing, it stops. I tried an on error goto, but that made this code not work at all

Sub Lookup(Target As Range)
Application.ScreenUpdating = False
Dim pFind As Range
'target.value is selected on sheet(4) and searches sheet(1) returning multiple values on sheet(4)
Set pFind = Sheets(1).Range("A:Z").find(What:=Target.Value, LookIn:=xlValues, LookAt:=xlPart).Offset(, -4).Resize(1, 11)

Target.Offset(, -4).Resize(1, 11).Value = pFind.Value

Application.ScreenUpdating = True
End Sub

I want to add something like

If pFind = "" then MsgBox("Target value not found")

and have the code wait for next target selection.

Thanks for your time - Jim A

 

[Eric W]

Find returns a cell location (object), not a value. Try:

IF pFind Is Nothing Then MsgBox("Target not found")

 

[jimayers]

Find returns a cell location (object), not a value. Try:

IF pFind Is Nothing Then MsgBox("Target not found")

It stops working at:

Set pFind = Sheets(1).Range("E:E").find(What:=Target.Value, LookIn:=xlValues, LookAt:=xlPart).Offset(, -4).Resize(1, 11)

and it looks like before your suggested line "pFind Is Nothing"

Thanks - Jim A

 

[jimayers]

OK...I tried:

Set pFind = Sheets(1).Range("E:E").find(What:=Target.Value, LookIn:=xlValues, LookAt:=xlPart)
If pFind Is Nothing Then MsgBox ("Target not found")
If pFind Is Nothing Then Exit Sub
Set pFind = pFind.Offset(, -4).Resize(1, 11)
With Worksheets(4).Range("A:P")
Target.Offset(, -4).Resize(1, 11).Value = pFind.Value
End With

This seems to be working so far. Thanks for your help - JA

 

[Eric W]

I got a valid message when I coded the On Error like this:

Public Sub Lookup(Target As Range)

Application.ScreenUpdating = False
Dim pFind As Range
'target.value is selected on sheet(4) and searches sheet(1) returning multiple values on sheet(4)

On Error GoTo Oops:
Set pFind = Sheets(1).Range("E:Z").Find(What:=Target.Value, LookIn:=xlValues, LookAt:=xlPart).Offset(, -4).Resize(1, 11)

Target.Offset(, -4).Resize(1, 11).Value = pFind.Value

Application.ScreenUpdating = True
Exit Sub

Oops:
    MsgBox ("Not found")
    Application.ScreenUpdating = True
End Sub

Edit: I see you found another way to code it. I suspected the Offset might be the culprit somehow. Glad you got it working.