I am very good in VLOOKUP().
I dont want to use and dont want to show formulas in my excel sheet
report. The form which I am creating is multi user form.
The person using this form is not aware of employee number. If he enter
employee name, he should automatically get corresponding employee number.
This form keep on passing to different person.

Is it possible create this form?? Please help me if any body has solutions
???macro???

If you have a table of names and numbers on say sheet3 you could use this

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("H1:H10")) Is Nothing Then
With Target
.Offset(0, 1).Value = Application.VLookup(.Value,
Worksheets("Sheet3").Range("A1:B1000"), 2, False)
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.

--
HTH

Bob Phillips

"vishu" wrote in message
...
I am very good in VLOOKUP().
I don't want to use and don't want to show formulas in my excel sheet
report. The form which I am creating is multi user form.
The person using this form is not aware of employee number. If he enter
employee name, he should automatically get corresponding employee number.
This form keep on passing to different person.

Is it possible create this form?? Please help me if any body has solutions
???macro???

Hi Bob,

How could the #N/As which might appear in I1:I10 be averted via a tweak in
your code ? For e.g.: when entries made in H1:H10 are cleared, or for any
unmatched entries. ( I wanna leave no trace <g)

Thanks
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

Hi Max,

By using Application.VLOOKUP, it is easy to trap the error

Private Sub Worksheet_Change(ByVal Target As Range)
Dim thisValue
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("H1:H10")) Is Nothing Then
With Target
thisValue = Application.VLookup(.Value,
Worksheets("Sheet3").Range("A1:B1000"), 2, False)
If Not IsError(thisValue) Then
.Offset(0, 1).Value = thisValue
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub


Regards

Bob

"Max" wrote in message
...
Hi Bob,

How could the #N/As which might appear in I1:I10 be averted via a tweak in
your code ? For e.g.: when entries made in H1:H10 are cleared, or for any
unmatched entries. ( I wanna leave no trace <g)

Thanks
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

Thanks, Bob. But when I clear any input cell(s) within H1:H10, I would
probably want the corresponding displays in I1:I10, i.e. the previous
result(s), if any, to be cleared as well. How could this be achieved ?
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

Hi Max,

Untetsed but should be okay

Private Sub Worksheet_Change(ByVal Target As Range)
Dim thisValue
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("H1:H10")) Is Nothing Then
With Target
thisValue = Application.VLookup(.Value, _
Worksheets("Sheet3").Range("A1:B1000"), 2, False)
If Not IsError(thisValue) Then
.Offset(0, 1).Value = thisValue
ElseIf Trim(.Value) = "" Then
.Offset(0,1).Value = ""
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub


--
HTH

Bob Phillips

"Max" wrote in message
...
Thanks, Bob. But when I clear any input cell(s) within H1:H10, I would
probably want the corresponding displays in I1:I10, i.e. the previous
result(s), if any, to be cleared as well. How could this be achieved ?
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

Hi bob,
Thank you for your reply.
I am not able to run this macro in excel.
I am not good in programming.
Could you please copy this program in excel and do test trail, send it me
for this email id:
Please help me....Thanks in advance and regards

vishu



"Bob Phillips" wrote:

Hi Max,

By using Application.VLOOKUP, it is easy to trap the error

Private Sub Worksheet_Change(ByVal Target As Range)
Dim thisValue
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("H1:H10")) Is Nothing Then
With Target
thisValue = Application.VLookup(.Value,
Worksheets("Sheet3").Range("A1:B1000"), 2, False)
If Not IsError(thisValue) Then
.Offset(0, 1).Value = thisValue
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub


Regards

Bob

"Max" wrote in message
...
Hi Bob,

How could the #N/As which might appear in I1:I10 be averted via a tweak in
your code ? For e.g.: when entries made in H1:H10 are cleared, or for any
unmatched entries. ( I wanna leave no trace <g)

Thanks
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

Here's a sample file*:
http://flypicture.com/p.cfm?id=46111
(right-click on link "Download File" at the top)

File: BobP_sub_Vishu_misc.xls
*contains Bob's revised sub (v2)
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----