vlookup using vba while allowing manual changes

[dicque]

Hey everyone,

It took me about 5 minutes just to come up with a subject line for my
question.. I just hope that my question itself won't be too confusing
to answer..

I work for a payroll company and have been assigned to create new forms
that will be used by payroll clerks then submitted to data entry
clerks. The way they work is I have one cell (A1) for inputting an
employee identification number (Empl_ID) and another cell (B1) for
retrieving the name of the employee (Empl_Name) from a table of values.

What I need to do is after the user inputs the Empl_ID in A1, then B1
will automatically populate with the Empl_Name from the table of
values. However, if it does not find the Empl_ID in the table, then
the user will have to manually input the Empl_Name in B1.

The simple solution is just to stuff a VLookup in B1, but if the
Empl_Name result comes up as #N/A, I don't want the user to overwrite
the VLookup formula in B1.

I know this is simple, but I've been RTFM'ing for the past 3 days and
just can't come up with the proper formula.. Any help or solutions
would be graciously appreciated.

Thanks very much,
Richard Garrett

 

[Dave Peterson]

Maybe you could use an event macro:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myTable As Range
Dim res As Variant

'one cell at a time
If Target.Cells.Count > 1 Then Exit Sub

'only in column A
If Intersect(Target, Me.Range("a:a")) Is Nothing Then Exit Sub

With Worksheets("sheet2")
Set myTable = .Range("a:e")
End With

On Error GoTo ErrHandler:

res = Application.Match(Target.Value, myTable.Columns(1), 0)

Application.EnableEvents = False
If IsNumeric(res) Then
Target.Offset(0, 1).Value = myTable(res).Offset(0, 1).Value
Else
With Target.Offset(0, 1)
.ClearContents
.Select
MsgBox "Please enter something in: " & .Address(0, 0)
End With
End If

ErrHandler:
Application.EnableEvents = True

End Sub

You can read more about events at:
Chip Pearson's site:
http://www.cpearson.com/excel/events.htm

David McRitchie's site:
http://www.mvps.org/dmcritchie/excel/event.htm

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[dicque]

Thanks Dave, an event macro sounds like exactly what I need but I'm
having trouble interpreting your code that you posted and applying it
to my form. I'll try and explain it a bit furthur, although it's
probably just me who's lacking the experience to alter it for myself..

The form I'm creating is not a list but a full landscape form that has
a couple of merged cells for the employee's ID number and another
couple of merged cells for the employee's name, so right away I hope
that identifies that I'm using Ranges for my two pieces of data. I
believe the ID number is A1:C1 and the name is F1:J1. Then I have a
completely seperate .xls file that is being used for the table of
values, column B contains the employee ID numbers and column D has the
names.

So when one of our payroll clerks punches in the employee's ID number
in A1:C1, what I'm hoping will happen is some sort of vlookup command
will search the table of values, then populate F1:J1 with the
employee's name. If the employee's ID number is not found, then the
payroll clerk has to manually enter the employee's name in F1:J1. I
know that if I stuck a Vlookup formula in F1:J1, I will either get a
matching name or #N/A after the ID number is entered. However if there
is no match, I don't want the payroll clerk to just delete the formula
and enter a name since the forms are on a shared drive. That's why I'm
trying to pursue a method in VBA, in which I have very little
experience.

I hope this all makes sense to help come up with something else.. or
maybe it's all for none and I'm just not interpreting the code
properly. Either way I would really appreciate further assistance with
my problem.

Thanks again,
Richard Garrett