Put a formula in a text formatted cell.

  • Hello


    I have spent hours trying to work this out myself to no avail.


    I have a cell formatted as text, to allow the use of leading zero's (with no fixed format, there might two leading zero's and 8 other didgits, or one zero and two other digits or no leading zero, so using 00### or the like doesn't help). That's fine 98% of the time, but on occasion the user needs to search for data using a vlookup formula. To make this happen, a macro is used to add a formula to the cell (and remove the formula after the task is complete).


    As soon as the formula is placed in the text formatted cell, everyone knows the formula will be displayed instead of the result.


    What can I do programmatically to stop the formula being displayed instead of the result, while maintaining the text format of the cell?


    Thanks

  • Re: Put a formula in a text formatted cell.


    If you're going to the bother of adding the formula to the cell, and removing it afterwards (But for the life of me I can't understand how that would work), why not just use code to ask the user for the information to find, use VLOOKUP in the code and just place the result in the cell?