Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! . . |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! . . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! . . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! . . . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! . . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula to change scientific number to regular number or text | Excel Discussion (Misc queries) | |||
Change number format from text to number? | New Users to Excel | |||
Change Text to a number | Excel Worksheet Functions | |||
EZ Q 4 U: How do I change a number to text, based on the number | Excel Worksheet Functions | |||
Macro to change a number to text | Excel Programming |