Excel 2007 Using vlookup function in VBA Solved/Closed

Question

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.

TrowaD · Answer

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

gsyrill · Answer

Thanks.... :)

Excel 2007 Using vlookup function in VBA

2 responses

Similar discussions

Subscribe To Our Newsletter!