Find and Copy row to new sheet ??

[rbeaslin]

I have a long list of customer information, which will include many similar names. I'd like to be able to first search and find a matching entry based on a name input (either partial or complete), then have a Yes/No if it is the right one. If yes, then copy that row to another sheet in the same workbook, if not, continue searching through the list. I've started the search as follows:

Sub SearchName()
Dim SearchValue As String
On Error Resume Next
SearchValue = InputBox("Enter Name")
If IsError(Cells.Find(What:=SearchValue, After:=ActiveCell, _
LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False).Activate) = True Then

.FindNext (SearchValue)


MsgBox ("Name Not Found")
End If
End Sub

I cant get the .findnext to work, and I dont know where to start with the copy row. Anyone willing to head me down the right path?

Thanks

 

[Mister H]

Hi rbeaslin:

WELCOME to the Board

I can help you with the FindNext portion but that is about it. SORRY.

Here is a code that I set up on a spreadsheet for a user that will Search the spreadsheet and then you can click Find Next to go the the next cell that contain the search string. Anyway, not sure if it can be used for your particular spreadsheet but thought I would post the code anyway. Good Luck.

Sub Search()
'
Dim Found As Range, tempcell As Range, Response As Integer, X
X = InputBox("Please enter the Text or Numbers you want to find", vbOKCancel)
Set Found = Cells.Find(What:=X)
If X = "" Then Exit Sub
If Found Is Nothing Then
    MsgBox X & " Not Found"
    Exit Sub
Else
    Application.Goto reference:=Found, scroll:=True
End If
Response = MsgBox("Find Next Record", vbYesNo)
If Response = vbYes Then
    Do
        Set tempcell = Cells.FindNext(After:=Found)
        If Found.Row >= tempcell.Row And Found.Column >= tempcell.Column Then
            MsgBox "You are at the last record."
            Exit Do
        End If
        Set Found = tempcell
        Application.Goto reference:=Found, scroll:=True
        Response = MsgBox("Find Next Record", vbYesNo)
        If Response = vbNo Then Exit Do
    Loop
    
End If
End Sub

Take Care,
Mark

 

[rbeaslin]

Thanks, Mark, for the help. That is a lot closer to what I was attempting to do. I'm still looking to copy this row to another sheet, but I'll keep working on that.