Using Find to locate data.

[Jietoh]

Greetings all,

I wrote the following script to find an entry in a long list based on two values found in cells V12 & W12. Essentially, W12 contains either "Last Name", or "First Name", while V12 contains the actual text that needs to be found. Based on the entry in W12 it'll select a different column to search for a match to the V12 data. When I tested this code, it works fine when searching for "First Name", but constantly fails to find any match when searching for "Last Name". There is no error code, it simply fails to find any match, valid or not.

Sub Find_Name_Search()
'Find the first or last name and select the table row
Dim x, x1 As Range
Dim ws As Worksheet

On Error GoTo ErrMsg

Set ws = Worksheets("Master List")
Set x = ws.Range("V12")
Set x1 = ws.Range("W12")

ws.Select
If x1 = "First Name" Then
    Columns("C:C").Select
ElseIf x1 = "Last Name" Then
    Columns("B:B").Select
End If
Selection.Find(what:=x, after:=ActiveCell, LookIn:=xlFormulas, _
    lookat:=xlPart, searchorder:=xlByRows, searchdirection:=xlNext, _
    MatchCase:=True, searchformat:=False).Activate

If x1 = "First Name" Then
    Range(ActiveCell.Offset(0, -1), ActiveCell.Offset(0, 4)).Select
ElseIf x1 = "Last Name" Then
    Range(ActiveCell.Offset(0, 5)).Select
End If

Exit Sub

ErrMsg:
    MsgBox ("Cannot Find Match")

End Sub

Thanks in advance.

[davesexcel]

Probably, this is the problem

Change
Range(ActiveCell.Offset(0, 5)).Select
to

 ActiveCell.Offset(0, 5).Select

[davesexcel]

This seems to work as well,

Sub Find_Name_Search()
'Find the first or last name and select the table row
    Dim x As Range, x1 As Range
    Dim ws As Worksheet
    Dim Rng As Range, Fnd As Range
    On Error GoTo ErrMsg

    Set ws = Worksheets("Master List")
    Set x = ws.Range("V12")
    Set x1 = ws.Range("W12")

    ws.Select
    If x1 = "First Name" Then
        Set Rng = ws.Range("C:C")
    ElseIf x1 = "Last Name" Then
        Set Rng = ws.Range("B:B")

    End If
    Set Fnd = Rng.Find(what:=x, lookat:=xlWhole)
    If x1 = "First Name" Then
        Range(Fnd.Offset(0, -1), Fnd.Offset(0, 4)).Select
    ElseIf x1 = "Last Name" Then
        Fnd.Offset(0, 5).Select
    End If

    Exit Sub

ErrMsg:
    MsgBox ("Cannot Find Match")


End Sub

[Jietoh]

Thanks! Such a simple error.