Link to home
Start Free TrialLog in
Avatar of mickyblue
mickyblue

asked on

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
ASKER CERTIFIED SOLUTION
Avatar of Brian Mulder
Brian Mulder
Flag of Netherlands image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
would be nice if the asker came back on this
if not then PAQ and points to bruintje