Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Number and Text issues
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Number and Text issues
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. --- "Shane" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Number/Text Issues | Excel Programming | |||
TEXT issues | Excel Discussion (Misc queries) | |||
Access Excel Linked Text and Number Issues | Excel Discussion (Misc queries) | |||
Issues with Word Wrapping in cells after a number of characters | Excel Discussion (Misc queries) | |||
Number Format Issues-Decimal Places | Excel Worksheet Functions |