SEARCH by the last two digits of a phone number

[jeh]

Hi Everyone,

I have a Combobox that is populated by a range of phone numbers. I am curious how to search for all of the phone numbers that end in the last two digits that I type in the combobox. Is this possible to do? I have never tried to use a list box, or combobox to search from. If it's really complicated, can someone send an example please?

Thank you very much for your time,&(patience)

Jim

[Damon Ostrander]

Hi Jim,

There are a number of ways of doing this. Probably the easiest is to use a two-column combobox and using the Match Entry capability to find values in the list that match the value being typed in. To do this first add a column of cells next to the column of phone numbers containing the last two digits. You can use the formula

=Right(A1,2)

to extract the last two digits if the phone numbers start in cell A1.

Then you can sort this list using as a key the last two digits. So all numbers ending in 00 would be first, then numbers ending with 01, etc.

To set up your combobox, you MUST use an ActiveX-type combobox (which comes from the Controls toolbar) rather than a Forms-type combobox (which comes from the Forms toolbar). Set its properties as follows:

ListFillRange: 2-column range containing phone numbers and last two digits.

MatchEntry: 1 - fmMatchEntryComplete

BoundColumn: 1 or 2 depending on whether the 2-digit numbers are to the right or left of the phone numbers.

ColumnCount: 2

TextColumn: same as BoundColumn.

ColumnWidths: 75 pt;40 pt if 2-digit numbers are on the right, 40 pt; 75 pt otherwise.

As I said, this is just one of many ways to do this. This one is easy, but does require that you create the extra column and sort the list. If you don't want to do these things, for example you want the list in a different sort order, you could use the same general approach, but fill the list in ascending order using a VBA macro. This would leave the original phone list order unmodified, and would eliminate the need for the list of two-digit suffixes.

[jeh]

Hi Damon,
Thank you very much. I will try it.

Jim