Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Vlookup Text | Excel Discussion (Misc queries) | |||
using a vlookup to enter text into rows beneath the vlookup cell | Excel Programming | |||
VLOOKUP (with text) | Excel Discussion (Misc queries) | |||
Vlookup a value and text | Excel Discussion (Misc queries) | |||
Vlookup and text | Excel Programming |