New to userforms - can you help with this code?

[Anthony]

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

 

[Jim May]

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

 

[John Bundy]

Worked wonderfully for me, my guess would be either that your textbox is not
named Textbox1, but more likely that the value you are putting in it is not
an integer as you have i dimensioned. Any other data?

 

[Anthony]

Hi John,
The textbox is named txtRef
and as I am quite new to this I don't understand your statement:
"but more likely that the value you are putting in it is not
an integer as you have i dimensioned"
The value to be input by the user will always be a number
any further help appreciated

 

[John Bundy]

I just meant to make sure they were passing a number. You are setting i =
frmGetJob.TextBox1.Value, if your textbox is named txtRef then it should be
i = frmGetJob.Txtref.Value

 

[Anthony]

John
thanks for your continued help, however this is now starting to drive me
nuts as I still can't get the full code to work. To sumerise, this is what I
want the code to do.

1) User inputs a number into the Textbox of the userform (frmGetJob / txtRef)
2) if no number is entered and Search is clicked - Msg box with error
displayed
3) if number entered is not found through column A of DATA worksheet - msg
box displayed
4) if number entered is found through column A of DATA worksheet, that whole
row is copy/paseted to row2 of the SEARCH JOB RESULTS worksheet

Thats it really!

The code I am trying to get to work is:

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")
If txtRef.Text = "" Then
MsgBox ("Please enter a reference number to search for")
Exit Sub
End If

Sheets("Search Job Results").Activate
nextrow = Application.WorksheetFunction.CountA(Range("A:A")) + 1

i = frmGetJob.txtRef.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

Can I ask you to try and get this to do as I require before I go and push
the Christmas Tree over!!
many thanks
Anthony

 

[JLGWhiz]

If the user puts a "number" in a textbox, it becomes text. If your Find
statement is looking for a match in a range of integers (real numbers) it
will not find a match because i will be "text" rather than a "number"
although i will look like a number in the textbox. If you look at it in VBA
it will probably have quote marks around it to indicate that it is type text.
That is what John is trying to tell you.

 

[Anthony]

Thanks for the explanation that makes sense.
So HOW do I get the value input by the user to become a number so that it
can be searched for and complete the macro?
As I said, I am kind of new to this so sorry if I am asking dumb questions
thanks

 

[JLGWhiz]

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

should coerce the value of i to an integer.

 

[JLGWhiz]

P.S. Don't worry about the questions. None of us were born smart.

 

[John Bundy]

thanks for the help JLGWhiz. Its ALWAYS good to verify that you data types
are the same. When in doubt, test it. You can do this by putting the data
that it should match in say cell A1, then first thing after they enter a
value, do a msgbox i=cells(1,1), if it is true you are good, if not, its a
data type. Its a good idea to do that anytime you compare data, especially
user input data. As an added note to error handling, come to be friends with
trim() it removes excess spaces. For example if your textbox is blank, you
get an error as intended, but if they put a space, it breaks, instead use If
trim(txtRef.Text) = "" Then
and
i=trim(frmGetJob.txtRef.Value)
if you don't then the user accidentally entering a space before or after
won't affect your search.
and just to save you some headache, also call an error if they enter
something that is not numeric with if isnumeric(txtRef.Text)=false then, and
handle the error. Sorry to go on so much but these are very good things for
you to do up front to keep from suffering later.