Thread: Search and return value to the left? Variables!

Hello,


I have one column with employee names (Column L) and and another column with employee ID numbers (Column M)

I need a macro that when the Employee ID number is searched it returns the Employees Name.

A previous thread helped me and gave me this:

Sub Crebsington()
    Dim SrchString As String
    Dim FoundRng As Range, oCell As Range
    Dim Ws1 As Worksheet
    
    Set Ws1 = Worksheets("Sheet1")
    SrchString = InputBox("Enter the employee number", "")
    If SrchString = "" Then Exit Sub
    Set FoundRng = Ws1.Range(Ws1.Cells(2, 13), Ws1.Cells(Rows.Count, 13).End(xlUp)).Cells.Find(SrchString)
    If Not FoundRng Is Nothing Then
        MsgBox "Employee No: " & SrchString & vbNewLine & "Employee Name: " & FoundRng.Offset(0, -1).Value
    End If
End Sub

But I was hoping that I could Dim both the Employee Name and Employee number both as their own string somethting like this maybe:

For example: 
Dim EmployeeName as string
Employee Name = FoundRng.Offset(0, -1).Value
Msgbox(EmployeeName)
Return "Employee1"

Do you mean like this?

Sub TryMe()
    Dim SrchString As String
    Dim EmployeeName As String
    Dim FoundRng As Range
    Dim Ws1 As Worksheet
    
    Set Ws1 = Worksheets("Sheet1")
    SrchString = InputBox("Enter the employee number", "")
    If SrchString = "" Then Exit Sub
    Set FoundRng = Ws1.Range(Ws1.Cells(2, 13), Ws1.Cells(Rows.Count, 13).End(xlUp)).Cells.Find(SrchString)
    If Not FoundRng Is Nothing Then
        EmployeeName = FoundRng.Offset(0, -1).Value
        MsgBox (EmployeeName)
    End If
End Sub

abousetta

Excellent point JB. Catching these early on saves a lot of time down the road.

abousetta