Macro to change a number to text

[Lori F]

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

 

[Jake Marx]

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]

 

[David McRitchie]

cell = "'" & cell.text

that's double quote, single quote, double quote

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

--

 

[Lori F.]

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]

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

.

 

[Lori F.]

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" <[email protected]> wrote in

message news:[email protected]...