Solution please
 

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 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
----

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
----

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
----

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

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

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

Max,

If I click on either of those links, ads apart, there is nothing there?

Is this a free, no subscribe service?

Regards

Bob

"Max" wrote in message
...
And here's the latest* sample file:
http://flypicture.com/p.cfm?id=46116
(right-click on link "Download File" at the top)

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

If I click on either of those links, ads apart, there is nothing there?
There's a tiny-weenie "Download File" link at the top of the page,
just above the ads, Bob. There's the baby <g

Is this a free, no subscribe service?
Looks like it. But under the terms,
files hosted will be automatically deleted after 30 days
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

"Max" wrote in message
...
If I click on either of those links, ads apart, there is nothing there?
There's a tiny-weenie "Download File" link at the top of the page,
just above the ads, Bob. There's the baby <g

Good grief, on my hi-res setting I could hardly see that :-)

Is this a free, no subscribe service?
Looks like it. But under the terms,
files hosted will be automatically deleted after 30 days

Useful though.

Thanks for the info.

Pleasure` Bob.

Good grief, on my hi-res setting I could hardly see that :-)
LOL ! Happened to me too, although I was only at 1024 x 768 res
The d/l link on the page looks so indescript and barely discernible
(that's why I've since added a pointer phrase to guide interested readers)
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

"Max" wrote in message
...
LOL ! Happened to me too, although I was only at 1024 x 768 res
The d/l link on the page looks so indescript and barely discernible

So imagine it at 1600x1024!

So imagine it at 1600x1024!
Yes, something like the movie: "Honey, I shrunk the kids" <bg

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

Hi Max/Bob,
Thanks for your great help.
I need some clarification.., as per your excel sheet which you have sent to
me earlier in that If I enter Employee name in Cell H1:H10 i can see
corresponding employee number in Cell I1:I10. That means result will appear
in right side first column.
But I need Correspoding employee number after 6 columns.
Say for exmple: If I give employee name in Cell H1:H10, I need Employee
number in N1:N10.
Can you please advice me in this.

Thanks in advance..

"Max" wrote:

So imagine it at 1600x1024!
Yes, something like the movie: "Honey, I shrunk the kids" <bg

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

Think you just need to change the value within Offset(0, 1) from "1" to "6"
i.e. just replace the 2 lines with " .Offset(0, 1).Value" in Bob's code
with ".Offset(0, 6).Value"

That should shift the output to N1:N10
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"vishu" wrote in message
...
Hi Max/Bob,
Thanks for your great help.
I need some clarification.., as per your excel sheet which you have sent
to
me earlier in that If I enter Employee name in Cell H1:H10 i can see
corresponding employee number in Cell I1:I10. That means result will
appear
in right side first column.
But I need Correspoding employee number after 6 columns.
Say for exmple: If I give employee name in Cell H1:H10, I need Employee
number in N1:N10.
Can you please advice me in this.

Thanks in advance.

Thanks a ton...
Max.. are you there in MicroSoft customer Relationship :-)

"Max" wrote:

Think you just need to change the value within Offset(0, 1) from "1" to "6"
i.e. just replace the 2 lines with " .Offset(0, 1).Value" in Bob's code
with ".Offset(0, 6).Value"

That should shift the output to N1:N10
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"vishu" wrote in message
...
Hi Max/Bob,
Thanks for your great help.
I need some clarification.., as per your excel sheet which you have sent
to
me earlier in that If I enter Employee name in Cell H1:H10 i can see
corresponding employee number in Cell I1:I10. That means result will
appear
in right side first column.
But I need Correspoding employee number after 6 columns.
Say for exmple: If I give employee name in Cell H1:H10, I need Employee
number in N1:N10.
Can you please advice me in this.

Thanks in advance.

"vishu" wrote
Thanks a ton...
You're welcome !

Max.. are you there in MicroSoft customer Relationship :-)
No, I'm not an MS employee ..

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