excel怎么让我输入的数字变成字母

更新时间2019-11-20 14:38:20

excel怎么让我输入的数字变成字母，例如输入1就显示a。2就是b，3就是c，12就是ab，123就是abc

（一）假设在A1输入，自动变成所需字符

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address(0, 0) <> "A1" Then Exit Sub

Application.EnableEvents = False

On Error Resume Next

For i = 1 To Len(Target)

On Error Resume Next

n = CInt(Mid(Target, i, 1))

    If Err.Number = 0 Then

        If n > 0 And n < 10 Then

            s = s & Chr(96 + n)

        Else

            s = s & Mid(Target, i, 1)

        End If

    Else

        s = s & Mid(Target, i, 1)

    End If

Resume

Next i

Target = s

Application.EnableEvents = True

End Sub

（二）假设在A列输入，自动变成所需字符

将上面

If Target.Address(0, 0) <> "A1" Then Exit Sub

一句改为

If Target.Column <> 1 Then Exit Sub

（三）假设在整个Sheet1工作表中输入，自动变成所需字符

就去掉

If Target.Address(0, 0) <> "A1" Then Exit Sub

一句。

（四）假设在整个工作簿输入，自动变成所需字符

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

Application.EnableEvents = False

On Error Resume Next

For i = 1 To Len(Target)

On Error Resume Next

n = CInt(Mid(Target, i, 1))

    If Err.Number = 0 Then

        If n > 0 And n < 10 Then

            s = s & Chr(96 + n)

        Else

            s = s & Mid(Target, i, 1)

        End If

    Else

        s = s & Mid(Target, i, 1)

    End If

Resume

Next i

Target = s

Application.EnableEvents = True

 

End Sub

相关标签:excel