• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 114
  • Last Modified:

Convert numeric code with VBA

I need to change a numeric value into a string based on a predefined code. Currently, I convert the numeric value into string. Check and replace the numeric value into corresponding data. Just wonder whether there is any better way to handle this ? Tks

21 -> AX
eg.
1 = X
2 = A
3 = G
0
AXISHK
Asked:
AXISHK
3 Solutions
 
Martin LissOlder than dirtCommented:
Usage: MsgBox ConvertToString(21)

Function ConvertToString(strValue As String) As String

Const Letters = "XAG"
Dim lngDigit As Long

For lngDigit = 1 To Len(strValue)
    ConvertToString = ConvertToString & Mid$(Letters, Mid$(strValue, lngDigit, 1), 1)
Next
End Function

Open in new window

0
 
Pratik MakwanaData AnalystCommented:
You can use below code for single column..
Sub ReplaceFunctions()
Dim dat As Variant
Dim rng As Range
Dim i As Long

Set rng = Range("A1:G27" & ActiveSheet.UsedRange.Rows.Count)
dat = rng
For i = LBound(dat, 1) To UBound(dat, 1)
    If dat(i, 1) = "1" Then
        dat(i, 1) = "X"
    ElseIf dat(i, 1) = "2" Then
        dat(i, 1) = "A"
    ElseIf dat(i, 1) = "3" Then
        dat(i, 1) = "G"
    End If
Next
rng = dat
End Sub
0
 
ChloesDadCommented:
Rather than searching each time for the correct letter, as in the above example, you could create an array of letters and then print the appropriate letter

Function ConvertToString(strValue As String) As String

Dim Letters(10)

Letters(0) = "Z"
Letters(1)="X"
Letters(2)="A"
Letters(3)="G"
etc


    ConvertToString = ConvertToString & Letters(Mid$(strValue, lngDigit, 1))

End Function

Open in new window

0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
AXISHKAuthor Commented:
No sure how to recursively call the function, supposed Pricecode() array has been loaded with 0-9, Tks


 MsgBox ConvertToString("512")

Private Function ConvertToString(strValue As String) As String

Dim lngDigit As Long

For lngDigit = 1 To Len(strValue)
    ConvertToString = ConvertToString(PriceCode(Mid$(strValue, lngDigit, 1)))
Next

End Function
0
 
Martin LissOlder than dirtCommented:
This assumes that 4 to 9 are HIJKL and M.
Function ConvertToString(strValue As String) As String

Const Letters = "XAGHIJKLM"
Dim lngDigit As Long

For lngDigit = 1 To Len(strValue)
    ConvertToString = ConvertToString & Mid$(Letters, Mid$(strValue, lngDigit, 1), 1)
Next
End Function

Open in new window

0
 
ChloesDadCommented:
Why would you need to recursively call this?

The input is a number and the output is a string.

In a function, the function name is also by default a local variable of the return type, so you don't need the Return x at the end of the function.
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now