Question posted 2015 · +6 upvotes
I am trying to get I/O as follows:
Input : 123490
Output : BCDEJA
Logic is simple:
if
strarr(i)=0,1,2,3,4,5,6,7,8,9
then
strarr(i) should be = A,B,C,D,E,F,G,H,I,J
code
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
Can anyone help me out where I am wrong?
Accepted answer +9 upvotes
Make use of the ASCII character numbers (…?) and adjust them by the digits you are converting. A capitol A is ASCII 0Ã41 or 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
Use like any native worksheet function. In D18 as,
=num_alpha(B18)
      
2 code variants in this answer
- Variant 1 — 10 lines, starts with
Function num_alpha(str As String) - Variant 2 — 1 lines, starts with
=num_alpha(B18)
Top excel-vba Q&A (6)
- How to clear the entire array? +58 (2010)
- How to change Format of a Cell to Text using VBA +55 (2011)
- Download attachment from Outlook and Open in Excel +43 (2012)
- Can a VBA function in Excel return a range? +36 (2009)
- 2 Dimensional array from range +34 (2013)
- Hiding an Excel worksheet with VBA +33 (2009)
excel-vba solutions on this site
.