Big_Belly_Bob
New Member
- Joined
- Feb 9, 2006
- Messages
- 27
Hi All,
I'm not sure what the best way to describe this is so I'm going to use an example...
Open a new work book
in cell A1 put '
in cell B1 put 2000
in cell C1 put =A1&B1
now use copy and paste special values on cell C1
you should see that the number is now formated as text and that there is a little green arrow in the top left hand corner to indicate that excel thinks this is an error because some numbers are formatted as text.
Now if in stead of doing all that you just right clicked cell B1 and did format cells and selected text from the list you wouldn't get the green arrow in the corner (in less you click in the cell then on the formula bar then press enter)
This plays havok with vlookups as you need to be using the same format on what you are looking up as you are using in the list that you are doing the lookup on.
So my questions:
A) What is the differance between the 2? i.e. if I format as text but don't click in the formula bar then press enter why doesn't the number flag up as being an error?
B) How using VBA can I convert the values in a cell to text (so that they flag up like an error with a green flag in the corner of the cell) and also how can I conver them back again using VBA so that they show as normal numbers with out a green flag in the top left corner of the cell?
Hope that make sense but if not post below to let me know an I'll clarify... not an easy one to explain if you're not familiar with the issues that it causes with Vlookups
I'm not sure what the best way to describe this is so I'm going to use an example...
Open a new work book
in cell A1 put '
in cell B1 put 2000
in cell C1 put =A1&B1
now use copy and paste special values on cell C1
you should see that the number is now formated as text and that there is a little green arrow in the top left hand corner to indicate that excel thinks this is an error because some numbers are formatted as text.
Now if in stead of doing all that you just right clicked cell B1 and did format cells and selected text from the list you wouldn't get the green arrow in the corner (in less you click in the cell then on the formula bar then press enter)
This plays havok with vlookups as you need to be using the same format on what you are looking up as you are using in the list that you are doing the lookup on.
So my questions:
A) What is the differance between the 2? i.e. if I format as text but don't click in the formula bar then press enter why doesn't the number flag up as being an error?
B) How using VBA can I convert the values in a cell to text (so that they flag up like an error with a green flag in the corner of the cell) and also how can I conver them back again using VBA so that they show as normal numbers with out a green flag in the top left corner of the cell?
Hope that make sense but if not post below to let me know an I'll clarify... not an easy one to explain if you're not familiar with the issues that it causes with Vlookups