These are my codes:
---------------------------------
dim aa_txt as string

aa=0.125

aa_txt=format(aa,"0.00%")

activecell.value=aa_txt
----------------------------------
It doesnt work. I got 12.50% into the cell, but it is still a VALUE, not a
TEXT. Pls correct me. Many thanks.

Sub aaaa()
Dim aa_txt As String
Dim aa As Double

aa = 0.125
aa_txt = Format$(aa, "0.00%")

ActiveCell.NumberFormat = "@"
ActiveCell.Value = aa_txt
End Sub
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"zhj23"
wrote in message
These are my codes:
---------------------------------
dim aa_txt as string
aa=0.125
aa_txt=format(aa,"0.00%")
activecell.value=aa_txt
----------------------------------
It doesnt work. I got 12.50% into the cell, but it is still a VALUE, not a
TEXT. Pls correct me. Many thanks.

You are going to hate me for all this. When you place it into the cell on
the sheet, Excel sees the digits and % and changes the format of the cell to
be percent! In effect converting it back to a number. If you click on that
cell and set the formatting to text, suddenly it becomes 0.125 on your sheet
again!

Try this routine instead.

This will actually fix your problem, change your last line of code to this:
ActiveCell.Value = "'" & aa_txt
that is a double-quote, single quote, doublequote so that the entry in the
cell goes in as '12.50% with the ' telling Excel to treat it as text.

Now here's a longer way to do it (not better, just different/longer, if I
were you, I'd stick with one line change I show above!

Sub MakeTextPercentageDisplay()
Dim aa_txt As String
Dim aa As Single

aa = 0.125
'convert to percent
aa = aa * 100
'build text
aa_txt = Trim(Str(aa))
'add leading zero if desired
If Left(aa_txt, 1) = "." Then
aa_txt = "0" & aa_txt
End If
'add trailing zeros if desired
'replace 2 with # of digits right of
'the decimal that you wish to display
On Error Resume Next
aa_txt = aa_txt & String(2 - _
(Len(aa_txt) - InStrRev(aa_txt, ".")), "0")
Err.Clear
On Error GoTo 0
aa_txt = aa_txt & "%"
ActiveCell.NumberFormat = "@"
ActiveCell.Formula = aa_txt
End Sub

Maybe someone will come along with an easier way - I think this is 1st time
I've tried this in such a fashion.

"zhj23" wrote:

These are my codes:
---------------------------------
dim aa_txt as string

aa=0.125

aa_txt=format(aa,"0.00%")

activecell.value=aa_txt
----------------------------------
It doesnt work. I got 12.50% into the cell, but it is still a VALUE, not a
TEXT. Pls correct me. Many thanks.

Thanks for your effort. I will certainly study your suggestions. And I won't
hate you, of course.

zhj23

"JLatham" wrote:

You are going to hate me for all this. When you place it into the cell on
the sheet, Excel sees the digits and % and changes the format of the cell to
be percent! In effect converting it back to a number. If you click on that
cell and set the formatting to text, suddenly it becomes 0.125 on your sheet
again!

Try this routine instead.

This will actually fix your problem, change your last line of code to this:
ActiveCell.Value = "'" & aa_txt
that is a double-quote, single quote, doublequote so that the entry in the
cell goes in as '12.50% with the ' telling Excel to treat it as text.

Now here's a longer way to do it (not better, just different/longer, if I
were you, I'd stick with one line change I show above!

Sub MakeTextPercentageDisplay()
Dim aa_txt As String
Dim aa As Single

aa = 0.125
'convert to percent
aa = aa * 100
'build text
aa_txt = Trim(Str(aa))
'add leading zero if desired
If Left(aa_txt, 1) = "." Then
aa_txt = "0" & aa_txt
End If
'add trailing zeros if desired
'replace 2 with # of digits right of
'the decimal that you wish to display
On Error Resume Next
aa_txt = aa_txt & String(2 - _
(Len(aa_txt) - InStrRev(aa_txt, ".")), "0")
Err.Clear
On Error GoTo 0
aa_txt = aa_txt & "%"
ActiveCell.NumberFormat = "@"
ActiveCell.Formula = aa_txt
End Sub

Maybe someone will come along with an easier way - I think this is 1st time
I've tried this in such a fashion.

"zhj23" wrote:

These are my codes:
---------------------------------
dim aa_txt as string

aa=0.125

aa_txt=format(aa,"0.00%")

activecell.value=aa_txt
----------------------------------
It doesnt work. I got 12.50% into the cell, but it is still a VALUE, not a
TEXT. Pls correct me. Many thanks.

If it were me, I'd just use the one-line change I suggested. Works every bit
as well as the long way around that I came up with (why I even did that I'll
never know - dumb of me not to come up with the one-liner first). The
one-liner would be much faster and less susceptible to breakage anyhow.

"zhj23" wrote:

Thanks for your effort. I will certainly study your suggestions. And I won't
hate you, of course.

zhj23

"JLatham" wrote:

You are going to hate me for all this. When you place it into the cell on
the sheet, Excel sees the digits and % and changes the format of the cell to
be percent! In effect converting it back to a number. If you click on that
cell and set the formatting to text, suddenly it becomes 0.125 on your sheet
again!

Try this routine instead.

This will actually fix your problem, change your last line of code to this:
ActiveCell.Value = "'" & aa_txt
that is a double-quote, single quote, doublequote so that the entry in the
cell goes in as '12.50% with the ' telling Excel to treat it as text.

Now here's a longer way to do it (not better, just different/longer, if I
were you, I'd stick with one line change I show above!

Sub MakeTextPercentageDisplay()
Dim aa_txt As String
Dim aa As Single

aa = 0.125
'convert to percent
aa = aa * 100
'build text
aa_txt = Trim(Str(aa))
'add leading zero if desired
If Left(aa_txt, 1) = "." Then
aa_txt = "0" & aa_txt
End If
'add trailing zeros if desired
'replace 2 with # of digits right of
'the decimal that you wish to display
On Error Resume Next
aa_txt = aa_txt & String(2 - _
(Len(aa_txt) - InStrRev(aa_txt, ".")), "0")
Err.Clear
On Error GoTo 0
aa_txt = aa_txt & "%"
ActiveCell.NumberFormat = "@"
ActiveCell.Formula = aa_txt
End Sub

Maybe someone will come along with an easier way - I think this is 1st time
I've tried this in such a fashion.

"zhj23" wrote:

These are my codes:
---------------------------------
dim aa_txt as string

aa=0.125

aa_txt=format(aa,"0.00%")

activecell.value=aa_txt
----------------------------------
It doesnt work. I got 12.50% into the cell, but it is still a VALUE, not a
TEXT. Pls correct me. Many thanks.