467,910 Members | 1,614 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Share your developer knowledge by writing an article on Bytes.

How to replace number to Alphabet

128KB
VBA code that replaces any numeric value with alphabet (Excel column).

Expand|Select|Wrap|Line Numbers
  1. Function Convert_Number_to_Aplha(ByVal num As Long) As String
  2.     Dim buf As String
  3.     buf = Cells(1, num).Address(True, False)
  4.     Convert_Number_to_Aplha = Left(buf, InStr(buf, "$") - 1)
  5. End Function
Example
Expand|Select|Wrap|Line Numbers
  1. Sub sample1()
  2.     Dim S As String
  3.     Dim i As Integer
  4.     For i = 1 To 40
  5.         S = Convert_Number_to_Aplha(i)
  6.         Cells(1, i).Value = S
  7.     Next i
  8. End Sub
Feb 21 '20 #1
  • viewed: 5477
Share:
5 Comments
128KB
This code determines if the argument is a number or an alphabet, returns the alphabet if it is a number, and returns the number if it is an alphabet.

Expand|Select|Wrap|Line Numbers
  1. Function Replace_NA_or_AN(ByVal val As Variant) As Variant
  2.     Dim buf As String
  3.     If IsNumeric(val) = True Then
  4.         buf = Cells(1, val).Address(True, False)
  5.         Replace_NA_or_AN = Left(buf, InStr(buf, "$") - 1)
  6.     Else
  7.         Replace_NA_or_AN = Range(val & "1").Column
  8.     End If
  9. End Function
Example

Expand|Select|Wrap|Line Numbers
  1. Sub sample2()
  2.     Dim S As Variant
  3.     Dim i As Integer
  4.     For i = 1 To 40
  5.         S = Replace_NA_or_AN(i)
  6.         Cells(1, i).Value = S
  7.     Next i
  8.     For i = 1 To 40
  9.         S = Cells(1, i).Value
  10.         Cells(2, i).Value = Replace_NA_or_AN(S)
  11.     Next i
  12. End Sub
Feb 21 '20 #2
gits
Expert Mod 4TB
well - may i ask what realistic usecase could be behind that? So far i never encountered something where i would have had the need for such code in a real scenario?
Feb 21 '20 #3
128KB
Hi gits
In cell operations (make the cell active, assignment and reference),
I think that it is useful when it can be handled by renge () (multiple) or Cells () (single).
Feb 21 '20 #4
gits
Expert Mod 4TB
ok thanks - i can see how it might help in demonstrating how to access/modify cells. i was just a bit confused why i would like to replace a number with a character or vice versa in reality. Just never had such a requirement in real life so far - so i just did ask to try to get a bit more context around the plain posted code.
Feb 21 '20 #5
Expert 256MB
I believe writing bit of theory/explanation related to the code can make it work better for the "articles/how to guides" category.
Feb 26 '20 #6

Sign in to post your reply or Sign up for a free account.

Similar topics

3 posts views Thread by - ions | last post: by
5 posts views Thread by Stefan Krah | last post: by
2 posts views Thread by rob merritt | last post: by
3 posts views Thread by Raed Sawalha | last post: by
4 posts views Thread by Brian Henry | last post: by
31 posts views Thread by Joe Smith | last post: by
23 posts views Thread by Umesh | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.