Macro to change a number to text
 

I'm having trouble trying to get this done. Just
recording the keystrokes to put in the apostrophe isn't
working. Is there a statement to be used in the macro to
accomplish this?

Thanks in advance,
Lori
-----Original Message-----
Have the Macro add an apostrophe before the number. This
will change it to text.


-----Original Message-----
Help, please?

I have a user who has a column of accts payable numbers
and a total at the bottom (=Sum). When a payment is
made,
she wants to be able to bold a number and tell Excel not
to include it in the total at the bottom.

I've created a macro to bold the current cell and format
it as text instead of a number. The cell that is now
text
should not be calculated the function, but it is. What
am
I doing wrong?

Thanks!
.

.

Macro to change a number to text
 

Hi Lori,

The following code will put a ' before each value in the selected cells:

Sub MakeText()
Dim rng As Range

For Each rng In Selection
With rng
.Value = "'" & CStr(.Value)
End With
Next rng
End Sub

Keep in mind that this will destroy formulas, so it should only be used on
cells that don't contain formulas. Here's how you can help protect against
that:

Sub MakeText()
Dim rng As Range

For Each rng In Selection
With rng
If Not .HasFormula Then .Value = "'" & CStr(.Value)
End With
Next rng
End Sub

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]


Lori F wrote:
I'm having trouble trying to get this done. Just
recording the keystrokes to put in the apostrophe isn't
working. Is there a statement to be used in the macro to
accomplish this?

Thanks in advance,
Lori
-----Original Message-----
Have the Macro add an apostrophe before the number. This
will change it to text.


-----Original Message-----
Help, please?

I have a user who has a column of accts payable numbers
and a total at the bottom (=Sum). When a payment is made,
she wants to be able to bold a number and tell Excel not
to include it in the total at the bottom.

I've created a macro to bold the current cell and format
it as text instead of a number. The cell that is now text
should not be calculated the function, but it is. What am
I doing wrong?

Thanks!
.

.

Macro to change a number to text
 

cell = "'" & cell.text

that's double quote, single quote, double quote

There are other ways, but this was the way you asked the question.

--
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Lori F" wrote in message ...
I'm having trouble trying to get this done. Just
recording the keystrokes to put in the apostrophe isn't
working. Is there a statement to be used in the macro to
accomplish this?

Thanks in advance,
Lori
-----Original Message-----
Have the Macro add an apostrophe before the number. This
will change it to text.


-----Original Message-----
Help, please?

I have a user who has a column of accts payable numbers
and a total at the bottom (=Sum). When a payment is
made,
she wants to be able to bold a number and tell Excel not
to include it in the total at the bottom.

I've created a macro to bold the current cell and format
it as text instead of a number. The cell that is now
text
should not be calculated the function, but it is. What
am
I doing wrong?

Thanks!
.

.

Macro to change a number to text
 

Thank you again, Jake! I'm not worried about the
formulas - the macro will only be used on raw data.

Lori
-----Original Message-----
Hi Lori,

The following code will put a ' before each value in the
selected cells:

Sub MakeText()
Dim rng As Range

For Each rng In Selection
With rng
.Value = "'" & CStr(.Value)
End With
Next rng
End Sub

Keep in mind that this will destroy formulas, so it
should only be used on
cells that don't contain formulas. Here's how you can
help protect against
that:

Sub MakeText()
Dim rng As Range

For Each rng In Selection
With rng
If Not .HasFormula Then .Value = "'" &
CStr(.Value)
End With
Next rng
End Sub

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address
unmonitored]


Lori F wrote:
I'm having trouble trying to get this done. Just
recording the keystrokes to put in the apostrophe isn't
working. Is there a statement to be used in the macro to
accomplish this?

Thanks in advance,
Lori
-----Original Message-----
Have the Macro add an apostrophe before the number.
This
will change it to text.


-----Original Message-----
Help, please?

I have a user who has a column of accts payable
numbers
and a total at the bottom (=Sum). When a payment is
made,
she wants to be able to bold a number and tell Excel
not
to include it in the total at the bottom.

I've created a macro to bold the current cell and
format
it as text instead of a number. The cell that is now
text
should not be calculated the function, but it is.
What am
I doing wrong?

Thanks!
.

.

.

Macro to change a number to text
 

Thank you so much, David! That should help a lot.

Lori
-----Original Message-----
cell = "'" & cell.text

that's double quote, single quote, double quote

There are other ways, but this was the way you asked the
question.

--
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed
Nov. 2001]
My Excel Pages:
http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page:
http://www.mvps.org/dmcritchie/excel/search.htm

"Lori F" wrote in
message ...
I'm having trouble trying to get this done. Just
recording the keystrokes to put in the apostrophe isn't
working. Is there a statement to be used in the macro to
accomplish this?

Thanks in advance,
Lori
-----Original Message-----
Have the Macro add an apostrophe before the number.
This
will change it to text.


-----Original Message-----
Help, please?

I have a user who has a column of accts payable
numbers
and a total at the bottom (=Sum). When a payment is
made,
she wants to be able to bold a number and tell Excel
not
to include it in the total at the bottom.

I've created a macro to bold the current cell and
format
it as text instead of a number. The cell that is now
text
should not be calculated the function, but it is. What
am
I doing wrong?

Thanks!
.

.



.