vlookup using vba while allowing manual changes
 

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

vlookup using vba while allowing manual changes
 

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

wrote:

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

vlookup using vba while allowing manual changes
 

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