How to FIX Recorded Macro

[JMay]

I want to create a macro that will convert a number to Text format
so that it can be read by another formula where the cell needs to be text.
Below is the result of turning on the recorder and doing as follows (with my
cell that needs fixing selected before):

Format, Cells, Text, OK
F2 pressed (To bring up in edit mode) + enter

But it doesn't seem to be working...
CAn someone assist?

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 5/22/2007 by Jim May
'

'
Selection.NumberFormat = "@"
' ActiveCell.FormulaR1C1 = "879"
With ActiveCell.Characters(Start:=1, Length:=3).Font
.Name = "Verdana"
.FontStyle = "Regular"
.Size = 8
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
' Range("L1149").Select
End Sub

 

[Barb Reinhardt]

How about using something like

=Text(A1,0)

HTH,
Barb Reinhardt

 

[JMay]

Thanks for your help. I suppose I was just lloking for the Macro line
which would mimic the pressing the F2 Ket and then Enter, I just did
it by itself (while recording) and got an error. hummmmm..
Thanks,

 

[AKphidelt]

What's not working with it?... works fine for me.

 

[Gord Dibben]

Jim

Macros won't run while you are in Edit mode if I read correctly what you are
attempting.


Gord Dibben MS Excel MVP

 

[JMay]

AKphidelt -- Thanks for your comment. I went back and played with it a bit
more and came up with this (and added to my personal.xls file) - which is
what I wanted in the first place.
Thanks for the encouragement.

Sub NumToText()
' Macro recorded 5/22/2007 by Jim May
'
With ActiveCell
TFontName = .Font.Name
TFontSize = .Font.Size
End With
Selection.NumberFormat = "@"
With ActiveCell.Characters(Start:=1, Length:=3).Font
.Name = TFontName
.FontStyle = "Regular"
.Size = TFontSize
End With
End Sub

 

[JMay]

Gord - Thanks for the comment. Even though I run this Macro after selecting
the subject cell, it still doesn't "register" as Text (Using the =ISTEXT()
function) until I do an F2 and Enter (meaning update it).. How can I do this
within the Macro?

Tks,

Jim May

Sub NumToText()
' Macro recorded 5/22/2007 by Jim May
'
With ActiveCell
TFontName = .Font.Name
TFontSize = .Font.Size
End With
Selection.NumberFormat = "@"
With ActiveCell.Characters(Start:=1, Length:=3).Font
.Name = TFontName
.FontStyle = "Regular"
.Size = TFontSize
End With
End Sub

 

[Gord Dibben]

Jim

Your first macro posted works for me. The font changes take place and the
ISTEXT returns TRUE.

Your second macro does nothing but left-align the number in the cell.

Where are these declared?

TFontName
TFontSize

Your first again with the activecell.formulaR1C1 line un-remmed

Sub Macro1()
Selection.NumberFormat = "@"
ActiveCell.FormulaR1C1 = "879"
With ActiveCell.Characters(Start:=1, Length:=3).Font
.Name = "Verdana"
.FontStyle = "Regular"
.Size = 8
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
End Sub

Gord