• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 393
  • Last Modified:

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
0
mickyblue
Asked:
mickyblue
  • 2
1 Solution
 
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
 
bruintjeCommented:
would be nice if the asker came back on this
if not then PAQ and points to bruintje
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now