excel VB selection.find searching outside of selected range

I am trying to create a price check form that searche a fixed range in a worksheet and then returns the price from a different column.

the search funtion works fine untill it reaches the end of the range where it automatically jumps out of the selecte drange and continues searchingthe remainder of the worksheet which i do not want, as if it finds a matching string my activecell.offset commands either return false data or they simply error due to trying to move out of the workbook space as such.
 please find below my code

Private Sub btnNewSearch_Click()

    txtSearch.Value = ""
    txtMis.Value = ""
    txtPrice.Value = ""
    txtItemDescription.Value = ""
    btnNewSearch.Enabled = False
    btnFindNext.Enabled = False
    btnFindPrevious.Enabled = False

End Sub

Private Sub btnPriceCheck_Click()

btnNewSearch.Enabled = True

If txtSearch <> "" Then
    Dim Target As Range
    Sheets("HK_Q").Select
    Range("B16:B42").Select
    Set Target = Selection.find(What:=txtSearch.Value, After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
    xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
    False, SearchFormat:=False)
    If Target Is Nothing Then
        MsgBox "Item Not Found"
        txtPrice.Value = ""
        txtItemDescription.Value = ""
        Exit Sub
    End If
    ActiveCell.Select
       
    txtItemDescription = ActiveCell.Offset(0, -1).Value
    txtPrice.Value = ActiveCell.Offset(0, 4).Value
       
    btnFindNext.Enabled = True
    btnFindPrevious.Enabled = True
    btnPriceCheck.Enabled = False
Else
     If txtMis.Value <> "" Then
        Dim Target2 As Range
        Sheets("HK_Q").Select
        Range("A16:A42").Select
        Set Target2 = Selection.find(What:=txtMis.Value, After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
            xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
            False, SearchFormat:=False)
        If Target2 Is Nothing Then
            MsgBox "Item Not Found"
            txtPrice.Value = ""
            txtItemDescription.Value = ""
            Exit Sub
        End If
        ActiveCell.Select
        txtItemDescription = ActiveCell.Value
        txtPrice.Value = ActiveCell.Offset(0, 5).Value
        btnFindNext.Enabled = True
        btnFindPrevious.Enabled = True
        btnPriceCheck.Enabled = False
     Else
        MsgBox "Please Enter a Search Term"
        cmdCrossref.Enabled = False
    End If
End If

End Sub
mickyblueAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

bruintjeCommented:
Hello mickyblue,

not sure what went wrong in your case but i rewrote the sub a bit and the part i tested with the find did work
---------
Sub test()
Dim sh As Worksheet
Dim Target As Range
Dim Target2 As Range
Dim rngSource As Range

  If txtSearch <> "" Then
    Set sh = Worksheets("HK_Q")
    Set rngSource = sh.Range("B2:B11")
    Set Target = rngSource.Find(What:="text", LookIn:=xlFormulas, _
          LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
          MatchCase:=True)
    If Target Is Nothing Then
      MsgBox "Item Not Found"
      txtPrice.Value = ""
      txtItemDescription.Value = ""
      Exit Sub
    End If
    txtItemDescription = Target.Offset(0, -1).Value
    txtPrice.Value = Target.Offset(0, 4).Value
 
    btnFindNext.Enabled = True
    btnFindPrevious.Enabled = True
    btnPriceCheck.Enabled = False
  ElseIf txtMis.Value <> "" Then
    Set sh = Worksheets("HK_Q")
    Set rngSource = sh.Range("A16:A42")
    Set Target2 = rngSource.Find(What:=txtMis.Value, After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
        xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False)
    If Target2 Is Nothing Then
      MsgBox "Item Not Found"
      txtPrice.Value = ""
      txtItemDescription.Value = ""
      Exit Sub
    End If
    txtItemDescription = Target2.Value
    txtPrice.Value = Target2.Offset(0, 5).Value
   
    btnFindNext.Enabled = True
    btnFindPrevious.Enabled = True
    btnPriceCheck.Enabled = False
  Else
    MsgBox "Please Enter a Search Term"
    cmdCrossref.Enabled = False
  End If

  'cleanup
  Set sh = Nothing
  Set Target = Nothing
  Set Target2 = Nothing
  Set rngSource = Nothing

End Sub
---------


hope this helps a bit
bruintje
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
bruintjeCommented:
would be nice if the asker came back on this
if not then PAQ and points to bruintje
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.