maybe i = CInt(frmGetJob.TextBox1.Value)
HTH


"Anthony" wrote:

Hi all,

I have a userform called GetJob in which the user inputs a reference number
into TextBox1
I want this number to be searched for through column A of the DATA worksheet
and then copy the whole row of data when found and paste into row 2 of the
Search Job Results worksheet
I am a little stuck with my code as I can't get the user's input number to
be searched for
Here is what I have so far

Private Sub cmdSearch_Click()

Dim i As Integer
Dim iRow As Integer
Dim Cel As Range
Dim wks1 As Worksheet, wks2 As Worksheet
Dim lLastRow As Long
'On Error GoTo err_handler
Set wks1 = ThisWorkbook.Worksheets("Data")
Set wks2 = ThisWorkbook.Worksheets("Search Job Results")

i = frmGetJob.TextBox1.Value

On Error Resume Next
Set Cel = wks1.Columns("A:A").Find _
(What:=i, _
LookIn:=xlValues, _
LookAt:=xlWhole)
If Cel Is Nothing Then
MsgBox "No job with the number " & i & _
" has been found, please try again! "
Exit Sub
End If
On Error GoTo err_handler
iRow = Cel.Row
wks1.Cells(iRow, 1).EntireRow.Copy Destination _
:=wks2.Cells(2, 1)

Exit Sub

err_handler:
MsgBox Error, , "Err " & Err.Number
End Sub

the code gets stuck on this
i = frmGetJob.TextBox1.Value

any help appreciated to make it work