Previous Topic Next Topic

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

Answer 1 / 2
Posts
2829
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
November 22, 2021
 490
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
Answer 2 / 2

Thanks.... :)
Ask a question