Search loop

[Mileshigh]

I am trying to make a very simple search and cant seem to get it to work

Basicaly I have a txtbox "txtsearch" that a user will type in an order number (this is in column C) and click cmdsearch button. Then the search would find the match and make the active cell the corresponding cell for that row in Column A.

Column A Column C
Sandy 89b40

The search would find the "89b40" number and make the active cell selected "sandy"

Thanks any help would be great

 

[VoG]

Try like this

Private Sub CommandButton1_Click()
Dim Found As Range
Set Found = Columns("C").Find(what:=TextBox1.Text, LookIn:=xlValues, lookat:=xlWhole)
If Not Found Is Nothing Then Found.Offset(, -2).Select
Unload Me
End Sub

 

[SteveO59L]

Something along the lines of

Dim c As Range, rng
Set rng = Range("c1:c1000")
myorder = Application.InputBox("Enter an Order Number")
For Each c In rng
If c.Value = myorder Then
c.Offset(0, -2).Select
End If
Next c

 

[Mileshigh]

Ok so the 1st way works but doesnt report to the user if there are no matches found but rather will just stay on the same cell...which is fine but i want the user to know...

The second method locks up excel everytime

 

[VoG]

Try

Private Sub CommandButton1_Click()
Dim Found As Range
Set Found = Columns("C").Find(what:=TextBox1.Text, LookIn:=xlValues, lookat:=xlWhole)
If Not Found Is Nothing Then
    Found.Offset(, -2).Select
Else
    MsgBox TextBox1.Text & " not found", vbInformation
End If
Unload Me
End Sub

 

[Mileshigh]

Yea thats what i tried first but I keep getting a "else without If" error and nothing i seem to do will work

 

[VoG]

The code I posted works without error.

 

[Mileshigh]

Very weird...so i commented out what I typed in and pasted your post...they are the same and yet i works! Thanks for helping me out