v-lookup function

[asyamonique]

Good Day,
Is it possible to run v-look up function for any cell with a code.
if the cell value changes the next row will be loaded automaticly with related info from another sheet.
the datas stored in sheet2 ,column"a" has the member id no's and column"b" has their names as well.
And if the user types the members id number on any cell in sheet1 the next row will be loaded with their names...
Many Thanks

 

[pedie]

try something like this; place this code in worksheet module;

[/FONT]
[FONT=Courier New]Private Sub Worksheet_Change(ByVal Target As Range)
 Application.EnableEvents = False
 ActiveCell.Value = Application.WorksheetFunction.VLookup(ActiveCell.Offset(-1, 0).Value, Sheets("Sheet2").Range("A1:B65000"), 2, 0)
 Application.EnableEvents = True
End Sub

 

[asyamonique]

try something like this; place this code in worksheet module;

[FONT=Courier New]Private Sub Worksheet_Change(ByVal Target As Range)[/FONT]
[FONT=Courier New]Application.EnableEvents = False[/FONT]
[FONT=Courier New]ActiveCell.Value = Application.WorksheetFunction.VLookup(ActiveCell.Offset(-1, 0).Value, Sheets("Sheet2").Range("A1:B65000"), 2, 0)[/FONT]
[FONT=Courier New]Application.EnableEvents = True[/FONT]
[FONT=Courier New]End Sub[/FONT]

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
 Application.EnableEvents = False
 ActiveCell.Value = Application.WorksheetFunction.VLookup(ActiveCell.Offset(-1, 0).Value, Sheets("Sheet2").Range("A1:B65000"), 2, 0)
 Application.EnableEvents = True
End Sub

Thanks Pedie,
Thats very good but how how we can change it to next right cell?like if entry d4 result will be on d5...

 

[pedie]

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
Application.EnableEvents = False
ActiveCell.Offset(-1, 1).Value = Application.WorksheetFunction.VLookup(ActiveCell.Offset(-1, 0).Value, Sheets("Sheet2").Range("A1:B65000"), 2, 0)
Application.EnableEvents = True
End Sub

 

[asyamonique]

Thanks again.