Convert column number to column letter in VBA

Question

I use the following VBA code to insert the column number from Cell C1 into Cell B1:

Sub AdressColumn()
Sheet1.Range("B1").Value = Sheet1.Range("C1").Column
End Sub

In this case the result on my spreadsheet looks like this:

    A     B    C
1         3
2
3

All this works fine so far.

However, instead of inserting the 3 I would prefer to insert the letter of the column. In this case the letter C should be inserted into Cell B1.

I also tried to go with the formula here but I could not make it work since in my case I do not use a given number. Instead I refer to a Column with the .Column function.

What do I have to change in my formula to make it work?

score 3 · Accepted Answer · 2018-09-25T06:54:19.573

3

Split the $ out of an absolute cell address.

Sub AdressColumn()
    Sheet1.Range("B1").Value = split(Sheet1.Range("C1").address, "$")(1)
End Sub

... or split the colon out of the relative full column address.

Sub AdressColumn()
    Sheet1.Range("B2").Value = Split(Sheet1.Range("C1").EntireColumn.Address(0, 0), ":")(0)
End Sub

edited Sep 25 '18 at 06:54

answered Jul 05 '18 at 12:50

Thanks. Exactly what I was looking for. – Michi Jul 05 '18 at 13:17

DSlomer64 · Answer 2 · 2020-02-17T13:45:15.497

user4039065 is very close, but the subscript at the end of the line should be (2). Then, e.g., 677 represents column "ZA" and column 16384 represents "XFD" by the function below:

    Const MAX_COL_NUMBER = 16384
        ...
    Function columnNumberToColumnString(col As Integer) As String

      If col > MAX_COL_NUMBER Or col < 1 Then
        columnNumberToColumnString = "ERROR":     Exit Function
      Else

        columnNumberToColumnString = Split(Columns(col).Address, "$")(2)

      End If

' delete code block below after seeing how Split works
msg = "Split <" & Columns(col).Address & ">"
    For i = 0 To UBound(Split(Columns(col).Address, "$"))
        msg = msg + Chr(13) & Chr(10) & "Substring " & i & _
              " is <" & Split(Columns(col).Address, "$")(i) & ">"        
    Next
MsgBox msg       


  End Function

In fact, if I use (1) in place of my (2), for column 26 I get Z:, not just Z, as explained below.

The Split function, when used with a valid Excel column address, returns an array of length 3, showing in stages how the final result is arrived at.

For 256, for example, the results displayed by the msg code block are:

Address of column number 256 is <$IV:$IV>
Substring 0 is <>           (since first $ is first character, strip it and all after)
Substring 1 is <IV:>        (since second $ is after the :, strip it and all after)
Substring 2 is <IV>         (since : above is a usual delimiter, strip it)

Split "Returns a zero-based, one-dimensional array containing ... 'all substrings' " (if the limit (third) argument is omitted) of the given expression (first argument).

Convert column number to column letter in VBA

2 Answers2