Find VBA

[jtodd]

Hi
I have this bit of code

Sub find()
rngY = ActiveCell.Value

Sheets("StkFile").Select
Columns("A:F").Select
Selection.find(What:=rngY, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

ActiveCell.Select
End Sub

Which works fine if there is a match if not it comes up with a debug message.

How can I get it to work even if there is no match and give the user some kind of message - not found!

Also is there a way I could get it to work on double clicking a cell and finding that value ??

 

[steve the fish]

See if this works putting into the 'ThisWorkbook' module

Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)

Set Rng = Sheets("StkFile").Columns("A:F").Find(What:=Target.Value, After:=Sheets("StkFile").Range("F" & Rows.Count), LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)

If Not Rng Is Nothing Then
    Application.Goto Rng
Else
    MsgBox "not found"
End If

End Sub

 

[jtodd]

Worked a charm ,

Many thanks

 

[jtodd]

See if this works putting into the 'ThisWorkbook' module

Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)

Set Rng = Sheets("StkFile").Columns("A:F").Find(What:=Target.Value, After:=Sheets("StkFile").Range("F" & Rows.Count), LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)

If Not Rng Is Nothing Then
    Application.Goto Rng
Else
    MsgBox "not found"
End If

End Sub

Many thanks , worked a charm