Excel 2007 Using vlookup function in VBA [Solved/Closed]

Report
Posts
1
Registration date
Saturday October 11, 2014
Status
Member
Last seen
October 11, 2014
-
 gsyrill -
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

Posts
2657
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 22, 2020
440
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
Thanks.... :)