Number and Text issues

[Shane]

I have an .xls file that has a list of clients that I have filtered and
sorted. In column "A", there is a list of numbers, some that have preceeding
zeros and some that do not, but all of the numbers are of the same length.

I have created a macro in another .xlsm spreadsheet (driven by commandbutton
located on a userform) which allows the user to enter (via inputbox) a number
and it will search the .xls file and return to the userform the information
which is in the same row as the number entered by the client. This macro
takes all of the values in column "A" and converts it to text and then
searches.

Here is the issue that I am having:
if the value that it is searching for has the preceding zeros, it finds the
value EVERY time. If it starts with any other number, it does not find it
EVER.

I have tried manually manipulating the original file to make it work and I
noticed that all of the cells in column "A" that have preceding zeros have
the "little green triangle" in the corner for the error of number stored as
text, but the other numbers do not; however, if I manually click on any of
the cells in column "A" it shows it as text in the number format window on
the ribbon. While playing around, I went into the formula bar and
highlighted the number inside of the cell and hit the enter key-and that
changed it to the text format!

I am not sure what the issue is or what highlighting the information in the
formula bar does, but how would I write code to do that for every cell in
column "A"... or is there something that I am completely missing?

The code is messy (I am pretty new to writing) but I can post it if needed.

 

[Bob Bridges]

Here's a theory: When the user types a number into InputBox, InputBox always
delivers the result to you as a text string, and therefore your program
searchers for a character string in the values of col A. Personally, my
choice would be to change all the numbers in col A to numeric format, and
then tell my program to convert the incoming character string to a number
befor doing the search.

But it's equally easy to write a program to change them all to to one format
as to another. This is a one-time solution, right? I mean, you have this
data comes in mixed format, and you want to change all the cells in that
range to a single format, and after you do that you'll never have to do it
again? I'm in the middle of something right now, but if you'll send me an
email (see my profile) I can walk you through the process so you'll
understand it.