find data on a sheet using a user form

[bulkodave]

Hello
Thank you for looking at my post and i hope someone can help me.
I am using excel 2010

I have created a user form ( first time doing this). It has 2 text boxes and a command button.

Text box 1 is named txtroutnumber.
Text box 2 is named txttrailerarrived
command button is named cmdfind

What i need to is when i type a number in the txtroutnumber box and press the cmdfind button the txttrailerarrived box is filled with the time relevant to that number on the spreadsheet. i.e. txtroutnumber = 20 txttrailerarrived will say 20:30
the spreadsheet is called Inbound Plan, and route range starts in b10. the trailer arrived starts in e10.

i have tried to adapt a macro i have found, but all is does is clear the txtroutnumber box and nothing is displayed in the txttrailerarrived box.

The macro is as follows:
Private Sub cmdfind_Click()
Dim lastrow
Dim routenum As String
lastrow = Sheets("Inbound Plan").Range("B" & Rows.Count).End(xlUp).Row
routnum = txtroutnumber.Text
For currentrow = 10 To lastrow
If Cells(currentrow, 2).Text = routenum Then


txtroutnumber.Text = Cells(currentrow, 2).Text
txttrailerarrived = Cells(currentrow, 4)


End If
Next currentrow
txtroutnumber.SetFocus




End Sub

any help will be greatly appreciated

Many Thanks
Dave

 

[My Aswer Is This]

You said:
and route range starts in b10. the trailer arrived starts in e10.

But then your script says:

txttrailerarrived = Cells(currentrow, 4)


4 is not column "E" 4 is column "D"

 

[bulkodave]

Hello thank you for your reply i have change to 5 column E, but it is still clearing all the txtboxes:

Private Sub cmdfind_Click()
Dim lastrow
Dim routenum As String
lastrow = Sheets("Inbound Plan").Range("B" & Rows.Count).End(xlUp).Row
routnum = txtroutnumber.Text
For currentrow = 10 To lastrow
If Cells(currentrow, 2).Text = routenum Then


txtroutnumber.Text = Cells(currentrow, 2).Text
txttrailerarrived = Cells(currentrow, 5)


End If
Next currentrow
txtroutnumber.SetFocus




End Sub

 

[My Aswer Is This]

Try this: I found a few problems

Private Sub cmdfind_Click()
 Dim lastrow
 Dim routenum As String
 lastrow = Sheets("Inbound Plan").Range("B" & Rows.Count).End(xlUp).Row
 routnum = txtroutnumber.Text
 
    For currentrow = 10 To lastrow
        If Cells(currentrow, 2).Text = routnum Then
            txttrailerarrived = Cells(currentrow, 5).Text
         End If
    Next currentrow
 txtroutnumber.SetFocus
End Sub

OK

 

[bulkodave]

Thank you that works brilliantly

 

[My Aswer Is This]

Glad I was able to help you. Come back here to Mr. Excel next time you need additional assistance.

Thank you that works brilliantly