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

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

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?
--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"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

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" wrote:

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?
--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"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

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


--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"Anthony" wrote:

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" wrote:

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?
--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"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

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

"John Bundy" wrote:

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


--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"Anthony" wrote:

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" wrote:

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?
--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"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

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" wrote:

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

"John Bundy" wrote:

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


--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"Anthony" wrote:

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" wrote:

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?
--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"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