I am trying to set up a spreadsheet where I can search
for a number or a name in a two column sheet


12345 ABCDEFGH
23456 XYZABCDE
46789 EFGHIJKL
45612 ZAYBCXDV

I need to be able to search for a number and the search
gives the corresponding name and number.

Or search the name and it gives the number and name.

I would like to put the value in a textbox and the output
to be placed in a list box.

Any thoughts,

Ian,

Hi

My normal soution to this would be to change the search
data slightly and use vlookup. I would change the data
thus:

A B C
Number Text Copy of Number
123455 abcdef 123455


I would create the copy using either copy paste or a
simple formula.

I would then use vlookup either against column AB or
columns BC depending on which is the value I want to
search. Vlookup looks like

vlookup(SearchItem, RangeToSearch, Offset, MatchType)

of interest the offset is which column is returned from
the range, and the MatchType if flase then searches the
entire range until it finds an exact match, or TRUE
requires that the values be sorted, as it will tell the
function to find the first item that is < than item. Full
help below:

Searches for a value in the leftmost column of a table,
and then returns a value in the same row from a column you
specify in the table. Use VLOOKUP instead of HLOOKUP when
your comparison values are located in a column to the left
of the data you want to find.

The V in VLOOKUP stands for "Vertical."

Syntax

VLOOKUP
(lookup_value,table_array,col_index_num,range_look up)

Lookup_value is the value to be found in the first
column of the array. Lookup_value can be a value, a
reference, or a text string.

Table_array is the table of information in which data is
looked up. Use a reference to a range or a range name,
such as Database or List.

If range_lookup is TRUE, the values in the first column of
table_array must be placed in ascending order: ..., -2, -
1, 0, 1, 2, ..., A-Z, FALSE, TRUE; otherwise VLOOKUP may
not give the correct value. If range_lookup is FALSE,
table_array does not need to be sorted.

You can put the values in ascending order by choosing the
Sort command from the Data menu and selecting Ascending.

The values in the first column of table_array can be text,
numbers, or logical values.

Uppercase and lowercase text are equivalent.

Col_index_num is the column number in table_array from
which the matching value must be returned. A col_index_num
of 1 returns the value in the first column in table_array;
a col_index_num of 2 returns the value in the second
column in table_array, and so on. If col_index_num is less
than 1, VLOOKUP returns the #VALUE! error value; if
col_index_num is greater than the number of columns in
table_array, VLOOKUP returns the #REF! error value.

Range_lookup is a logical value that specifies whether
you want VLOOKUP to find an exact match or an approximate
match. If TRUE or omitted, an approximate match is
returned. In other words, if an exact match is not found,
the next largest value that is less than lookup_value is
returned. If FALSE, VLOOKUP will find an exact match. If
one is not found, the error value #N/A is returned.

Remarks

If VLOOKUP can't find lookup_value, and range_lookup is
TRUE, it uses the largest value that is less than or equal
to lookup_value.
If lookup_value is smaller than the smallest value in the
first column of table_array, VLOOKUP returns the #N/A
error value.
If VLOOKUP can't find lookup_value, and range_lookup is
FALSE, VLOOKUP returns the #N/A value.

Well, as I under stand, you want to enter a number of name in a text
box, and then click on a button, which will find corresponding name or
number and add to the list box.
Assuming the numbers and names are in column A and B in worksheet1 and
number of rows of them are 1000, you can use following code in the
commandbutton_click event.

The below code checks first if the entered data is a number or text and
accordingly puts name in case it is number or number in case it is name.

Dim myRange As Range, foundRange As Range, findWhat As String
Set myRange = Worksheets("Sheet1").Range("A1:B1000")
findWhat = TextBox1.Text
On Error Resume Next
If IsNumeric(findWhat) Then
Set foundRange = myRange.Find(What:=findWhat,
LookAt:=xlWhole).Offset(0, 1)
If Err < 0 Then
MsgBox "The number you entered was not found in the list."
Exit Sub
End If
ListBox1.AddItem (ListBox1.ListCount + 1 & ". " & foundRange.Value)
Else
Set foundRange = myRange.Find(What:=findWhat,
LookAt:=xlWhole).Offset(0, -1)
If Err < 0 Then
MsgBox "The name you entered was not found in the list."
Exit Sub
End If
ListBox1.AddItem (ListBox1.ListCount + 1 & ". " & foundRange.Value)
End If

Sharad


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!