CODEWITHSUNDEEP
excelvba
Michi
Michi

Reputation: 5471

Convert column number to column letter in VBA

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?

Upvotes: 3

Views: 18950

Answers (2)

DSlomer64
DSlomer64

Reputation: 4283

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).

Upvotes: 1

user4039065
user4039065

Reputation:

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

Upvotes: 3

Related Questions