MsgBox when Value not found.

[Scarlacc]

Morning,
Having Trouble putting message box when my Text search result turns nothing found. tried doing if statement different ways but had no luck getting it to work. what it is doing is after if finds the Value from text box it will copy 7 rows down and 3 columns over and paste it on sheet 2 from sheet 5. Still need to add Screen control was waiting till i got it all done. included pics of the both sheets.

here is the Code that currently works to pull data from sheet5(Data Dump 2) to sheet2(Broker Search). just want a message box if it finds nothing instead of error you normally get.

Sub SearchStop5()

Sheets("Data Dump 2").Visible = True
Sheets("Data Dump 2").Select
Range("A1").Select
Range("A3").EntireColumn.Select

Selection.Find(What:=Sheet2.Cells(2, 4).Value, After:=ActiveCell, LookIn:=xlValues, _
Lookat:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Select

ActiveCell.Range("A1:C7").Select
Selection.Copy
Sheets("Broker Search").Select
Range("G19").Select
ActiveSheet.Paste

Sheets("Data Dump 2").Visible = False

End Sub


Thanks

 

[Georgiboy]

The below may help:

Sub SearchStop5()
    Dim wsDD2 As Worksheet
    Dim FindRng As Range
    
    Set wsDD2 = Sheets("Data Dump 2")
    Set FindRng = wsDD2.Range("A:A").Find(What:=Sheet2.Cells(2, 4).Value, Lookat:=xlPart)
    If Not FindRng Is Nothing Then
        MsgBox "Item found @ " & FindRng.Address
    Else
        MsgBox "Nothing found"
    End If
End Sub

 

[Scarlacc]

The below may help:

Sub SearchStop5()
    Dim wsDD2 As Worksheet
    Dim FindRng As Range
   
    Set wsDD2 = Sheets("Data Dump 2")
    Set FindRng = wsDD2.Range("A:A").Find(What:=Sheet2.Cells(2, 4).Value, Lookat:=xlPart)
    If Not FindRng Is Nothing Then
        MsgBox "Item found @ " & FindRng.Address
    Else
        MsgBox "Nothing found"
    End If
End Sub

Thanks ill try tonight when at work and let you know

 

[Scarlacc]

it finds right where it is.. What im trying to do is from find selection copy over two columns and down six rows to the search sheet. witch i accomplished with code I wrote just cant seem to figure out a message box if it turns up nothing. Having a hard time learning the if's and loops sure with practice and time i will get it down.

 

[Scarlacc]

No other Help Needed i forgot about the If Error GoTo Statement. That fixed my issue. here is what i did.

Sub SearchStop5()

On Error GoTo TryAgain

Sheets("Data Dump 2").Visible = True
Sheets("Data Dump 2").Select
Range("A1").Select
Range("A3").EntireColumn.Select

Selection.Find(What:=Sheet2.Cells(2, 4).Value, After:=ActiveCell, LookIn:=xlValues, _
Lookat:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Select

ActiveCell.Range("A1:C7").Select
Selection.Copy
Sheets("Broker Search").Select
Range("G19").Select
ActiveSheet.Paste

Sheets("Data Dump 2").Visible = False

Exit Sub

TryAgain:
MsgBox "Customer Not Found", , "Try Again"
Sheets("Data Dump 2").Visible = False

End Sub