Previous Topic Next Topic

Excel 2007 Using vlookup function in VBA

Solved/Closed
gsyrill
Posts
1
Registration date
Saturday October 11, 2014
Status
Member
Last seen
October 11, 2014
 - Oct 11, 2014 at 09:37 AM
 gsyrill - Oct 15, 2014 at 04:32 AM
I have a list of name and employee number in sheet 1 in column a and b.. Then what i want is to by simply typing the employee number in sheet 2 column a the account will automatically fill in column b in all rows. Is there any code in vba for these because when i use vlookup if there are many data to fill the file becomes heavy.

2 replies

Answer 1 / 2
TrowaD
Posts
2886
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
June 27, 2022
 515
Oct 14, 2014 at 11:34 AM
Hi Gsyrill,

Place the following code in Sheet2: 
Private Sub Worksheet_Change(ByVal Target As Range)
Dim fValue As Range
If Intersect(Target, Range("A1")) Is Nothing Then Exit Sub

Set fValue = Sheets("Sheet1").Columns(1).Find(Target)
    If fValue Is Nothing Then
        MsgBox "Value not found."
        Target.Offset(0, 1).ClearContents
        Exit Sub
    Else
        Target.Offset(0, 1) = fValue.Offset(0, 1)
    End If

End Sub


Now type a number in A1 of sheet2 and check if it works.

Best regards,
Trowa
0
Answer 2 / 2
gsyrill
Oct 15, 2014 at 04:32 AM
Thanks.... :)
0