vba - 将数字字符转换为字母字符

Question

我正在尝试按如下方式获取 I/O:

Input : 123490
Output : BCDEJA

逻辑很简单:

如果
strarr(i)=0,1,2,3,4,5,6,7,8,9
那么
strarr(i) 应该 = A,B,C,D,E,F,G,H,I,J

代码

str = .Cells(18, "B").Value
strarr() = Split(str) 
For i = LBound(strarr) To UBound(strarr)
  If strarr(i) = 0 Then
  .Cells(24, "B") = "A" & .Cells(24, "B")
  Else
  If strarr(i) = 1 Then
  .Cells(24, "C") = "B" & .Cells(24, "C")
  Else
  If strarr(i) = 2 Then
  .Cells(24, "C") = "C" & .Cells(24, "C")
  Else
  If strarr(i) = 3 Then
  .Cells(24, "D") = "D" & .Cells(24, "D")
  Else
  .
  .
  .

  If strarr(i) = 9 Then
  .Cells(24, "J") = "J" & .Cells(24, "J")
  Else

  End If x10 times
Next i

.Cells(24, "B") = .Cells(24, "B") & .Cells(24, "C") & .Cells(24, "D") & .Cells(24, "E") & .Cells(24, "F") & .Cells(24, "G") & .Cells(24, "H") & .Cells(24, "I") & .Cells(24, "I") & .Cells(24, "J")

.Cells(18, "D").Value = .Cells(24, "B")

Worksheets("Functions").Rows(24).ClearContents
End With

任何人都可以帮我解决我的错误吗？

Answer 1

利用 ASCII 字符数字 (...?) 并根据要转换的数字进行调整。国会大厦A是ASCII 0×41或65 dec。

Function num_alpha(str As String)
    Dim sTMP As String, d As Long

    For d = 1 To Len(str)
        sTMP = sTMP & Chr(65 + Mid(str, d, 1))
    Next d

    num_alpha = sTMP

End Function

像任何 native 工作表函数一样使用。在 D18 中，

=num_alpha(B18)