using VLOOKUP in a text box
 

Hello,
I have a user form with textbox1 and textbox2. I want to enter a
phone number into textbox1, and then I want textbox2 to be populated
from another sheet based on the value in textbox1, just like a lookup
table. So the name of the person whose phone number is in textbox1
will appear in textbox2. Does anyone know how to do this?

thanks

using VLOOKUP in a text box
 

I created a small userform -- two textboxes and a commandbutton.

This was the code behind the userform:

Option Explicit
Private Sub CommandButton1_Click()

Dim res As Variant
Dim LookUpRng As Range

If Me.TextBox1.Value = "" Then
Beep
Exit Sub
End If

Set LookUpRng = ThisWorkbook.Worksheets("Sheet1").Range("A:B")

'look for a text match 123-124-1235
res = Application.VLookup(Me.TextBox1.Value, LookUpRng, 2, False)
If IsError(res) Then
If IsNumeric(Me.TextBox1.Value) Then
'look for a number match
res _
= Application.VLookup(Val(Me.TextBox1.Value), LookUpRng, 2, False)
End If
End If

If IsError(res) Then
'not found either way
res = "No Match!"
End If

Me.TextBox2.Value = res

End Sub


Andrew wrote:

Hello,
I have a user form with textbox1 and textbox2. I want to enter a
phone number into textbox1, and then I want textbox2 to be populated
from another sheet based on the value in textbox1, just like a lookup
table. So the name of the person whose phone number is in textbox1
will appear in textbox2. Does anyone know how to do this?

thanks

--

Dave Peterson

using VLOOKUP in a text box
 

On May 5, 11:03*am, Dave Peterson wrote:
I created a small userform -- two textboxes and a commandbutton.

This was the code behind the userform:

Option Explicit
Private Sub CommandButton1_Click()

* * Dim res As Variant
* * Dim LookUpRng As Range

* * If Me.TextBox1.Value = "" Then
* * * * Beep
* * * * Exit Sub
* * End If

* * Set LookUpRng = ThisWorkbook.Worksheets("Sheet1").Range("A:B")

* * 'look for a text match 123-124-1235
* * res = Application.VLookup(Me.TextBox1.Value, LookUpRng, 2, False)
* * If IsError(res) Then
* * * * If IsNumeric(Me.TextBox1.Value) Then
* * * * * *'look for a number match
* * * * * * res _
* * * * * * *= Application.VLookup(Val(Me.TextBox1.Value), LookUpRng, 2, False)
* * * * End If
* * End If

* * If IsError(res) Then
* * * * 'not found either way
* * * * res = "No Match!"
* * End If * *

* * Me.TextBox2.Value = res

End Sub

Andrew wrote:

Hello,
I have a user form with textbox1 and textbox2. *I want to enter a
phone number into textbox1, and then I want textbox2 to be populated
from another sheet based on the value in textbox1, just like a lookup
table. *So the name of the person whose phone number is in textbox1
will appear in textbox2. *Does anyone know how to do this?

thanks

--

Dave Peterson

Thank you. This is exactly what I was looking for.