Find/ Search Not Working Correctly

[Jaye7]

I am having a lot of difficulty with the following.

Basically what happens is I have a userform named search, in textbox1 of the search form you type a part number in and it searchs and then fills the appropriate textboxes with the found results.

It then searchs again for the same value within textbox1 from the next cell in the next row to the bottom of the page, if it finds the part again then it will put the appropriate data in the appropriate textboxes, however if it does not find the value then the textboxes would be blank.

What is going wrong is the script is over-riding the activcell to end range and searching from the start again and putting the first found results in textbox6 whereas there should be no found results.

Private Sub CommandButton2_Click()
Range("A1").Select
On Error Resume Next
Cells.Find(What:=Search.TextBox1.Value, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
Search.TextBox3.Value = ActiveCell.Value
ActiveCell.Offset(0, 2).Select
Search.TextBox4.Value = ActiveCell.Value
ActiveCell.Offset(0, 1).Select
Search.TextBox5.Value = "$ " & ActiveCell.Value
 
If Search.TextBox3.Value = "" Then
MsgBox "No Results Found"
Search.TextBox3.Value = ""
Search.TextBox4.Value = ""
Search.TextBox5.Value = ""
End If

second script to fill textbox6.

Call partfind2
End Sub
Sub partfind2()
ActiveCell.Offset(0, -3).Select
Search.TextBox18.Value = ActiveCell.Address
ActiveCell.Offset(1, 0).Select
ActiveCell.Select
 
Set TopCell = ActiveCell
Set BottomCell = Cells(65535, ActiveCell.Column)
Range(TopCell, BottomCell).Select
Cells.Find(What:=Search.TextBox1.Value, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
 
Search.TextBox6.Value = ActiveCell.Value
 
ActiveCell.Offset(0, 2).Select
Search.TextBox7.Value = ActiveCell.Value
ActiveCell.Offset(0, 1).Select
Search.TextBox8.Value = "$ " & ActiveCell.Value
 
If Search.TextBox6.Value = "" Then
Search.TextBox6.Value = ""
Search.TextBox7.Value = ""
Search.TextBox8.Value = ""
End If
'Call partfind3
End Sub

help would be appreciated

 

[Gary McMaster]

Without studying your code, my first impression is your problem is probably being caused by using "Select" and "Activecell" together. When you select a cell in code it probably becomes the new "Activecell" just like it would if you made the selection in the GUI. That would seem to indicate that your starting point may keep moving in your "Find" statement.

You do not have to "select" a cell to access its data in code.

Gary

 

[Jaye7]

Thanks Gary,

I tried changing the select to activate but it still did the same thing.

I have rewritten my code using additional code and now it works, so I'm happy.

Thanks for your help.