how can i ask the program to convert numbers to text automaticaly

J

jack

how can i ask the program to convert numbers to text automatically?
I want to input a number into a cell and have the program automatically
convert the number into a letter.
For example 1=a 2=b etc.
is there an fuction that can do that?
 
G

Greg Wilson

This is example code and can be changed to suit. It assumes that the range is
in column A starting at A2. Paste the code to the worksheet's code module. To
access the code module, right click the worksheet tab and select View Code.
Numbers between 1 and 26 entered into any cell in column A except A1 will be
converted to lower case letters.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Range
If Target.Count > 1 Then Exit Sub
Set r = Range(Cells(2, 1), Cells(Rows.Count, 1).End(xlUp))
If Not Intersect(Target, r) Is Nothing Then
With Application
.EnableEvents = False
If IsNumeric(Target) Then
Target.Value = Chr(Target.Value + 96)
End If
.EnableEvents = True
End With
End If
End Sub

Regards,
Greg
 
G

Greg Wilson

To simplify, the End(xlUp) part can be eliminated. Suggest this:

Set r = Range(Cells(2, 1), Cells(Rows.Count, 1))

in place of this:

Set r = Range(Cells(2, 1), Cells(Rows.Count, 1).End(xlUp))

Greg
 
Top