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:=txtSe arch.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:=txtMi s.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
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:=txtSe
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:=txtMi
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
if not then PAQ and points to bruintje