Solution please

[Guest]

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

 

[Bob Phillips]

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.

 

[Max]

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

 

[Bob Phillips]

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]

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 ?

 

[Guest]

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: (e-mail address removed)
Please help me....Thanks in advance and regards

vishu

 

[Bob Phillips]

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

 

[Max]

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)

 

[Max]

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

 

[Max]

Works great ! Thanks, Bob.

 

[Bob Phillips]

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]

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,

Is this a free, no subscribe service?

Looks like it. But under the terms,
files hosted will be automatically deleted after 30 days

 

[Bob Phillips]

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

Looks like it. But under the terms,
files hosted will be automatically deleted after 30 days

Useful though.

Thanks for the info.

 

[Max]

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)

 

[Bob Phillips]

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!

 

[Max]

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

 

[Guest]

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]

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

 

[Guest]

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

 

[Max]

Thanks a ton...

You're welcome !

Max.. are you there in MicroSoft customer Relationship

No, I'm not an MS employee ..