Excel 2007 Using vlookup function in VBA

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 responses

TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
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
        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,
Thanks.... :)