User Input to VLOOKUP a number in spreadsheet

[Merckling]

Hey all,

Requesting how to have the user enter a specific 4-5 digit employee ID and then have VBA basically do a Vlookup to retrieve a number within that ID's row and display it in a currency format.

Thanks!

[mdmackillop]

Enter it where? Display it where?

[Merckling]

Enter it through an Inputbox, and display through a Msgbox, my apologies.

EDIT: More specifically, I'm trying to have a basic "Enter employee ID" inputbox for the user to enter an ID ...
VBA searches through column A through approx. 150 employee ID's to make sure there is a match, if there is, it retrieves a value in column I, if not, it displays an error message like "Employee ID does not exist" and asks for another Employee ID.

Thanks again.

[Paul_Hossler]

Something like this maybe?

The attachment has employee number in col A and salary in col B for demo

Col A is formatted as Text, and Col B is formatted as currency






Option Explicit

Sub LookupSalary()
Dim empNum As String
Dim empSalary As Double


empNum = Application.InputBox("Enter Employee Number", "Salary Lookup", vbNullString)

If Len(empNum) = 0 Then Exit Sub


On Error GoTo NotFound

empSalary = Application.WorksheetFunction.VLookup(empNum, Worksheets("Sheet1").Range("A:B"), 2, False)

Call MsgBox("Salary for Employee Number " & empNum & " is " & Format(empSalary, "$#,##0"), vbInformation + vbOKOnly, "Salary Lookup")
Exit Sub
NotFound:
Call MsgBox("Employee Number " & empNum & " not found", vbInformation + vbOKOnly, "Salary Lookup")
End Sub

[Merckling]

Hey Paul,

VBA is not able to recognize any of my Employee ID's in column A. They're all formatted as text as well.

Any idea why?

[mdmackillop]

Why not use a combobox to minimise data entry errors

[Paul_Hossler]

Hey Paul,

VBA is not able to recognize any of my Employee ID's in column A. They're all formatted as text as well.

Any idea why?

Not without seeing a sample, or you could use Mac's combobox approach

[Merckling]

This works, thanks mac.