Text Fromat in VBA

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
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
To generate the error marker

Code:
With Range("A1")
    .Value = "'" & .Value
End With

and to remove it

Code:
With Range("A1")
    .Value = .Value
End With
 
Upvote 0

Forum statistics

Threads
1,213,560
Messages
6,114,309
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top