Determining the Column Number in Excel Based on Column Name

Feedback


Question:

To insert the column number from Cell C1 into Cell B1, I utilize the following
VBA code
.

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

In this instance, the appearance of the outcome on my spreadsheet is as follows:

    A     B    C
1         3
2
3

All this works fine so far.


Instead of using

3

, I would opt to use the letter of the column, specifically

C

, which should be inserted into Cell B1.

I attempted to use the formula provided, but it was unsuccessful in my situation as I do not have a specific number to use. Instead, I utilize the

.Column

function with reference to a Column.

Which modifications should I make to my formula in order for it to function properly?


Solution 1:

Extract the currency symbol from an absolute value of MSDT with the identifier
cell address
.

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

… or separate the colon from the relative column address in its entirety.

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


Solution 2:


user4039065 is nearly there, but the subscript should be

(2)

at the end of the line. For example, 677 corresponds to column “ZA” and column 16384 corresponds to “XFD” according to the function provided 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

By substituting

(1)

for

(2)

, column 26 yields

Z:

instead of just

Z

, as elaborated below.

The function

Split

returns an array of length 3, showcasing the step-by-step process leading to the final result when a valid
Excel column
address is utilized.

The displayed results for 256, as shown in the code block labeled

msg

, 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         (since second $ is after the :, strip it and all after)
Substring 2 is          (since : above is a usual delimiter, strip it)


Split

“Returns a zero-based, one-
dimensional array
that contains all substrings of the given expression (first argument), when the third argument is omitted.”

Frequently Asked Questions