Return an entire row from a range 1

[mxp346]

I have a form, SearchForm, that has a text box called TicketNumber. Once the button is clicked, I want the value in the text box to be compared to the Column Range called AllTicketNumbers. When it finds a match, I want the entire row output to a worksheet called Search Results. For this case there's only going to be one row returned because each ticket number can only occur once. But, I want to take this code, change the specific names and be able to use it to search other fields. The following code is what I came up with. No errors are found but no results are output either. Any suggestions on what I should change?

For Each cell In Range("AllTicketNumbers&quot
If cell.Value = SearchForm.TicketNumber.Value Then
lRow = Worksheets("Search Results&quot.Range("a1&quot.End(xlDown).Row
Worksheets("Search Results&quot.Range("A" & lRow + 1).Value = cell.Value.EntireRow
End If
Next

 

[RobBroekhuis]

Try this modified version:

For Each cell In Range("AllTicketNumbers&quot
If cell = SearchForm.TicketNumber Then
lRow = Worksheets("Search Results&quot.Range("a1&quot.End(xlDown).Row
cell.entirerow.copy Worksheets("Search Results&quot.Range("A" & lRow + 1)
End If
Next cell

Rob

 

[mxp346]

That code isn't working either. Any other suggestions, Rob or anyone else?

 

[Zathras]

As long as the ticket numbers are unique, you could use something like this (Create the range name "Ticket_Number" for the list of numbers in column "A" and change the hard-coded colum ":F" to what you need):

Sub Macro1()

Sheets("All Ticket Numbers&quot.Select
nRow = WorksheetFunction.Match(SearchForm.TicketNumber.Value, Range("Ticket_Number&quot, 0)
Range("A" & Trim(nRow) & ":F" & Trim(nRow)).Select
Selection.Copy
Sheets("Search Results&quot.Select
lRow = Worksheets("Search Results&quot.Range("a1&quot.End(xlDown).Row
Range("A" & Trim(lRow + 1)).Select
ActiveSheet.Paste
Application.CutCopyMode = False

End Sub

 

[RobBroekhuis]

Tell us how it's not working. Did you put a breakpoint on the line finding the last row in your results sheet, to see if your code ever finds a match?
By the way, you may want to experiment as well with

set cell = range("AllTicketNumbers&quot.find searchform.ticketnumber
if not (cell is nothing) then
(do your copy here)
end if

Rob

 

[mxp346]

Here's what's wrong. I used this code below to search through the repair sites:

Private Sub SearchRepairSite_Click()

Dim Counter As Integer
Counter = 0

With Worksheets("Search Results&quot.Range("A2", "J65536&quot
.ClearContents
End With

For Each cell In Range("AllRepairSites&quot
If cell = SearchForm.RepairSite Then
Counter = Counter + 1
lRow = Worksheets("Search Results&quot.Range("a65356&quot.End(xlUp).Row
cell.EntireRow.Copy Worksheets("Search Results&quot.Range("A" & lRow + 1)
End If
Next cell

Unload Me
Worksheets("Search Results&quot.Visible = True
Worksheets("Search Results&quot.Activate

If Counter = 1 Then
MsgBox "There was 1 result found."
Else
MsgBox "There were " & Counter & " results found."
End If

End Sub


This code works for repair site and job description, both of which have just text in their cells. When I tried to use this code(modified for ticket numbers) which is a just a number, the code doesn't work. The code runs through and each time produces a MsgBox at the end that says There were 0 results found. So there's no errors in the code, but it's not matching up the numbers.

 

[RobBroekhuis]

Did you try the .find approach yet? I would also try using the val function, as in:

If cell = val(SearchForm.TicketNumber) Then

to convert the number in the textbox on your userform to a true number.
Rob

 

[mxp346]

Adding Val() made it work. Thank you very much. Any ideas on what I would want to add if I am looking at a date?

 

[RobBroekhuis]

datevalue() is the appropriate function there.
Rob